How to get the numbers of data rows from sqlite table in python

Question

I am trying to get the numbers of rows returned from an sqlite3 database in python but it seems the feature isn't available:

Think of php mysqli_num_rows() in mysql

Although I devised a means but it is a awkward: assuming a class execute sql and give me the results:

# Query Execution returning a result
data = sql.sqlExec("select * from user")
# run another query for number of row checking, not very good workaround
dataCopy = sql.sqlExec("select * from user")
# Try to cast dataCopy to list and get the length, I did this because i notice as soon 
# as I perform any action of the data, data becomes null
# This is not too good as someone else can perform another transaction on the database 
# In the nick of time
    if len(list(dataCopy)) :
        for m in data :
            print("Name = {}, Password = {}".format(m["username"], m["password"]));
    else :
        print("Query return nothing")

Is there a function or property that can do this without stress.

Answer 1

import sqlite3
conn = sqlite3.connect(path/to/db)
cursor = conn.cursor()
cursor.execute("select * from user")
results = cursor.fetchall()
print len(results)

len(results) is just what you want

Answer 2

When you just want an estimate beforehand, then simple use COUNT():

n_estimate = cursor.execute("SELECT COUNT() FROM user").fetchone()[0]

To get the exact number before fetching, use a locked "Read transaction", during which the table won't be changed from outside, like this:

cursor.execute("BEGIN")  # start transaction
n = cursor.execute("SELECT COUNT() FROM user").fetchone()[0]
# if n > big: be_prepared()
allrows=cursor.execute("SELECT * FROM user").fetchall()
cursor.connection.commit()  # end transaction
assert n == len(allrows)

Note: A normal SELECT also locks - but just until it itself is completely fetched or the cursor closes or commit() / END or other actions implicitely end the transaction ...