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

python, sqlite

Solution

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 row is None:
    raise ValueError('No such user found')

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

Problem

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.

Original source