SQL：如何使用pyodbc从select查询中获取最新的/max Id

Question

我试图使用pyodbc库执行sqlselect查询，但无法从数据库中找到最后一行或最大行ID

import pyodbc
conn = pyodbc.connect(r"Driver={ODBC Driver 13 for SQL Server}; Server=XXXX; Database=sampleDb; Trusted_Connection=yes;")
cursor = conn.cursor()

cursor.execute("select Id, text from Table1 where dataexecuted IS NULL AND text is not null")
newdata = cursor.fetchall()
for l in newdata:
   rowId = l.Id
   rowId = max(rowId)
   print(rowId)

我也尝试过用这种方法查找数据，但显示出错误

select max(Id) as lastid, Id, text from Table1 where dataexecuted IS NULL AND text is not null

Answer 1

如果您只需要最大ID值，那么您可以通过一个非常简单的查询来实现这一点：

SELECT Max(Id) AS maximum_id
FROM   Table1
WHERE  dataexecuted IS NULL
AND    text IS NOT NULL
;

然后可以使用cursor.fetchone()来获取单行结果集

更新：单个标量值的fetchone()替代方法是fetchval()

maxid = cursor.execute("select max(Id) from Table1;").fetchval()