Geek asks and answers

How to get data line numbers from sqlite table in python

I am trying to get the number of rows returned from sqlite3 database in python, but the function seems to be unavailable:

Think about php mysqli_num_rows()inmysql

Although I developed the tool, it’s inconvenient: assuming the class completed sqland gave 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 feature or property that can do this without stress.

+4
source share
6 answers

Usually cursor.rowcountgives you the number of query results.

However, for SQLite, this property is often set to -1 because of how SQLite produces the results. With the exception of the query COUNT(), you often will not know the number of results returned.

, SQLite , .

cursor.rowcount:

, Cursor sqlite3 , " " ​​/ " " ​​ .

executemany() rowcount.

API- Python rowcount -1, executeXX() , ". SELECT, , , , .

.

, :

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

.

, cursor.fetchone() :

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"])
+5

:

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

, select count() . , fetch all() .

If you obviously do not 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 are in your database.

+4
source 
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 what you want

+1
source

A simple alternative approach here is to use fetchall to translate the column into a python list and then count the length of the list. I don't know if this is pythonic or especially effective, 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)
+1
source

this code worked for me:

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

More articles:


All Articles