SQLAlchemy printing raw SQL from create()

I am giving Pylons a try with SQLAlchemy, and I love it, there is just one thing, is it possible to print out the raw SQL CREATE TABLE data generated from Table().create() before it's executed?

Solution 1:

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

Solution 2:

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.

Solution 3:

I needed to get the raw table sql in order to setup tests for some existing models. Here's a successful unit test that I created for SQLAlchemy 0.7.4 based on Antoine's answer as proof of concept:

from sqlalchemy import create_engine
from sqlalchemy.schema import CreateTable
from model import Foo

sql_url = "sqlite:///:memory:"    
db_engine = create_engine(sql_url)

table_sql = CreateTable(Foo.table).compile(db_engine)
self.assertTrue("CREATE TABLE foos" in str(table_sql))

Solution 4:

Something like this? (from the SQLA FAQ)

http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html