Extend create_all / drop_all to include schemas

[sqlalchemy-bot]

Migrated issue, originally created by Omni Flux (@omniflux)

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'))

[sqlalchemy-bot]

Changes by Omni Flux (@omniflux):

• edited description

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Omni Flux (@omniflux) wrote:

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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)

[sqlalchemy-bot]

Omni Flux (@omniflux) wrote:

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: wontfix
• changed status to closed

[sqlalchemy-bot]

Diego Quintana wrote:

@zzzeek Sorry to revive such an old topic with my elementary questions . I understand the wontfix status, but what I don't understand is the usage of the code that uses listens_for. Why not use DDL() instead of connection.execute() or engine.begin()? How are these methods suppose to be used?

• How would you create tables from Metadata using this very same approach? I assume that using table.create(engine), but is it possible to do so by passing an object of type sqlalchemy.ext.declarative.api.DeclarativeMeta instead of 'Base.metadata? My doubt is because table objects from metadata are of type sqlalchemy.sql.schema.Table and I'm not sure if I understand the difference.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Why not use DDL() instead of connection.execute()

The DDL() construct is mostly a short cut to invoking a single SQL statement given an event to listen for, and it also gives you the "execute_if" thing which we don't need here. We need to run multiple SQL statements within the one event using a loop, as well as figure out what schemas we will be running for, so we fill out the body of the event with a full function rather than using the DDL() shorthand-syntax which does not give us those options.

How would you create tables from Metadata using this very same approach? I assume that using table.create(engine), but is it possible to do so by passing an object of type sqlalchemy.ext.declarative.api.DeclarativeMeta instead of 'Base.metadata? My doubt is because table objects from metadata are of type sqlalchemy.sql.schema.Table and I'm not sure if I understand the difference.

I'm not sure what that's asking. this issue is about how to emit CREATE SCHEMA. If you want to create tables given a declarative base, usually we just say Base.metadata.create_all(engine). You can run on individual tables using MyClass.__table__.create(engine) if you want.