Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to check if PostgreSQL schema exists using SQLAlchemy?

I am using SQLAlchemy to generate tables in a specific schema in a PostgreSQL database. If the schema does not exist, I want to create it. I know the PostgreSQL query to check for the existence of the schema:

SELECT exists(select schema_name FROM information_schema.schemata WHERE schema_name = 'foo')

but I want to know how I should handle this using SQLAlchemy.

like image 964
Tanner Semerad Avatar asked Aug 20 '13 22:08

Tanner Semerad


4 Answers

@javax's answer is almost correct; the following is a little clarification:

q = exists(select([("schema_name")]).select_from("information_schema.schemata")
    .where("schema_name = 'foo'"))
if not session.query(q).scalar():
    session.execute('CREATE SCHEMA foo;')
like image 65
synergetic Avatar answered Nov 03 '22 21:11

synergetic


If you want to integrate it with SQLAlchemy you could use reflection but for an easier and quicker solution:

from sqlalchemy.sql import exists, select
exists(select([("schema_name")]).select_from("information_schema.schemata").
       where("schema_name == 'foo'"))

This will return True or False.

like image 28
javex Avatar answered Nov 03 '22 20:11

javex


I'm using MySQL and this works for me (with sqlalchemy v1.4.1, python 3.9.1):

import sqlalchemy as sa

engine = sa.create_engine("mysql+pymysql://someuser:somepassword@somehost")
inspector = sa.inspect(engine)
myschema = "someschema"
if myschema in inspector.get_schema_names():
    print(f"{myschema} schema exists")

and if you want to create a schema if it doesn't exist:

if myschema not in inspector.get_schema_names():
    engine.execute(sa.schema.CreateSchema(myschema))
    # optional. set the default schema to the new schema:
    engine.dialect.default_schema_name = myschema
like image 3
Bjartmar Kristjansson Avatar answered Nov 03 '22 20:11

Bjartmar Kristjansson


I've been using this with Postgres, though was surprised to learn that IF NOT EXISTS is not part of the SQL standard -

engine.execute('CREATE SCHEMA IF NOT EXISTS foo;')

Apparently it's an extension for Postgres and MySQL - https://www.w3resource.com/sql/sql-basic/create-schema.php

like image 1
Brian Burns Avatar answered Nov 03 '22 21:11

Brian Burns