在pyodbc中使用INSERT ... OUTPUT时如何获取IDENTITY值 [英] How to get the IDENTITY value when using INSERT ... OUTPUT with pyodbc

问题描述

我正在尝试使用 OUTPUT 获取新插入行的 ID.但是，我遇到了 HY010 错误.以下查询/代码是我使用的:

I am trying to get the ID of a newly inserted row by using OUTPUT. However, I encountered the HY010 error. The following query/code is what I use:

string = """
         SET NOCOUNT ON;
         DECLARE @NEWID TABLE(ID INT);

         INSERT INTO dbo.t1 (Username, Age)
         OUTPUT inserted.id INTO @NEWID(ID)
         VALUES(?, ?)

         SELECT ID FROM @NEWID
         """

cursor.execute(string, "John Doe", 35)
cursor.commit()
id = cursor.fetchone()[0]

最后一行 id = cursor.fetchone()[0] 导致 HY010 错误(见下文).任何建议将不胜感激！

the last line id = cursor.fetchone()[0] led to a HY010 error (see below). Any advice would be greatly appreciated!

pyodbc.Error: ('HY010', '[HY010] [Microsoft][ODBC SQL Server Driver]Function sequence error (0) (SQLFetch)')

推荐答案

我能够重现您的问题，并且通过在 INSERT 和 before 提交.也就是说，代替

I was able to reproduce your issue, and I was able to avoid it by retrieving the id value immediately after the INSERT and before the commit. That is, instead of

cursor.execute(string, "John Doe", 35)
cursor.commit()
id = cursor.fetchone()[0]

我做到了

cursor.execute(string, "John Doe", 35)
id = cursor.fetchone()[0]  # although cursor.fetchval() would be preferred
cursor.commit()

这篇关于在pyodbc中使用INSERT ... OUTPUT时如何获取IDENTITY值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！