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)

Answer 1

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.

Answer 2

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.