how to use rowcount in mysql using python

nishant picture nishant · Jul 7, 2017 · Viewed 26.6k times · Source

We are implementing a database in a student record project. We want to see that how many rows are there in a table before and after deleting a row from a table. The code we tried is as follows:

1  roll=5
2  m = mysql.connector.connect(host='localhost', database='student',user='root', password='')
3  cur = m.cursor()
4  rc = cur.rowcount
5  print("%d"%rc)
6  e=cur.execute("DELETE FROM `acc_details` WHERE roll_No=%s" % roll)
7  print("%d"%cur.rowcount) 

In the above code, the first rowcount in line 4 is giving -1 as the output and the rowcount in the last line is giving the no of rows that the table has after deleting a row.

Why is the first rowcount in line 4 give -1 as the output?

Any help shall be great.

Answer

Pankhuri Agarwal picture Pankhuri Agarwal · Jul 7, 2017

As per the document of psycopg2, cur.rowcount returns the number of rows affected by the last execute method for the same cur object and thus it returns -1 for the first cur.rowcount call as there is no previous execute() method.

You can try this-

roll=5 
m = mysql.connector.connect(host='localhost', database='student',user='root', password='')
cur = m.cursor()
cur.execute("SELECT * FROM `acc_details`")
cur.fetchall()
rc = cur.rowcount
print("%d"%rc)
e=cur.execute("DELETE FROM `acc_details` WHERE roll_No=%s" % roll)
print("%d"%cur.rowcount)