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)

Most upvoted comments

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:

param = param.replace("'", "''")
return f"'{param}'"