[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

[susodapop]

Thanks for opening this issue. You've hit one of the limitations of this dialect, which is documented here. Namely, the autoincrement argument is currently ignored. The handling of server_default= isn't captured in the sqlalchemy compliance test suite either so it's effectively untested.

This feature is a focus for development. Would you please send an email to databricks-sql-connector-maintainers@databricks.com so we can associate your organization with the internal work item?

The most important thing we need to know is what version of sqlalchemy you have installed.

[wibbico]

This is bad, I've read all the documentation, but it seemed that this was just a DDL limitation.

We have an application scenario where we need to be able to insert objects. This could be a showstopper for us.

Is there any timelime for this feature?

We are currently using SQLAlchemy 2.0.25.

[susodapop]

I've seen your email and will respond during normal work hours (middle of the night for me at present). But have you looked at the documentation Iinked above? You are able to set autoincrementing columns with this dialect. You just can't use the autoincrement= argument on your columns. Instead, you have to declare the identity for the column explicitly. An example of this is given.

[wibbico]

This was the documentation i referred to, but unfortunately it does not work

from sqlalchemy import Identity, String

class SomeModel(Base):
    id      = BigInteger(Identity())
    value   = String()

Raises error

TypeError: BigInteger() takes no arguments

How can I fix the example from above?

[susodapop]

Indeed, this is an issue with the example. It should be Column(BigInteger, Identity()).

…

On Tue, Jan 23, 2024 at 3:42 AM Wibbico ***@***.***> wrote: This <https://github.com/databricks/databricks-sql-python/blob/main/src/databricks/sqlalchemy/README.sqlalchemy.md#identity-and-autoincrement> was the documentation i referred to, but unfortunately it does not work from sqlalchemy import Identity, String class SomeModel(Base): id = BigInteger(Identity()) value = String() Raises error TypeError: BigInteger() takes no arguments — Reply to this email directly, view it on GitHub <#323 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AECG7BYIA24ADIKOF5XWGFLYP5ZWZAVCNFSM6AAAAABCE3OX3CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMBVGU3DMNJQHA> . You are receiving this because you commented.Message ID: ***@***.***>

[wibbico]

Iam trying to get as close to the docs as possible, still no success:

@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 dummy2")
            session.execute(rollback_query_kopf)
            session.commit()


class Base(DeclarativeBase):
    pass


class SomeModel(Base):
    __tablename__ = "dummy2"

    # primary_key=True is needed: sqlalchemy.exc.ArgumentError: 
    # Mapper Mapper[SomeModel(...)] could not assemble any primary key columns for mapped table 'dummy2'
    id = Column(BigInteger, Identity(), primary_key=True)
    value = Column(String)


def test_insert_row_autogenerate(fix_session):
    sample = SomeModel(value="test")
    fix_session.add(sample)
    fix_session.commit()

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

    assert actual

Still error

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

[susodapop]

I will need to put eyes on this when I am near a workstation. You've caught me with a touch of insomnia and checking emails from my phone. Will respond during business hours in the US.

…

On Tue, Jan 23, 2024 at 3:59 AM Wibbico ***@***.***> wrote: Iam trying to get as close to the docs as possible, still no success: @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 dummy2") session.execute(rollback_query_kopf) session.commit() class Base(DeclarativeBase): pass class SomeModel(Base): __tablename__ = "dummy2" # primary_key=True is needed: sqlalchemy.exc.ArgumentError: # Mapper Mapper[SomeModel(...)] could not assemble any primary key columns for mapped table 'dummy2' id = Column(BigInteger, Identity(), primary_key=True) value = Column(String) def test_insert_row_autogenerate(fix_session): sample = SomeModel(value="test") fix_session.add(sample) fix_session.commit() actual = fix_session.query(SomeModel).first() assert actual Still error sqlalchemy.orm.exc.FlushError: Instance <SomeModel at 0x7fd5b9134050> 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. — Reply to this email directly, view it on GitHub <#323 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AECG7B3GSYJCT6XXEQQO4WDYP53X7AVCNFSM6AAAAABCE3OX3CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMBVGU4TIMBZGE> . You are receiving this because you commented.Message ID: ***@***.***>

[susodapop]

Alright I've confirmed what's going on here and pushed a branch with a test file here to show my work.

tl;dr if you are inserting records with sqlalchemy core, then auto-incrementing works just fine. if you need to use the ORM it will fail with the error you see because databricks-sql-connector has no way to return the id of the last inserted row.

I'm researching internally to see what is the status on adding the bolded behaviour above.

Here's an example without ORM that works

class TestIdentityColumnBehavior(IdentityTestBase):
    @pytest.fixture
    def table(self, engine):
        md = MetaData()
        tbl = Table(
            "foo",
            md,
            Column("bar", String, comment="column comment"),
            Column("id", BigInteger, Identity()),
        )
        md.create_all(bind=engine)

        yield tbl

        md.drop_all(bind=engine)

    def test_identity_column_autoincrements(self, engine: Engine, table: Table):
        stmt1 = table.insert().values(bar="baz")
        stmt2 = table.select()
        with engine.begin() as conn:
            conn.execute(stmt1)
            result = conn.execute(stmt2).fetchone()

        assert result.id == 1

[susodapop]

Will need to update our documentation with more specificity about what's supported at the moment. For your usage, are you able to drop into sqlalchemy core rather than using the ORM?

[kostahuffman]

Alright I've confirmed what's going on here and pushed a branch with a test file here to show my work.

tl;dr if you are inserting records with sqlalchemy core, then auto-incrementing works just fine. if you need to use the ORM it will fail with the error you see because databricks-sql-connector has no way to return the id of the last inserted row.

I'm researching internally to see what is the status on adding the bolded behaviour above.

Here's an example without ORM that works

class TestIdentityColumnBehavior(IdentityTestBase):
@pytest.fixture
def table(self, engine):
md = MetaData()
tbl = Table(
"foo",
md,
Column("bar", String, comment="column comment"),
Column("id", BigInteger, Identity()),
)
md.create_all(bind=engine)

    yield tbl

    md.drop_all(bind=engine)

def test_identity_column_autoincrements(self, engine: Engine, table: Table):
    stmt1 = table.insert().values(bar="baz")
    stmt2 = table.select()
    with engine.begin() as conn:
        conn.execute(stmt1)
        result = conn.execute(stmt2).fetchone()

    assert result.id == 1

Hi @susodapop, thanks for taking a look at this. By any chance do you know if this issue is still valid / has been addressed in another ticket? or if this change is possible.