CreateTable doesn't specify AUTO INCREMENT or indexes

[Shatil Rafiullah]

Is CreateTable the best way to get the SQL syntax for table creation?

If I run my program altogether, I see it in the echoed SQL output, but it seems like there has to be a better way. I'm at a loss for how to get AUTO INCREMENT sequences to print along with table creation syntax.

>>> table = MyModel.__table__
>>> print sqlalchemy.schema.CreateTable(table)

This will print something like:

CREATE TABLE table_name (
        sequence INTEGER NOT NULL, 
        id VARCHAR(128) NOT NULL, 
        status VARCHAR(128) NOT NULL, 
        status_message TEXT, 
        PRIMARY KEY (sequence)
)

It doesn't close the SQL statement with a semicolon. To get indexes/indices, I must write:

for index in table.indexes:
    print sqlalchemy.schema.CreateIndex(index)

To get:

CREATE INDEX ix_table_name_id ON table_name (id)
CREATE INDEX ix_table_name_status ON table_name (status)

Now, compare the above to the echoed SQL output:

2014-12-22 09:10:58,697 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE table_name (
        sequence INTEGER NOT NULL AUTO_INCREMENT, 
        id VARCHAR(128) NOT NULL, 
        status VARCHAR(128) NOT NULL, 
        status_message TEXT, 
        PRIMARY KEY (sequence),
)

2014-12-22 09:10:58,723 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_table_name_id ON table_name (id)

2014-12-22 09:10:58,744 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_table_name_status ON table_name (status)

[Mike Bayer]

It doesn't close the SQL statement with a semicolon.

this is not needed for SQL execution. the semicolon is an artifact of your SQL shell, just append it.

To get indexes/indices, I must write:

indexes are separate CREATE statements which would need to be passed to execute() separately, so yes these are necessarily separate in this context. We are dealing with single statement at a time.

I'm at a loss for how to get AUTO INCREMENT sequences to print along with table creation syntax.

follow the guidelines in the FAQ (sorry, readthedocs seems to be down at the moment), search for "How can I get the CREATE TABLE/ DROP TABLE output as a string? " since it's down, here's the text:

#!

How can I get the CREATE TABLE/ DROP TABLE output as a string?

Modern SQLAlchemy has clause constructs which represent DDL operations. These can be rendered to strings like any other SQL expression:

    from sqlalchemy.schema import CreateTable

    print CreateTable(mytable)

To get the string specific to a certain engine:

    print CreateTable(mytable).compile(engine)

that is, CreateTable(table).compile(my_engine), or CreateTable(table).compile(dialect=mysql.dialect()).

[Mike Bayer]

these are usage issues, so please continue on the SQLAlchemy mailing list, thanks.

[Shatil Rafiullah]

Thanks @zzzeek for humoring me!

mysql.dialect() (from sqlalchemy.dialects) got me the AUTO_INCREMENT statement like you said it would.

I had previously tried CreateTable(table).compile(my_engine), but the lacked it, though maybe I did something wrong along the way.