Python: Number of rows affected by cursor.execute("SELECT ...)

Question

How can I access the number of rows affected by:

cursor.execute("SELECT COUNT(*) from result where server_state='2' AND name LIKE '"+digest+"_"+charset+"_%'")

Answer 1

From PEP 249, which is usually implemented by Python database APIs:

Cursor Objects should respond to the following methods and attributes:

[...]

.rowcount
This read-only attribute specifies the number of rows that the last .execute*() produced (for DQL statements like 'select') or affected (for DML statements like 'update' or 'insert').

Hope that is what you meant.

Answer 2

Try using fetchone:

result=cursor.fetchone("SELECT COUNT(*) from result where server_state='2' AND name LIKE '"+digest+"_"+charset+"_%'")

result will hold a tuple with one element, the value of COUNT(*). So to find the number of rows:

number_of_rows=result[0]

Or, if you'd rather do it in one fell swoop:

(number_of_rows,)=cursor.fetchone("SELECT COUNT(*) from result where server_state='2' AND name LIKE '"+digest+"_"+charset+"_%'")

PS. It's also good practice to use parametrized arguments whenever possible, because it can automatically quote arguments for you when needed, and protect against sql injection.

The correct syntax for parametrized arguments depends on your python/database adapter (e.g. mysqldb, psycopg2 or sqlite3). It would look something like

(number_of_rows,)=cursor.fetchone("SELECT COUNT(*) from result where server_state= %s AND name LIKE %s",[2,digest+"_"+charset+"_%"])

Answer 3

The number of rows effected is returned from execute:

rows_affected=cursor.execute("SELECT ... ")

of course, as AndiDog already mentioned, you can get the row count by accessing the rowcount property of the cursor at any time to get the count for the last execute:

cursor.execute("SELECT ... ")
rows_affected=cursor.rowcount

From the inline documentation of python MySQLdb:

 def execute(self, query, args=None):

    """Execute a query.

    query -- string, query to execute on server
    args -- optional sequence or mapping, parameters to use with query.

    Note: If args is a sequence, then %s must be used as the
    parameter placeholder in the query. If a mapping is used,
    %(key)s must be used as the placeholder.

    Returns long integer rows affected, if any

    """