CODEWITHSUNDEEP
pythonmysql-pythondelete-row
Chiyaan Suraj
Chiyaan Suraj

Reputation: 1015

Python: How to get number of rows deleted with MySQLdb

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

Upvotes: 4

Views: 12951

Answers (2)

falsetru
falsetru

Reputation: 369054

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]

Upvotes: 13

Stephen Satchell
Stephen Satchell

Reputation: 31

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.

Upvotes: 3

Related Questions