CODEWITHSUNDEEP
pythonsqlalchemypyodbc
adt100
adt100

Reputation: 31

Function sequence error in Python from pyodbc when getting id of row inserted into MSSQL

I am trying to add a new auto incremented row in an MSSQL database and read the id value created. When I do the SQL statement runs okay but as soon as I try to query the row from the returned ResultProxy object I get a 'Function sequence error'

I am using SQL Alchemy (pyodbc) to connect to the database and using execute() to run text queries. Running the SQL command via SQL Management Studio works fine.

This is what I am trying;

from sqlalchemy import create_engine
from sqlalchemy.sql import text
dbstring = "mssql+pyodbc://<SNIPPED>"
engine = create_engine(dbstring)
con = engine.connect()
res = con.execute(text("""INSERT INTO test(v) OUTPUT INSERTED.id VALUES( :v )"""), {"v": 10})

print(res) # This prints <sqlalchemy.engine.result.ResultProxy object>
print(res.first()) # This is the line where things go wrong

The final line of code causes the following error;

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY010', '[HY010] [Microsoft][SQL Server Native Client 11.0]Function sequence error (0) (SQLFetch)')

Any ideas what I am doing wrong?

Thanks, Andrew

Upvotes: 3

Views: 2684

Answers (1)

Simone
Simone

Reputation: 435

Use engine.begin() instead engine.connect() like so:

with engine.begin() as con:
    new_id = con.execute(text("""INSERT INTO test(v) OUTPUT INSERTED.id VALUES( :v )"""), {"v": 10}).scalar()

Related question: Get MSSQL autogenerated id from INSERT Statement using SQLAlchemy

Upvotes: 1

Related Questions