the_big_blackbox the_big_blackbox - 11 months ago 126
SQL Question

Python rowcount returning negative 1

Please can someone help i cant believe I am not getting this right. I am trying to count the number of rows that I have in a mysql db. The correct number is 7, however everytime I execute the following I get an answer of -1. The connection is established successfully.I am using python 3.4.4

import mysql.connector

config = {
'user': 'root',
'password': '',
'host': '',
'database': 'test'
cnx =mysql.connector.MySQLConnection(**config)

if cnx.is_connected():
print('Connected to MySQL database')

cursor = cnx.cursor()
cursor.execute("SELECT * FROM test")
numrows = int (cursor.rowcount)


According to the documentation:

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.

Which means that, in order for .rowcount to work in this case, you have to fetch the results first.

cursor.execute("SELECT * FROM test")
rows = cursor.fetchall()
numrows = int(cursor.rowcount)

Of course, you can also get the length of the rows list.