How to create "GENERATED BY DEFAULT AS IDENTITY" field in Firebird 3 via SQLAlchemy?

Question

I want create column ID via SQLAlchemy.
SQL seems like:

CREATE TABLE OBJECT (
    ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR(15)
)

Or how to create table with autoincrement field?

Column(autoincrement='auto')

default for primary_key, but that not work for Firebird.

I add fdb to github.
In this string SQL-code what needed for that, but i don't know how setup Column in SQLAlchemy to use that.
Screenshot from IBExpert showing what create after use "GENERATED BY DEFAULT AS IDENTITY". That not same as a simple generator.

Answer 1

This postgresql work around might work: https://docs.sqlalchemy.org/en/13/dialects/postgresql.html

I believe you would have to modify the example for firebird by changing the compile dialect like this:

from sqlalchemy import MetaData
from sqlalchemy.schema import CreateColumn
from sqlalchemy.ext.compiler import compiles


@compiles(CreateColumn, 'firebird')
def use_identity(element, compiler, **kw):
    text = compiler.visit_create_column(element, **kw)
    text = text.replace("SERIAL", "INT GENERATED BY DEFAULT AS IDENTITY")
    return text

m = MetaData()
t = Table(
    't', m,
    Column('id', Integer, primary_key=True),
    Column('data', String)
)