SQLAlchemy: "create schema if not exists"

Question

I want to do the "CREATE SCHEMA IF NOT EXISTS" query in SQLAlchemy. Is there a better way than this:

    engine = sqlalchemy.create_engine(connstr)

    schema_name = config.get_config_value('db', 'schema_name')

    #Create schema; if it already exists, skip this
    try:
        engine.execute(CreateSchema(schema_name))
    except sqlalchemy.exc.ProgrammingError:
        pass

I am using Python 3.5.

Well you have not mentioned which db you are using. At the moment I cannot see anything in sqlalchemy but sure we can run raw ddl query. For example, in case of postgres db we can execute something like `engine.execute('CREATE SCHEMA IF NOT EXISTS schema_name')` — mad_, Jun 19 '18 at 13:09
I am using PostgreSQL at the moment but this is meant to be used by multiple databases in the future. Thats why I didnt use psycopg2 or any other database-specific tool - because I thougt I can just change the connstr variable for each database and SQLAlchemy will create schema in that particular database. — Jan Pisl, Jun 19 '18 at 13:24
Well it will work in the same way only thing is it will throw Programming exception if it already exists. If you can gracefully handle it then Sqlalchemy will be able to create schema on the fly. — mad_, Jun 19 '18 at 13:29
Also you can have a look at https://stackoverflow.com/questions/18346076/how-to-check-if-postgresql-schema-exists-using-sqlalchemy — mad_, Jun 19 '18 at 13:35
The try/except approach is better than the look before you leap approaches in the answers, since they have an implicit race condition: another process could create the schema between checking for its existence and creation. — snakecharmerb, Jul 18 '21 at 14:56

stardust · Accepted Answer · 2021-07-18T14:50:41.940

42

I had the same question and the answer, which I found, is:

if not engine.dialect.has_schema(engine, schema_name):
    engine.execute(sqlalchemy.schema.CreateSchema(schema_name))

We can also check schema without engine instance, but using connection

conn = engine.connect()
if conn.dialect.has_schema(conn, schema_name):
    ...

edited Jul 18 '21 at 14:50

answered Mar 25 '19 at 19:50

stardust

593
7
9

2

You are missing `not` in the `conn` example if clause: `if not conn.dialect.has_schema`. – Aleksandar Jovanovic Aug 18 '20 at 08:07
You can also import the method as such: `from sqlalchemy.schema import CreateSchema`. And use it directly with `engine.execute(CreateSchema(schema_name))`. – Paul Rougieux Dec 17 '21 at 14:55
re `has_schema`: `NotImplementedError: This method is not implemented for SQLAlchemy 2.0.` – joel Jan 27 '23 at 13:26
@joel, you can use Inspector instead, it has the same method: https://docs.sqlalchemy.org/en/20/core/reflection.html#sqlalchemy.engine.reflection.Inspector.has_schema – stardust Jan 29 '23 at 17:07

score 7 · Answer 2 · answered Apr 17 '20 at 14:51

7

For MS Sql users, there's no has_schema() but this seems to work:

if schemaname not in conn.dialect.get_schema_names(conn):
   conn.execute(schema.CreateSchema(schemaname))

answered Apr 17 '20 at 14:51

Matthew

10,361
5
42
54

score 3 · Answer 3 · answered Sep 08 '22 at 10:12

My preferred way of doing that task:

from sqlalchemy import inspect
<You might need a few more SQLAlchemy imports

def setup_schemas(engine, metadata):
    inspector = inspect(engine)
    all_schemas = inspector.get_schema_names()
    for schema in metadata._schemas:
        if schema not in all_schemas:
            _create_schema(engine, schema)
    
def _create_schema(engine, schema) -> None:
    stmt = text(f"CREATE SCHEMA {schema}")
    with engine.connect() as conn:
        conn.execute(stmt)
        conn.commit()

score 1 · Answer 4 · answered Oct 14 '19 at 18:39

1

You can use the excellent sqlalchemy_utils package to do just that, in a very neat way.

First, install the package:

pip install sqlalchemy_utils

Then use it like this:

from sqlalchemy_utils.functions import database_exists, create_database

engin_uri = 'postgres://postgres@localhost/name'

if not database_exists(engin_uri):
    create_database(engin_uri)

The example from the official docs has used PostgreSQL and I have personally used it on MySQL 8.

answered Oct 14 '19 at 18:39

pmsoltani

976
8
13

5

Your example show how to create a database not a schema. The OP wants to know a better way than his to create a specific schema – sdikby Oct 02 '20 at 11:47
@sdikby I can confirm that this example works for MySQL as well. database and schema describe the same entity in MySQL – Almog-at-Nailo Jan 26 '23 at 12:20

SQLAlchemy: "create schema if not exists"

4 Answers4

Linked