SNOW-2331576: sqlalchemy.orm.exc.FlushError: NULL identity key for primary key column with when using Identity()

[gouthamblend360]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

python 3.13

2. What operating system and processor architecture are you using?

   Windows 11

3. What are the component versions in the environment (pip freeze)?

   alembic==1.16.5
   annotated-types==0.7.0
   anyio==4.10.0
   asn1crypto==1.5.1
   bcrypt==4.0.1
   boto3==1.40.22
   botocore==1.40.22
   certifi==2025.8.3
   cffi==1.17.1
   charset-normalizer==3.4.3
   click==8.2.1
   colorama==0.4.6
   cryptography==45.0.7
   dnspython==2.7.0
   email-validator==2.3.0
   fastapi==0.116.1
   filelock==3.19.1
   greenlet==3.2.4
   h11==0.16.0
   idna==3.10
   jmespath==1.0.1
   Mako==1.3.10
   MarkupSafe==3.0.2
   packaging==25.0
   passlib==1.7.4
   platformdirs==4.4.0
   pycparser==2.22
   pydantic==2.11.7
   pydantic-settings==2.10.1
   pydantic_core==2.33.2
   PyJWT==2.10.1
   pyOpenSSL==25.1.0
   python-dateutil==2.9.0.post0
   python-dotenv==1.1.1
   python-multipart==0.0.20
   pytz==2025.2
   requests==2.32.5
   s3transfer==0.13.1
   six==1.17.0
   sniffio==1.3.1
   snowflake-connector-python==3.17.2
   snowflake-sqlalchemy==1.7.6
   sortedcontainers==2.4.0
   SQLAlchemy==2.0.43
   starlette==0.47.3
   tomlkit==0.13.3
   typing-inspection==0.4.1
   typing_extensions==4.15.0
   urllib3==2.5.0
   uvicorn==0.35.0

4. What did you do?

   Tried to create a new record into the table. But failed with the 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.

    `model= ModelA(**data.model_dump())
    db.add(model)
    db.commit()
    db.refresh(model)`
   
   `class Model(Base):
      id = Column(Integer, Identity(always=True,on_null=False, start=1, increment=1), primary_key=True)`
   

5. What did you expect to see?

   It should create the record in the db

[sfc-gh-dszmolka]

(unfortunately we're in maintenance mode, see README in the repo, unable to give any kind of timeline for when the issue actually will be addressed by the team. if it's critical to you, please follow the steps outlined in the README and raise a support case)

[sfc-gh-dszmolka]

The library is now out of maintenance mode 🎉, so reopening this issue. so it could be addressed per available resources.