Getting the rowcount of a Sqlite resultset with SqlAlchemy

How database inconsistencies lead to a hack which makes my chest swell with pride.

I came accross an interesting problem this morning when I was trying to get the rowcount of a Sqlite resultset. When using the MySQL engine, you can use the rowcount property to count the number of rows returned from a query, but strangely the same operation gives -1 when using a sqlite database.

At first, I thought the problem was due to SqlAlchemy which I was using, and quickly made a script to isolate and compare:

Which results in:

  Testing mysql & sqlite rowcount with SqlAlchemy v0.5.8
  Creating schemas
  Inserting some data
  all_mysql_rows.rowcount: 1
  len(all_mysql_rows.fetchall()): 1
  all_sqlite_rows.rowcount: -1
  len(all_sqlite_rows.fetchall()): 1

After some digging around, I discovered that the problem is infact due to pysqlite, and I quote:

Well this was a bummer, because I wanted to write code that I could switch between Sqlite and MySQL - using MySQL in production/development, and sqlite in-memory databases for unit-testing etc. Then it occurred to me: code for unit testing doesn't have to be extremely efficient, so perhaps I could monkey-patch SqlAlchemy's ResultProxy to make up for sqlite's deficiancy...

The hack