sqlalchemy: SYBASE: Get ID of last inserted row returns 0
Ref: #5294 - currently driver is deprecated, but may accept patch for now 🤞
Describe the bug When inserting a new row into an IDENTITY column, the ID of the new row incorrectly returns 0 when using ORM. This also makes it impossible to refresh the model.
This could definitely be something that I am doing wrong, so I will provide as much detail as possible.
Expected behavior It is very important to get the correct ID of the model inserted from the database.
To Reproduce
Model definition:
class Table(Base):
__tablename__ = "mytable"
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True, nullable=True) # Note: I have tried removing autoincrement
type = sa.Column(sa.CHAR(4))
description = sa.Column(sa.VARCHAR(255))
create_date = sa.Column(sa.DATETIME)
Create session:
from datetime import datetime
import pyodbc
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
connect_args = {"autoconnect": True}
creator = pyodbc.connect(
database=database,
user=username,
password=password,
driver="FreeTDS",
host=host,
port=port,
**connect_args,
)
engine = create_engine(
"sybase+pyodbc://",
creator=creator,
connect_args=connect_args,
)
session = Session(bind=engine)
Direct SQL execute works:
session.execute(
"INSERT INTO mytable (type, description, create_date) "
f"VALUES ('TEST', 'TEST', '{datetime.utcnow().isoformat()}')"
)
print(session.scalar("SELECT @@identity"))
5436789 # example of number returned
Fails using execute with model:
result = session.execute(
sa.insert(Table),
dict(
type="TEST",
description="TEST",
create_date=datetime.utcnow().isoformat(),
)
)
print(result.inserted_primary_key[0])
print(session.scalar("SELECT @@identity"))
2020-05-06 08:22:44,456 INFO sqlalchemy.engine.base.Engine SELECT user_name() as user_name
2020-05-06 08:22:44,460 INFO sqlalchemy.engine.base.Engine ()
2020-05-06 08:22:44,506 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-05-06 08:22:44,507 INFO sqlalchemy.engine.base.Engine ()
2020-05-06 08:22:44,548 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2020-05-06 08:22:44,551 INFO sqlalchemy.engine.base.Engine ()
2020-05-06 08:22:44,613 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-05-06 08:22:44,617 INFO sqlalchemy.engine.base.Engine INSERT INTO mytable (type, description, create_date) VALUES (?, ?, ?)
2020-05-06 08:22:44,620 INFO sqlalchemy.engine.base.Engine ('TEST', 'TEST', '2020-05-06T13:22:43.923404')
0
2020-05-06 08:22:44,727 INFO sqlalchemy.engine.base.Engine SELECT @@identity
2020-05-06 08:22:44,729 INFO sqlalchemy.engine.base.Engine ()
0
Fails using add with model:
tbl = Table(
type="TEST",
description="TEST",
create_date=datetime.utcnow().isoformat(),
)
session.add(tbl)
session.flush()
print(tbl.id)
2020-05-06 08:24:45,736 INFO sqlalchemy.engine.base.Engine SELECT user_name() as user_name
2020-05-06 08:24:45,740 INFO sqlalchemy.engine.base.Engine ()
2020-05-06 08:24:45,789 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-05-06 08:24:45,794 INFO sqlalchemy.engine.base.Engine ()
2020-05-06 08:24:45,839 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2020-05-06 08:24:45,841 INFO sqlalchemy.engine.base.Engine ()
2020-05-06 08:24:45,901 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-05-06 08:24:45,902 INFO sqlalchemy.engine.base.Engine INSERT INTO mytable (type, description, create_date) VALUES (?, ?, ?)
2020-05-06 08:24:45,903 INFO sqlalchemy.engine.base.Engine ('TEST', 'TEST', '2020-05-06T13:24:45.580975')
0
Versions.
- OS: Linux-4.15.0-99-generic-x86_64-with-Ubuntu-18.04-bionic
- Python: 3.7.5
- SQLAlchemy: 1.3.16
- Database: sybase (Adaptive Server Enterprise/16.0 SP02)
- DBAPI: pyodbc
About this issue
- Original URL
- State: closed
- Created 4 years ago
- Comments: 18 (18 by maintainers)
PR merged into external dialect. Version 1.0.3 released.
Thanks again, @snowman2 !
I purposely didn’t give you one so that you would be responsible for it 😃
however, may I recommend you do it like this: