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.

`Column(Integer, primary_key=True)` if It doesn't work, you should provide more details. — metmirr, Mar 05 '17 at 19:33
Have you tested it? I mean did you insert a record and checked it? — metmirr, Mar 05 '17 at 19:57
You should not insert null in the primary key, you need to leave the column out of the insert statement. The `BY DEFAULT` indicates that it can be overridden by the user, which happens when you explicitly set it to `null`, and the fact it's a primary key comes into play: primary keys are not allowed to be null. — Mark Rotteveel, Mar 06 '17 at 14:00

score 1 · Answer 1 · answered Oct 05 '19 at 22:03

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)
)

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

1 Answers1