"NULL identity key" error using SQLAlchemy's base automap to reflect a postgres DB using IDENTITY columns

Question

I have a postgres database that I'm trying to reflect that uses the now standard "Identity" column for primary keys.

Here's my table definition:

create table class_label (
    class_label_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    class_name varchar not null,
    default_color varchar,
    created_dttm timestamp default current_timestamp NOT NULL,
    created_by varchar DEFAULT USER NOT NULL,
    updated_dttm timestamp default current_timestamp NOT NULL,
    updated_by varchar DEFAULT user NOT NULL
);

And here's my code:

from sqlalchemy import create_engine, MetaData, insert, Table, or_, and_, func
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import scoped_session, sessionmaker
import os

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

@compiles(CreateColumn, 'postgresql')
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

usr = os.environ.get("POSTGRES_USR")
pwd = os.environ.get("POSTGRES_PWD")
host = os.environ.get("POSTGRES_HOST")

engine = create_engine('postgresql://' + usr + ':' + pwd + host, convert_unicode=True)
session = scoped_session(sessionmaker(bind=engine))
metadata = MetaData(bind=engine)

metadata.reflect(engine, only=['class_label'])
Base = automap_base(metadata=metadata)
Base.prepare()

Class_Label = Base.classes.class_label

session.add(Class_Label(class_name="Testing", default_color="red"))
session.commit()

When I run my code, I get this error:

sqlalchemy.orm.exc.FlushError: Instance <class_label at 0x1091c4ba8> has a NULL identity key.
If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values. Ensure also that this flush() is not occurring at an inappropriate time, such as within a load() event.

Per https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#postgresql-10-identity-columns I understand that this is somewhat a shortcoming of SQLAlchemy, but I'm wondering if the work-around they suggest can work for auto-mapped/reflected databases and how I would implement it.

I'm using SQLAlchemy 1.3.16 and Postgres 11.

Answer 1

A fix was added in SQLAlchemy 1.4