how to use rowcount in mysql using python

Question

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.

Pankhuri Agarwal · Accepted Answer · 2017-07-07 05:41:33Z

17

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)

edited Jul 7, 2017 at 5:41

answered Jul 7, 2017 at 5:27

Pankhuri Agarwal

7944 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nishant Over a year ago

thank you for the response but this gives a error message: mysql.connector.errors.InternalError: Unread result found

Pankhuri Agarwal Over a year ago

Dint noticed that you were using mysql. Please check the edit at line number 4 of code in answer. Should work now.

cwl · Accepted Answer · 2017-07-07 05:29:06Z

4

have your code include

m.commit()
cur.close()
m.close()

If your code don't have these, maybe you can add them. and your first 4 lines ,I think you should add

cur.execute('select * from 'table_name'')
cur.fetchall()

then you can get the rowcount.I hope my answer can help you.

answered Jul 7, 2017 at 5:29

cwl

2041 gold badge2 silver badges10 bronze badges

Comments

oss · Accepted Answer · 2021-08-07 11:23:15Z

1

cursor.rowcount will output -1 until you have fetched all rows of the result. Unless you are using a buffered cursor, you should use cursor.fetchall() before getting the real number of rows.

edited Aug 7, 2021 at 11:23

oss

1159 bronze badges

answered Oct 29, 2019 at 21:39

Caresth

214 bronze badges

1 Comment

Bob Stein Over a year ago

Great clarification that cursor.fetchall() is required after a SELECT statement. But cursor.rowcount is not a function, it's just a property dev.mysql.com/doc/connector-python/en/…

Collectives™ on Stack Overflow

how to use rowcount in mysql using python

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related