how to use rowcount in mysql using python

how to use rowcount in mysql using python

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.


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.

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)


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) 

So, the code to count the number of rows in a MySQL table in Python is shown below. import pymysql pymysql.install_as_MySQLdb () import MySQLdb db= MySQLdb.connect ("hostname", "username", "password", "database_name") cursor= db.cursor () number_of_rows= cursor.execute ("SELECT * FROM Table_name) # enter the table name


A bit late, but...

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

For nonbuffered cursors, the row count cannot be known before the rows have been fetched. In this case, the number of rows is -1 immediately after query execution and is incremented as rows are fetched. The rowcount property is like the mysql_affected_rows () C API function; see mysql_affected_rows (). PREV HOME UP NEXT


For a result set with just one row, you could use: cursor.execute("select count(*) from fixtures") result = cursor.fetchone() cursor.fetchone() returns either one row, or None if there are no results at all. In all cases rows are sequences of columns, for a one-column result that'll be a tuple with just one value in it.


The MySQLCursor of mysql-connector-python (and similar libraries) is used to execute statements to communicate with the MySQL database. Using the methods of it you can execute SQL statements, fetch data from the result sets, call procedures. You can create Cursor object using the cursor() method of the Connection object/class. Example


print("The query affected {} rows".format(cur.rowcount)) We build a message that shows the number of affected rows. $ ./affected_rows.py The query affected 3 rows This is the output. In this tutorial, we have have been programming MySQL database in Python with PyMySQL module.