pyodbc rowcount only returns -1

Question

How does rowcount work. I am using pyodbc and it's always returning -1.

 return_query = conn.query_db_param(query, q_params)
 print(return_query.rowcount)

 def query_db_param(self, query, params):
     self.cursor.execute(query,params)
     print(self.cursor.rowcount)

Possible duplicate of [cursor.rowcount always -1 in sqlite3 in python3k](https://stackoverflow.com/questions/839069/cursor-rowcount-always-1-in-sqlite3-in-python3k) — cwallenpoole, Nov 28 '17 at 20:45

score 16 · Accepted Answer · answered Nov 28 '17 at 20:47

16

rowcount refers to the number of rows affected by the last operation. So, if you do an insert and insert only one row, then it will return 1. If you update 200 rows, then it will return 200. On the other hand, if you SELECT, the last operation doesn't really affect rows, it is a result set. In that case, 0 would be syntactically incorrect, so the interface returns -1 instead.

It will also return -1 for operations where you do things like set variables or use create/alter commands.

answered Nov 28 '17 at 20:47

cwallenpoole

79,954
26
128
166

Thanks I thought rowcount was the number of rows returned from a query. In Coldfusion they have queryname.RecordCount Php has mysql_num_rows does python have anything like that? @cwallenpoole – user3525290 Nov 29 '17 at 11:37
3

`len(cursor.fetchall())` is you closest. That's what MySQL does behind the scenes anyway. – cwallenpoole Nov 29 '17 at 14:16
2

*On the other hand, if you SELECT, the last operation doesn't really affect rows, it is a result set. In that case, 0 would be syntactically incorrect, so the interface returns -1 instead.* This is not correct, sorry. Many database implementations will set the rowcount to the *number of rows selected*. It is only set to -1 if that number can't be determined up-front; e.g. the SQLite engine won't be able to tell you how many rows are selected until it has returned all rows. The pyodbc documenation [explicitly covers this case](https://github.com/mkleehammer/pyodbc/wiki/Cursor). – Martijn Pieters Feb 11 '19 at 13:52
1

@user3525290: rowcount *is* the number of rows returned for a SELECT query. But not all database engines can know this number up front (they find rows as they scan the affected tables and indices, so the final count is only known when all rows have been produced). The Python DB-API 2.0 specification [allows the rowcount to be set to -1 in that case](https://www.python.org/dev/peps/pep-0249/#rowcount). – Martijn Pieters Feb 11 '19 at 13:53
Another nuance: for `UPDATE`, some databases will only report _affected_ row counts, not the number matched. This can occur when the condition for an update matches _n_ rows, but of those rows, only _n - x_ actually would be changed (the current row content differs from the update), and so _n - x_ is the count which will be returned by `rowcount`. – bsplosion May 26 '21 at 21:18
Beware, that it *will* genuinely return 0 if there is an empty cursor, so if you are doing a probe on a primary key with "Fetch 1 ..." then it is valid to test for zero. That's if you never intended to fetch, only verify. -1 means indeterminate, but *exists*. – mckenzm Feb 05 '23 at 22:45

Martijn Pieters · Answer 2 · 2019-02-11T14:21:25.527

You are connecting to a database that can't give you that number for your query. Many database engines produce rows as you fetch results, scanning their internal table and index data structures for the next matching result as you do so. The engine can't know the final count until you fetched all rows.

When the rowcount is not known, the Python DB-API 2.0 specification for Cursor.rowcount states the number must be set to -1 in that case:

The attribute is -1 in case [...] the rowcount of the last operation is cannot be determined by the interface.

The pyodbc Cursor.rowcount documentation conforms to this requirement:

The number of rows modified by the last SQL statement.

This is -1 if no SQL has been executed or if the number of rows is unknown. Note that it is not uncommon for databases to report -1 immediately after a SQL select statement for performance reasons. (The exact number may not be known before the first records are returned to the application.)

pyodbc is not alone in this, another easy-to-link-to example is the Python standard library sqlite3 module; it's Cursor.rowcount documentation states:

As required by the Python DB API Spec, the rowcount attribute “is -1 in case no executeXX() has been performed on the cursor or the rowcount of the last operation is not determinable by the interface”. This includes SELECT statements because we cannot determine the number of rows a query produced until all rows were fetched.

Note that for subset of database implementations, the rowcount value can be updated after fetching some of the rows. You'll have to check your specific database documentation you are connecting to to see if that implementations can do this, or if the rowcount must remain at -1. You could always experiment, of course.

You could execute a COUNT() select first, or, if the result set is not expected to be too large, use cursor.fetchall() and use len() on the resulting list.

score 4 · Answer 3 · answered Feb 14 '20 at 07:36

4

If you are using microsoft sql server, and you want to get the number of rows returned in the prior select statement, you can just execute select @@rowcount.

E.g.:

cursor.execute("select @@rowcount")
rowcount = cursor.fetchall()[0][0]

answered Feb 14 '20 at 07:36

dstandish

2,328
18
34

pyodbc rowcount only returns -1

3 Answers3

Linked