Set ``autoincrement`` value based on dialects in Sqlalchemy

I would like to control the value for the Column's argument autoincrement to be False for some dialects and True for others with Sqlalchemy.

Class Table(Base):
  Id = Column(Integer, autoincrement=False, primary_key=True)

In this case I am using my model against SqlServer and DuckDB. Is this possible?

Solution

In principle* you can do this by customising the compilation of the CreateColumn construct. I don't have access to MSSQL or DuckDB, so this example uses MySQL, but the approach would be the same regardless of dialect.

import sqlalchemy as sa
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.schema import CreateColumn


# Specify the dialect(s) that require the behaviour in the 
# decorator's arguments. You can create multiple decorated functions if 
# necessary, for example one for each dialect that deviates from standard
# behaviour.
@compiles(CreateColumn, 'mysql')
def my_column(element, compiler, **kw):
    column = element.element
    column.autoincrement = True
    return compiler.visit_create_column(element, **kw)


tbl = sa.Table('foos', sa.MetaData(), sa.Column('id', sa.Integer, primary_key=True))


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


engine = sa.create_mock_engine('mysql://', my_dump)
tbl.create(engine)

The script generates this output:

CREATE TABLE foos (
        id INTEGER NOT NULL AUTO_INCREMENT, 
        PRIMARY KEY (id)
)

* I'm assuming that setting the "right" value of the flag does what you want for each dialect, and you only want a way to condition the value of the flag based on the dialect in use.