How To Get The Numbers Of Data Rows From Sqlite Table In Python

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 devi

Solution 1:

Normally, cursor.rowcount would give you the number of results of a query.

However, for SQLite, that property is often set to -1 due to the nature of how SQLite produces results. Short of a COUNT() query first you often won't know the number of results returned.

This is because SQLite produces rows as it finds them in the database, and won't itself know how many rows are produced until the end of the database is reached.

From the documentation of cursor.rowcount:

Although the Cursor class of the sqlite3 module implements this attribute, the database engine’s own support for the determination of “rows affected”/”rows selected” is quirky.

For executemany() statements, the number of modifications are summed up into rowcount.

As required by the Python DB API Spec, the rowcount attribute “is -1 in case no executeXX() has been performed on the cursor or the rowcount of the last operation is not determinable by the interface”. This includes SELECT statements because we cannot determine the number of rows a query produced until all rows were fetched.

Emphasis mine.

For your specific query, you can add a sub-select to add a column:

data = sql.sqlExec("select (select count() from user) as count, * from user")

This is not all that efficient for large tables, however.

If all you need is one row, use cursor.fetchone() instead:

cursor.execute('SELECT * FROM user WHERE userid=?', (userid,))
row= cursor.fetchone()
if rowisNone:
    raise ValueError('No such user found')

result= "Name = {}, Password = {}".format(row["username"], row["password"])

Solution 2:

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

len(results) is just what you want

Solution 3:

Use following:

dataCopy = sql.sqlExec("select count(*) from user")
values = dataCopy.fetchone()
printvalues[0]

Solution 4:

I've found the select statement with count() to be slow on a very large DB. Moreover, using fetch all() can be very memory-intensive.

Unless you explicitly design your database so that it does not have a rowid, you can always try a quick solution

cur.execute("SELECT max(rowid) from Table")
n = cur.fetchone()[0]

This will tell you how many rows your database has.

Solution 5:

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