CODEWITHSUNDEEP
pythonsql-serverpandaspyodbc
Manz
Manz

Reputation: 605

SQL :How to get the latest/max Id from the select query using pyodbc

I am trying to execute the SQL select query using the pyodbc library but not getting the way to find the last or the max row ID from the Database.

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)

I Have also tried to find the data in this way but Shows error

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

Upvotes: 0

Views: 4057

Answers (3)

Christoph Kröger
Christoph Kröger

Reputation: 31

To get the maximum id from database use the following:

cursor.execute("select max(id) from Table1 where dataexecuted IS NULL AND text is not null;")
result = cursor.fetchone()
conn.commit;

The result will have the structure (id,). You can select it by result[0].

If you write

cursor.execute("select max(id) from Table1 where dataexecuted IS NULL AND text is not null;").fetchone()

you'll get probably AttributeError: 'NoneType' object has no attribute 'fetchone', because the reture object of execute is none.

Upvotes: 0

gvee
gvee

Reputation: 17171

If all you want is the maximum ID value then you can do this in a very simple query:

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

You can then use cursor.fetchone() to obtain the single row resultset.

UPDATE: alternative to fetchone() is fetchval() for single, scalar values.

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

Upvotes: 1

Avneet Singh
Avneet Singh

Reputation: 82

May be the exact requirement is not clear from the question. But if you just want to resolve the error and find the max value of Id, then changing your sql query should help.

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

which query to use depends on your requirement.

And there is no need to iterate result set using for loop to find max values or any aggregate values from database that is not a very efficient approach.

Upvotes: 0

Related Questions