Get row count in MySQLdb

Question

Is there a better way to get the row count than doing the following?

cursor.execute("SELECT DISTINCT(provider_id) FROM main_iteminstance")
num_items = len(cursor.fetchall())

Is there a shorthand in MySQLdb for the above?

Answer 1

You could execute the following SQL directly on cursor.execute rather than depending on MySQLdb:

cursor.execute("SELECT COUNT(DISTINCT provider_id) FROM main_iteminstance")

Then you just get the result from that query.

Answer 2

The result of cursor.execute returns the number of rows returned by the query so just assign to a variable.

num_items = cursor.execute("SELECT DISTINCT(provider_id) FROM main_iteminstance")

This will also work and you won't have to run an extra query just to get the number of items