如何从SQL Server获取QSqlQuery.lastInsertId（）的最后插入行的ID？

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.

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

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

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

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()

Results:

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

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

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

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

Any ideas?

Edit:

So far I use SELECT @@IDENTITY.

到目前为止,我使用SELECT @@ IDENTITY。

2 个解决方案

#1

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

将我的评论移至答案,以便彻底关闭它:

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.

我不知道Python,但我认为SCOPE_IDENTITY()仅适用于批处理。因此,您可能希望使用INSERT将SELECT SCOPE_IDENTITY()添加到查询中。希望这可以帮助。

So your Insert could look like:

所以你的Insert看起来像:

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)

#2

QSqlQuery Class has a method lastInsertId which returns the id of the last inserted row.

QSqlQuery类有一个方法lastInsertId,它返回最后一个插入行的id。

query = QtSql.QSqlQuery() 
query.exec_('INSERT Test (id, name) VALUES(1, 'Test')')

# Get the ID of the last inserted row
query.lastInsertId() # Output : 1

#1