Extend create_all / drop_all to include schemas

[Omni Flux]

It would be nice if Metadata.create_all and drop_all would also automatically create and drop schemas as necessary.

engine.execute (CreateSchema('Alpha'))
Base.metadata.create_all(engine)
Base.metadata.drop_all(engine)
engine.execute (DropSchema('Alpha'))

[Mike Bayer]

There's ways to get this in as extension points on your own end. Unfortunately the concept of a "schema" is not very portable across databases. In MySQL, there's no "CREATE SCHEMA"; there's just other databases, e.g. "CREATE DATABASE" which the user account will often not have access to do, plus there's a lot of other arguments that go along with creating a database. In SQLite, there's no "CREATE SCHEMA", there's separate files you can attach to. In Oracle, there's no "CREATE SCHEMA", there are other user accounts that act as a namespace which again have very different permissions/syntax, additionally there are synonyms that can refer to other kinds of objects like remote tablespaces and such. Only Postgresql and SQL Server link the "schema" to a name that corresponds (usually) to "CREATE SCHEMA", and even then, those names might be symbolic names to something else, since you can put dotted symbols and other expressions into "schema".

The easy way to throw in "CREATE SCHEMA" with your metadata is just a simple event, and this is acceptable to add to the docs / examples:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)


class B(Base):
    __tablename__ = 'b'

    __table_args__ = {'schema': 'foo'}

    id = Column(Integer, primary_key=True)


event.listen(
    Base.metadata,
    "before_create",
    DDL("CREATE SCHEMA IF NOT EXISTS foo").execute_if(dialect='postgresql')
)

event.listen(
    Base.metadata,
    "after_drop",
    DDL("DROP SCHEMA IF EXISTS foo").execute_if(dialect='postgresql')
)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

Base.metadata.create_all(e)

Base.metadata.drop_all(e)

The DDL code above is directly from the documentation at http://docs.sqlalchemy.org/en/latest/core/ddl.html#custom-ddl and we can add this example too.

Since this is very specific to Postgresql / SQL Server and is also quite simple I'm not sure there's value in making more of a pattern out of it than that.

[Omni Flux]

Is it possible to access %(schema)s in the event.listen for metadata so foo would not have to be hardcoded?

[Mike Bayer]

sure you can iterate through metadata.tables and pull it:

@event.listens_for(Base.metadata, "before_create")
def create_schemas(target, connection, **kw):

    schemas = set()
    for table in target.tables.values():
        if table.schema is not None:
            schemas.add(table.schema)
    for schema in schemas:
        connection.execute("CREATE SCHEMA IF NOT EXISTS %s" % schema)


@event.listens_for(Base.metadata, "after_drop")
def drop_schemas(target, connection, **kw):

    schemas = set()
    for table in target.tables.values():
        if table.schema is not None:
            schemas.add(table.schema)
    for schema in schemas:
        connection.execute("DROP SCHEMA IF EXISTS %s" % schema)

[Omni Flux]

This is exactly what I was looking for, thank you.

[Mike Bayer]

this could use some more documentation examples but for the time being a "Schema" is kind of too much of a moving target to be worth a first class construct.