BigGibb BigGibb - 1 month ago 21
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")
cursor.commit
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

Answer

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")  
print(list(cursor))

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