In Python's Sqlite3 Module, Why Can't Cursor.rowcount() Tell Me The Number Of Rows Returned By A Select Statement

So I've read the documentation and it says: Cursor.rowcount¶ Although the Cursor class of the sqlite3 module implements this attribute, the database engine’s own support

Solution 1:

SQLite does not "fetch" all the rows when initially processing the query. It returns a single row each time the prepared query is stepped.

From the SQLite3 FAQ here

Q) Which func could get the number of rows?

A) There is no function to retrieve the number of rows in a result set. SQLite doesn't know the number in advance, but returns row by row while iterating through the tables. The application can increment a row counter as needed at every successful sqlite3_step() .

Some wrappers are able to collect all rows in a resultset in a in-memory table, so they can return the number of rows.

You can always get the number of rows that a certain SELECT statement would return at the cost of some performance:

BEGIN IMMEDIATE TRANSACTION;
SELECTCOUNT(*) FROM x WHERE y;
SELECT a,b,c FROM x WHERE y;
ROLLBACK TRANSACTION;

You have to wrap this in a transaction to prevent other connections from inserting / deleting rows between the two SELECT statements.