如何通过 QSqlQuery.lastInsertId() 从 SQL Server 获取最后插入行的 ID?

问题描述：

如何通过 PyQt4.QtSql 模块从 SQL Server 获取最后插入行的 ID?现在我使用的是 SQL Server 2012 Express，但程序也必须在 SQL Server 2000 上运行.

How can I get ID of last inserted row from SQL Server by PyQt4.QtSql module? Now I'm using SQL Server 2012 Express, but program has to work also on SQL Server 2000.

这是我的代码(Python + PyQt)和结果:

Here is my code (Python + PyQt) and results:

from PyQt4.QtGui import QApplication
from PyQt4 import QtSql

app = QApplication([])
db = QtSql.QSqlDatabase.addDatabase("QODBC")
db.setDatabaseName('Driver={SQL Server Native Client 11.0};Server=(localdb)\\v11.0;')
db.open()

query = QtSql.QSqlQuery()
query.prepare("""CREATE TABLE Test(
    ID INT PRIMARY KEY IDENTITY(1, 1),
    Row nvarchar(255)
)
""")
query.exec_()

query = QtSql.QSqlQuery()
query.prepare('INSERT Test OUTPUT Inserted.ID VALUES(?)')
query.bindValue(0, 'Test')

query.exec_()

while query.next():
    last_inserted_id = query.value(0)

print('OUTPUT: ', last_inserted_id)
print('QSqlQuery.lastInsertId: ', query.lastInsertId())

query = QtSql.QSqlQuery('SELECT SCOPE_IDENTITY()')

while query.next():
    last_inserted_id_ = query.value(0)

print('SCOPE_IDENTITY: ', last_inserted_id_)

db.close()

结果:

OUTPUT:  1
QSqlQuery.lastInsertId:  None
SCOPE_IDENTITY:  <PyQt4.QtCore.QPyNullVariant object at 0x00000000032D88D0>

不幸的是，OUTPUT 子句支持 SQL Server 2005 或更高版本.

Unfortunately OUTPUT Clause is supported by SQL Server 2005 or above.

Python 3.2.3 (x64)、PyQt 4.9.4、SQL Server 2012 Express

Python 3.2.3 (x64), PyQt 4.9.4, SQL Server 2012 Express

有什么想法吗?

到目前为止，我使用 SELECT @@IDENTITY.

So far I use SELECT @@IDENTITY.

答

将我的评论移至一个答案以允许其干净利落地关闭:

Moving my comment to an answer to allow this to be closed cleanly:

我不懂 Python，但我认为 SCOPE_IDENTITY() 只能在批处理中使用.因此，您可能希望使用 INSERT 将 ;SELECT SCOPE_IDENTITY() 添加到查询中.希望这会有所帮助.

I don't know Python, but I think SCOPE_IDENTITY() only works within a batch. So, you might want to add the ;SELECT SCOPE_IDENTITY() to the query with the INSERT. Hope this helps.

因此您的插入内容可能如下所示:

So your Insert could look like:

query = QtSql.QSqlQuery() 
query.prepare('INSERT Test VALUES(?); SELECT SCOPE_IDENTITY()') 
query.bindValue(0, 'Test') 

query.exec_() 

while query.next(): 
    last_inserted_id = query.value(0)

如何通过 QSqlQuery.lastInsertId() 从 SQL Server 获取最后插入行的 ID?

相关推荐