16

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.

Improve this question

9 Answers 9

Reset to default
16

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"])
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

data.rowcount returned -1
data.COUNT() returned AttributeError: 'sqlite3.Cursor' object has no attribute 'COUNT'
@Temitayo: COUNT() is a SQL function, not a function on the cursor.
"select (select count() from user) as count, * from user" using this query nearly solve the problem, although there are still some thing i need to figure out, try to pull out the first row and pull out the count without iterating on the data data[0]["count"] returns TypeError: 'sqlite3.Cursor' object is not subscriptable
@Temitayo: No, you'd use cursor.fetchall() to get all rows in one go, or use cursor.fetchone() to just get the one result you wanted, the first row.
|
13 
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

Improve this answer

2 Comments

you need to add tags to you questions, to get viewer
For a large table, this is extremely inefficient.
8

Use following:

dataCopy = sql.sqlExec("select count(*) from user")
values = dataCopy.fetchone()
print values[0]
Improve this answer

1 Comment

Good but i want to determine the numbers of row, before doing anything, and what what if i dont what to fetchall?
7

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

Improve this answer

Comments

5

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.

Improve this answer

2 Comments

I would never use this method to determine the row count of a table. I have a table with 7,706,455 rows (as reported by SELECT COUNT(*)... but the max rowid is 96,171,095,196!
Following up on my previous comment, I actually gave a bad example, but I still say you shouldn't use this method to get the row count. Have a look at SQLite and unique rowid … Something you really need to know. If you delete rows from a table without using VACUUM you will get an incorrect total.
2

I did it like

cursor.execute("select count(*) from my_table")
results = cursor.fetchone()
print(results[0])
Improve this answer

Comments

2

this code worked for me:

import sqlite3
con = sqlite3.connect(your_db_file)
cursor = con.cursor()
result = cursor.execute("select count(*) from your_table").fetchall() #returns array of tupples
num_of_rows = result[0][0]
Improve this answer

2 Comments

it would produce error because of result[0][0]: TypeError: 'sqlite3.Cursor' object is not subscriptable. You miss one line of code: result = cursor.fetchall()
Kardi Teknomo, You are right Fixing my answer
1

A simple alternative approach here is to use fetchall to pull a column into a python list, then count the length of the list. I don't know if this is pythonic or especially efficient but it seems to work:

rowlist = []
c.execute("SELECT {rowid} from {whichTable}".\
          format (rowid = "rowid", whichTable = whichTable))
rowlist = c.fetchall ()
rowlistcount = len(rowlist)
print (rowlistcount)
Improve this answer

1 Comment

For a large table, this is extremely inefficient
0

The following script works:

def say():
    global s #make s global decleration    
    vt = sqlite3.connect('kur_kel.db') #connecting db.file
    bilgi = vt.cursor() 
    bilgi.execute(' select count (*) from kuke ') #execute sql command
    say_01=bilgi.fetchone() #catch one query from executed sql
    print (say_01[0]) #catch a tuple first item
    s=say_01[0] # assign variable to sql query result
    bilgi.close() #close query
    vt.close() #close db file
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.