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?

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.

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

Something like this? (from the SQLA FAQ)

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

It turns out this is straight-forward:

from sqlalchemy.dialects import postgresql
from sqlalchemy.schema import CreateTable
from sqlalchemy import Table, Column, String, MetaData

metadata = MetaData()

users = Table('users', metadata,
              Column('username', String)
)

statement = CreateTable(users)

print(statement.compile(dialect=postgresql.dialect()))

Outputs this:

CREATE TABLE users (
	username VARCHAR
)

Going further, it can even support bound parameters in prepared statements.

Reference

> How do I render SQL expressions as strings, possibly with bound parameters inlined? > > ... > > or without an Engine: > > from sqlalchemy.dialects import postgresql > print(statement.compile(dialect=postgresql.dialect()))

SOURCE: http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html#faq-sql-expression-string

Example: Using SQL Alchemy to generate a user rename script

#!/usr/bin/env python
import csv
from sqlalchemy.dialects import postgresql
from sqlalchemy import bindparam, Table, Column, String, MetaData

metadata = MetaData()

users = Table('users', metadata,
              Column('username', String)
)

renames = []

with open('users.csv') as csvfile:
    for row in csv.DictReader(csvfile):
        renames.append({
            'from': row['sAMAccountName'],
            'to': row['mail']
        })

for rename in renames:
    stmt = (users.update()
            .where(users.c.username == rename['from'])
            .values(username=rename['to']))
    print(str(stmt.compile(dialect=postgresql.dialect(),
                           compile_kwargs={"literal_binds": True})) + ';')

When processing this users.csv:

sAMAccountName,mail
bmcboatface,boaty.mcboatface@example.com
ndhyani,naina.dhyani@contoso.com

Gives output like this:

UPDATE users SET username='[email protected]' WHERE users.username = 'bmcboatface';
UPDATE users SET username='[email protected]' WHERE users.username = 'ndhyani';users.username = 'ndhyani';

Why a research vessel has an email address is yet to be determined. I have been in touch with Example Inc's IT team and have had no response.

May be you mean echo parameter of sqlalchemy.create_engine?

/tmp$ cat test_s.py

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Department(Base):
    __tablename__ = "departments"

    department_id = sa.Column(sa.types.Integer, primary_key=True)
    name = sa.Column(sa.types.Unicode(100), unique=True)
    chief_id = sa.Column(sa.types.Integer)
    parent_department_id = sa.Column(sa.types.Integer,
                                     sa.ForeignKey("departments.department_id"))

    parent_department = sa.orm.relation("Department")


engine = sa.create_engine("sqlite:///:memory:", echo=True)
Base.metadata.create_all(bind=engine)

/tmp$ python test_s.py

2011-03-24 15:09:58,311 INFO sqlalchemy.engine.base.Engine.0x...42cc PRAGMA table_info("departments")
2011-03-24 15:09:58,312 INFO sqlalchemy.engine.base.Engine.0x...42cc ()
2011-03-24 15:09:58,312 INFO sqlalchemy.engine.base.Engine.0x...42cc 
CREATE TABLE departments (
	department_id INTEGER NOT NULL, 
	name VARCHAR(100), 
	chief_id INTEGER, 
	parent_department_id INTEGER, 
	PRIMARY KEY (department_id), 
	UNIQUE (name), 
	FOREIGN KEY(parent_department_id) REFERENCES departments (department_id)
)

2011-03-24 15:09:58,312 INFO sqlalchemy.engine.base.Engine.0x...42cc ()
2011-03-24 15:09:58,312 INFO sqlalchemy.engine.base.Engine.0x...42cc COMMIT