BigGibb BigGibb - 8 months ago 50
Python Question

How to count number of records in an SQL database with python

I can't seem to print the number of records in my database:

When I program:

cursor = cnxn.cursor()
count = cursor.execute("select count(*) from fixtures")
print (count)

(fixtures is the name of my database)

I get:

pyodbc.Cursor object at 0x00000000032FC150

...rather than the number of records.

I am using pyodbc module on python


For pyodbc, cursor.execute() returns the cursor object itself. You still need to retrieve the results separately.

You could loop over the cursor to get rows; list() can do the looping for you and pull in all rows into a list object:

cursor.execute("select count(*) from fixtures")  

For a result set with just one row, you could use:

cursor.execute("select count(*) from fixtures")
result = cursor.fetch_one()

cursor.fetch_one() 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.

You don't need to commit after a SELECT, but you didn't call the commit() method either. If you are altering data, do remember to use cursor.commit().