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

PythonSqlRowsDatabase

Python Problem Overview


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+"_%'")

Python Solutions


Solution 1 - Python

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').

But be careful—it goes on to say:

> The attribute is -1 in case no .execute*() has been performed on the cursor or the rowcount of the last operation is cannot be determined by the interface. [7] > > Note:
> Future versions of the DB API specification could redefine the latter case to have the object return None instead of -1.

So if you've executed your statement, and it works, and you're certain your code will always be run against the same version of the same DBMS, this is a reasonable solution.

Solution 2 - Python

Try using fetchone:

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

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:

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

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

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

Solution 3 - Python

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

    """

Solution 4 - Python

In my opinion, the simplest way to get the amount of selected rows is the following:

The cursor object returns a list with the results when using the fetch commands (fetchall(), fetchone(), fetchmany()). To get the selected rows just print the length of this list. But it just makes sense for fetchall(). ;-)

print len(cursor.fetchall)

# python3
print(len(cur.fetchall()))

Solution 5 - Python

To get the number of selected rows I usually use the following:

cursor.execute(sql)
count = (len(cursor.fetchall))

Solution 6 - Python

when using count(*) the result is {'count(*)': 9}

-- where 9 represents the number of rows in the table, for the instance.

So, in order to fetch the just the number, this worked in my case, using mysql 8.

cursor.fetchone()['count(*)']

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionTie-fighterView Question on Stackoverflow
Solution 1 - PythonAndiDogView Answer on Stackoverflow
Solution 2 - PythonunutbuView Answer on Stackoverflow
Solution 3 - PythonBoazView Answer on Stackoverflow
Solution 4 - Pythonharvey_cView Answer on Stackoverflow
Solution 5 - PythonFedericoSalaView Answer on Stackoverflow
Solution 6 - PythonJennerView Answer on Stackoverflow