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

Question

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 1

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

or you can call cursor.fetchall().

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.

In your example query, you are fetching a single row, with a single column, so you can get that single value with cursor.fetchone() then using indexing or tuple assignment, e.g.

cursor.execute("select count(*) from fixtures")
fixture_count = cursor.fetchone()[0]

or

cursor.execute("select count(*) from fixtures")
fixture_count, = cursor.fetchone()

You don't need to commit after a SELECT, but you didn't actually call the commit() method either, you are missing the () part. If you are altering data, do remember to use cursor.commit(). Note that cursor.commit() does exactly the same thing as cnxn.commit(); transactions are managed per connection, not per cursor.

However, when not using autocommit, it is easier and better to use the connection as a context manager to ensure a transaction is aborted or committed based on there being any exceptions:

with cnxn:
    # anything in this block is handled with a transaction.

# after the block the transaction is committed, unless there was an exception.