See SQL CREATE TABLE statement generated by SQLAlchemy without actually creating table

Question

With the code snippet below I can easily create a Table using SQL Alchemy and see the actual SQL 'CREATE TABLE' statement generated.

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, TIMESTAMP
#engine = create_engine('sqlite:///sqlapi.db', echo = True)
import testing.postgresql
postgresql = testing.postgresql.Postgresql()
engine = create_engine(postgresql.url(), echo=True)

meta = MetaData()

alerts = Table(
   'alerts', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String(255), nullable=False),
   Column('labeler', String(255)),
   Column('store', String(255)),
   Column('label', String(255)),
   Column('comment', String(255)),
)

meta.create_all(engine)

My example prints the result below:

2020-10-20 14:50:00,327 INFO sqlalchemy.engine.base.Engine select version()
2020-10-20 14:50:00,327 INFO sqlalchemy.engine.base.Engine {}
2020-10-20 14:50:00,329 INFO sqlalchemy.engine.base.Engine select current_schema()
2020-10-20 14:50:00,329 INFO sqlalchemy.engine.base.Engine {}
2020-10-20 14:50:00,331 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-10-20 14:50:00,331 INFO sqlalchemy.engine.base.Engine {}
2020-10-20 14:50:00,332 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2020-10-20 14:50:00,332 INFO sqlalchemy.engine.base.Engine {}
2020-10-20 14:50:00,333 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2020-10-20 14:50:00,333 INFO sqlalchemy.engine.base.Engine {}
2020-10-20 14:50:00,334 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2020-10-20 14:50:00,334 INFO sqlalchemy.engine.base.Engine {'name': 'alerts'}
2020-10-20 14:50:00,337 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE alerts (
    id SERIAL NOT NULL, 
    name VARCHAR(255) NOT NULL, 
    labeler VARCHAR(255), 
    store VARCHAR(255), 
    label VARCHAR(255), 
    comment VARCHAR(255), 
    PRIMARY KEY (id)
)


2020-10-20 14:50:00,337 INFO sqlalchemy.engine.base.Engine {}
2020-10-20 14:50:00,343 INFO sqlalchemy.engine.base.Engine COMMIT

But is there a way to get the CREATE TABLE statement as a string without actually creating the table in a database and get it as a side effect ?

kriss · Accepted Answer · 2020-10-20 13:05:21Z

2

The code snippet below is doing exactly what I was looking for.

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, TIMESTAMP
from sqlalchemy.schema import CreateTable
import psycopg2
#engine = create_engine('sqlite:///sqlapi.db', echo = True)
import testing.postgresql
postgresql = testing.postgresql.Postgresql()
engine = create_engine(postgresql.url())

meta = MetaData()

alerts = Table(
   'alerts', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String(255), nullable=False),
   Column('labeler', String(255)),
   Column('store', String(255)),
   Column('label', String(255)),
   Column('comment', String(255)),
)

create_stmt = CreateTable(alerts).compile(engine)
print(create_stmt)

answered Oct 20, 2020 at 13:05

kriss

24.3k17 gold badges104 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Flavio Garcia Over a year ago

That's beautiful! Worked fine with a Base object also. create_stmt = CreateTable(AlertsBase.__table__).compile(engine) Without engine also worked but I assume the using generic Column types from sqlalchemy(i.e, Integer and String) that would be necessary.

kriss Over a year ago

I'm glad it was useful. I have spent maybe two hours figuring out how to do it from the doc.

Collectives™ on Stack Overflow

See SQL CREATE TABLE statement generated by SQLAlchemy without actually creating table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related