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的最大值，那么更改sql查询应该会有所帮助

 removed Id, text columns from select   this query will give absolute max value of ID
select max(Id) as lastid from Table1 where dataexecuted IS NULL AND text is not null 

  added Id, text in group by   this query will give max Id for each set of text column value.
select max(Id) as lastid, Id, text from Table1 where dataexecuted IS NULL AND text is not null group by Id, text

使用哪个查询取决于您的需求

而且不需要使用for循环来迭代结果集，从数据库中查找max值或任何aggregate值，这不是一种非常有效的方法

Answer 2

如果您只需要最大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()