How to generate a file with DDL in the engine's SQL dialect in SQLAlchemy?

Question

Suppose I have an engine pointing at MySQL database:

engine = create_engine('mysql://arthurdent:answer42@localhost/dtdb', echo=True)

I can populate dtdb with tables, FKs, etc by:

metadata.create_all(engine)

Is there an easy way to generate the SQL file that contains all the DDL statements instead of actually applying these DDL statements to dtdb?

So far I have resorted to capturing SQLAlchemy log output produced by echo=True, and editing it by hand. But that's just too painful.

It looks like SA has pretty elaborate schema management API, but I haven't seen examples of simply streaming the schema definitions as text.

Answer 1

The quick answer is in the SQLAlchemy 0.8 FAQ.

In SQLAlchemy 0.8 you need to do

engine = create_engine(
'mssql+pyodbc://./MyDb',
strategy='mock',
executor= lambda sql, *multiparams, **params: print (sql.compile(dialect=engine.dialect)))

In SQLAlchemy 0.9 the syntax is simplified.

engine = create_engine(
'mssql+pyodbc://./MyDb',
strategy='mock',
executor= lambda sql, *multiparams, **params: print (sql)

The longer answer is that capturing the output still has some slight issues. Like with the encoding of literals of types. But this hasn't been a big enough of an issue for anyone to step up and scratch their itch. You could always let SQLAlchemy programmatically create an empty database and dump the SQL from there.

The more difficult problem is the handling of schema migrations. This is where SQLAlchemy-migrate can help you.