[sqlalchemy] Exception: Instance %s has a NULL identity key

[wibbico]

I cant get autogenerated identity columns to work.

The table is created just fine, but sqlalchelmy cant insert any rows

Error:

sqlalchemy.orm.exc.FlushError: Instance <SampleObject at 0x7fc694ddbd90> 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.

Sample code:

from sqlalchemy import create_engine, text, Column, BigInteger, String, Identity
from sqlalchemy.orm import Session, DeclarativeBase

from sample import databricks_engine


@pytest.fixture()
def fix_session():
    engine = databricks_engine()
    try:
        with Session(engine) as session:
            Base.metadata.create_all(engine)
            yield session
    finally:
        with Session(engine) as session:
            rollback_query_kopf = text("DROP TABLE dummy")
            session.execute(rollback_query_kopf)
            session.commit()

class Base(DeclarativeBase):
    pass

class SampleObject(Base):
    __tablename__ = "dummy"

    bigint_col = Column(
        BigInteger, primary_key=True, server_default=Identity(), autoincrement="auto"
    )
    str_col = Column(String)

def test_insert_row_autogenerate(fix_session):
    sample = SampleObject(str_col="test")
    fix_session.add(sample)
    fix_session.commit()

    actual = fix_session.query(SampleObject).first()

    assert actual

[chetkhatri]

PING for review - @mrt @marmbrus @ahirreddy @holdenk