CODEWITHSUNDEEP
pythonsqlpyodbc
Stephen K. Karanja
Stephen K. Karanja

Reputation: 498

How to get correct row count when using pyodbc cursor.executemany()

In pyodbc, cursor.rowcount works perfectly when using cursor.execute(). However, it always returns -1 when using cursor.executemany().

How does one get the correct row count for cursor.executemany()?

This applies to multiple inserts, updates, and deletes.

Upvotes: 7

Views: 10717

Answers (2)

danieldiasm
danieldiasm

Reputation: 36

I believe that you can do first another database query, avoiding this problem of Pyodbc, having the line count then.

Create another cursor before the actual desired query cursor, "cursor0" for example. Then you make a SELECT COUNT(*) FROM desiredTable WHERE desiredConditions.

I think it is easier if you can see a bit of the code.

cursor = cnxn.cursor()
cursor2 = cnxn.cursor()

cursor.execute("SELECT COUNT(*) FROM myTable")
row = cursor.fetchone()
print (str(row[0]))

cursor2.execute("SELECT * FROM myTable")
row = cursor2.fetchone()
while row:
    print(str(row))

I hope that I've helped in someway.

I've tested the code before answering, I believe it works on the code you was working on or for learning purposes.

Upvotes: 1

Adam Owczarczyk
Adam Owczarczyk

Reputation: 2862

You can't, only the last query row count is returned from executemany, at least that's how it says in the pyodbc code docs. -1 usually indicates problems with query though. If you absolutely need the rowcount, you need to either cursor.execute in a loop or write a patch for pyodbc library.

Upvotes: 1

Related Questions