如何从python中的sqlite表中获取数据行数

我试图获取从python中的sqlite3数据库返回的行数,但似乎该功能不可用：

想想mysql中的php mysqli_num_rows()

虽然我设计了一种方法,但它很尴尬：假设一个类执行sql并给我结果：

# 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")

是否有能够在没有压力的情况下做到这一点的功能或属性.

解决方法:

通常,cursor.rowcount会为您提供查询结果的数量.

但是,对于SQLite,由于SQLite生成结果的性质,该属性通常设置为-1.如果没有COUNT()查询,您通常不会知道返回的结果数.

这是因为SQLite在数据库中找到行时会生成行,并且在达到数据库末尾之前本身不会知道生成了多少行.

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

强调我的.

对于您的特定查询,您可以添加子选择以添加列：

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

【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！