SNOW-1920422: primary key has null identity key when inserting values into a table

[nuwanda94]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

   Python 3.11.9

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

Windows-10-10.0.22631-SP0

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

asn1crypto==1.5.1
blinker==1.9.0
certifi==2024.12.14
cffi==1.17.1
charset-normalizer==3.4.1
click==8.1.8
colorama==0.4.6
cryptography==44.0.0
filelock==3.16.1
Flask==3.1.0
Flask-Login==0.6.3
Flask-SQLAlchemy==3.1.1
greenlet==3.1.1
idna==3.10
itsdangerous==2.2.0
Jinja2==3.1.5
MarkupSafe==3.0.2
numpy==2.2.1
packaging==24.2
pandas==2.2.3
platformdirs==4.3.6
pyarrow==19.0.0
pycparser==2.22
PyJWT==2.10.1
pyOpenSSL==24.3.0
python-certifi-win32==1.6.1
python-dateutil==2.9.0.post0
pytz==2024.2
requests==2.32.3
setuptools-scm==8.1.0
six==1.17.0
snowflake-connector-python==3.12.4
snowflake-sqlalchemy==1.7.3
sortedcontainers==2.4.0
SQLAlchemy==2.0.36
tomlkit==0.13.2
typing_extensions==4.12.2
tzdata==2024.2
urllib3==2.3.0
Werkzeug==3.1.3
wrapt==1.17.0

4. What did you do?

I have the following model class of flask :

class Demo(Base):

id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, nullable=False)
created_by = Column(Integer)
updated_by = Column(Integer)
date_added = Column(TIMESTAMP, server_default=func.current_timestamp())
updated_at = Column(TIMESTAMP, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

and i created the table in snowflake using the following :

Demo.metadata.create_all(engine)

5. What did you expect to see?

   When inserting values in the table it gives me error that Instance <Demo at 0x17d02057e50> 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.

as the id column is set as primary and autoincrement i should be able to insert the row without passing the id value but it does not seems to be happening.

when i execute the same query using the snowflake UI i am able to insert records in the table

i am unable to map the primary key column object to database i have tried passing parameters autoincrement, Indentity(1,1) but the error persists.

6. Can you set logging to DEBUG and collect the logs?

INFO:sqlalchemy.engine.Engine:INSERT INTO "DEMO" (user_id,created_by, updated_by) VALUES (%(user_id)s, %(created_by)s, %(updated_by)s)
DEBUG:sqlalchemy.engine.Engine:Col ('number of rows inserted',)
INFO:sqlalchemy.engine.Engine:ROLLBACK
Instance <Demo at 0x1a4494f3290> 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.

<!--
If you need urgent assistance reach out to support for escalated issue processing https://community.snowflake.com/s/article/How-To-Submit-a-Support-Case-in-Snowflake-Lodge
-->

[sfc-gh-dszmolka]

hi - thanks for raising this with us. based on your snippet, I tried this:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, create_engine, func, insert
from sqlalchemy.orm import declarative_base
from sqlalchemy.sql import text
from snowflake.sqlalchemy import TIMESTAMP, URL

import os
import logging

for logger_name in ['sqlalchemy', 'snowflake','botocore']:
	logger = logging.getLogger(logger_name)
	logger.setLevel(logging.DEBUG)
	ch = logging.FileHandler('python_connector.log')
	ch.setLevel(logging.DEBUG)
	ch.setFormatter(logging.Formatter('%(asctime)s - %(threadName)s %(filename)s:%(lineno)d - %(funcName)s() - %(levelname)s - %(message)s'))
	logger.addHandler(ch)

Base = declarative_base()

class Demo(Base):
    __tablename__ = "ISSUE575"
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, nullable=False)
    created_by = Column(Integer)
    updated_by = Column(Integer)
    date_added = Column(TIMESTAMP, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

engine = create_engine(URL(
    account = os.environ["SFACCOUNT"],
    user = os.environ["SFUSER"],
    password = os.environ["SFPASS"],
    database = os.environ["SFDB"],
    schema = os.environ["SFSCHEMA"],
    warehouse = os.environ["SFWH"],
    role = os.environ["SFROLE"],
))

insert_statement = insert(Demo).values(user_id=1, created_by=10, updated_by=20)
select_statement = text("SELECT * FROM ISSUE575")

with engine.connect() as conn:
    Base.metadata.create_all(engine)

    insert_result = conn.execute(insert_statement)
    conn.commit()

    select_result = conn.execute(select_statement).fetchall()
    print(select_result[0])

    Base.metadata.drop_all(engine)

and it seems to work, as in the values are inserted even without passing the id ; the SELECT in the middle of the flow shows:

# python test.py 
(1, 1, 10, 20, datetime.datetime(2025, 2, 11, 2, 10, 28, 21000), datetime.datetime(2025, 2, 11, 2, 10, 28, 21000))

so not entirely sure what's going wrong in your case. I'm using the following snowflake packages:

# pip freeze | grep -i ^snow
snowflake-connector-python==3.13.2
snowflake-sqlalchemy==1.7.3

I see we have a different version in PythonConnector, which should not really matter.

Anyhow - could you please try the above script and see if it works for you ? It also should generate a verbose log at python_connector.log in the current directory, there you might even see clues what is going wrong.

If my reproduction differs from what you're trying to do, can you please send a full runnable script which I can execute on my end and see the issue happening for myself? Thank you in advance !

[nuwanda94]

Hi @sfc-gh-dszmolka,

this works for me, will be looking into my code to check what went wrong at my end

i think we can close this issue !!

thanks for resolving this!

[sfc-gh-dszmolka]

hi @nuwanda94 good to hear you're unblocked now. Marking this as closed.