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.

Grigsby

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.

Arrogate

That FAQ link is what I needed, thanks! :) I wasn't looking for migrations-like schema management. Simply, I needed a simple hack to grab DDL without mucking with engine echo output. – Grigsby

This seems to have changed in Python 0.9. Also, the FAQ doesn't seem to exist for 0.9. – Intrepid

The second syntax did not work in SQLAlchemy 1.0.12 for me – see groups.google.com/d/topic/sqlalchemy/r9neOsc64wc/discussion – Liz

Answer 2

In SQLAlchemy version 2, there is a function create_mock_engine for this purpose. The below code is given on their documentation page.

from sqlalchemy import create_mock_engine

def dump(sql, *multiparams, **params):
    print(sql.compile(dialect=engine.dialect))

engine = create_mock_engine('postgresql+psycopg2://', dump)
metadata.create_all(engine, checkfirst=False))

Excited