Generate create table SQL from SQLAlchemy models and support MySQL specific features like AUTO_INCREMENT¶

We can use SQLAlchemy's CreateTable class to generate SQL of creating table.

Generate create table SQL from SQLAlchemy model¶

Here's the example code to generate SQL of creating table logs:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, BIGINT, VARCHAR, INTEGER
from sqlalchemy.schema import CreateTable

Base = declarative_base()


class Log(Base):
    __tablename__ = 'logs'
    id = Column(BIGINT, primary_key=True, autoincrement=True)  # auto increment PK
    content = Column(VARCHAR(200), nullable=False)
    user_id = Column(INTEGER)


print(CreateTable(Log.__table__))

The output is:

CREATE TABLE logs (
        id BIGINT NOT NULL, 
        content VARCHAR(200) NOT NULL, 
        user_id INTEGER, 
        PRIMARY KEY (id)
)

The problem is that, the primary key id is not an auto-increment!

Why? That's because SQLAlchemy does not know the database you are using and it has no idea how to make the PK auto-increment.

To fix it, we need to do more.

Support MySQL specific features like AUTO_INCREMENT¶

from sqlalchemy.dialects.mysql import dialect

print(CreateTable(Log.__table__).compile(dialect=dialect()))

The output is:

CREATE TABLE logs (
        id BIGINT NOT NULL AUTO_INCREMENT, 
        content VARCHAR(200) NOT NULL, 
        user_id INTEGER, 
        PRIMARY KEY (id)
)

Look, now AUTO_INCREMENT appeared.

Put it together¶

Final version of the code:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, BIGINT, VARCHAR, INTEGER
from sqlalchemy.dialects.mysql import dialect
from sqlalchemy.schema import CreateTable

Base = declarative_base()


class Log(Base):
    __tablename__ = 'logs'
    id = Column(BIGINT, primary_key=True, autoincrement=True)
    content = Column(VARCHAR(200), nullable=False)
    user_id = Column(INTEGER)


print(CreateTable(Log.__table__).compile(dialect=dialect()))