SQLAlchemy printing raw SQL from create()

by 
from sqlalchemy.schema import CreateTable

print(CreateTable(table))

If you are using declarative syntax:

print(CreateTable(Model.__table__))

Update:

Since I have the accepted answer and there is important information in klenwell answer, I’ll also add it here.

You can get the SQL for your specific database (MySQL, Postgresql, etc.) by compiling with your engine.

print(CreateTable(Model.__table__).compile(engine))

Update 2:

@jackotonye Added in the comments a way to do it without an engine.

print(CreateTable(Model.__table__).compile(dialect=postgresql.dialect()))

You can set up you engine to dump the metadata creation sequence, using the following:

def metadata_dump(sql, *multiparams, **params):
    # print or write to log or file etc
    print(sql.compile(dialect=engine.dialect))

engine = create_engine(myDatabaseURL, strategy='mock', executor=metadata_dump)
metadata.create_all(engine)

One advantage of this approach is that enums and indexes are included in the printout. Using CreateTable leaves this out.

Another advantage is that the order of the schema definitions is correct and (almost) usable as a script.

Leave a Comment