Python: How to get number of rows deleted with MySQLdb

Question

I have this below code which deletes all the rows in a table whose date column value is current_date.

db = MySQLdb.connect("localhost",'root','XXXX','XXXX')
cur = db.cursor()
query = "delete from RealTimeData where date = current_date()"
cur.execute(query)
db.commit()

Now, How can I know that How many rows did my query delete from table? I tried fetchone() it was returning None

Answer 1

You can use Cursor.rowcount attribute:

cur.execute(query)
deleted_row_count = cur.rowcount

According to the documentation,

.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 ). [9]

Answer 2

Using Python 2.7.5, MySQLdb version 1.2.5 in Linux:

The key is that the API returns .rowcount ONLY for SELECT, INSERT, and UPDATE. From https://www.python.org/dev/peps/pep-0249/:

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

Notice there is NO mention of this attribute being updated for DELETE. Closing my old cursor and creating a new one in my application, I saw a result of -1 for .rowcount after a DELETE statement, exactly as documented in PEP249. (Future note: the return may be changed to None instead of -1.)

So, Plan B:

with ... as mycursor:
    mycursor.execute("DELETE FROM mytable WHERE id = %s", params=(myindex,))
    mycursor.execute("SELECT ROW_COUNT() as rowcount")
    rows = mycursor.fetchall()
    rowcount = rows[0].get('rowcount', -1)

Now, the variable rowcount has the number of rows deleted by the execution of the DELETE SQL command.

Why not use the multi=True parameter? From https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html:

If multi is set to True, execute() is able to execute multiple statements specified in the operation string. It returns an iterator that enables processing the result of each statement. However, using parameters does not work well in this case, and it is usually a good idea to execute each statement on its own.

(Emphasis mine.) Using this code technique, I was able to verify that the DELETE statement actually deleted the record specified.