Search code examples
databasesqlitecountqsqlquery

Using COUNT to find number of rows in table in sqlite3

ive tried various methods but i keep getting an object returned.

import sqlite3

connection = sqlite3.connect('films.db')
cur = connection.cursor()

def createTable():

    connection.execute('CREATE TABLE IF NOT EXISTS FILMS(TITLE TEXT NOT NULL , YEAR INT NOT NULL,RATING INT NOT NULL,unique(TITLE))')
    connection.execute('INSERT OR IGNORE INTO FILMS VALUES(? , ? , ?)',('Middle Men','2010','6.6'))
    connection.execute("SELECT * FROM FILMS")

    row_count = cur.execute("SELECT Count(*) FROM FILMS")
    print(row_count)

    cur.close()
    connection.commit()
createTable()

I keep getting this cursor object returned when i print the count: sqlite3.Cursor object at 0x027CE820 is there a reason for this? is my syntax wrong?

Solution

  • You are fetching the whole object. Instead of:

        row_count = cur.execute("SELECT Count(*) FROM FILMS")

    Do:

    cur.execute("SELECT Count(*) FROM FILMS")
row_count = cur.fetchone()
print(row_count)

    This will get the row that has the count in it. If you were doing a select that had multiple rows, you could do 

    cur.fetchall()