如何从python中的sqlite表中获取数据行数 [英] How to get the numbers of data rows from sqlite table in python

问题描述

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

I am trying to get the numbers of rows returned from an sqlite3 database in python but it seems the feature isn't available:

想想mysql

虽然我设计了一个方法但是很尴尬:假设一个类执行sql并给我结果:

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.

推荐答案

通常, cursor.rowcount 会给你一个查询结果的数量.

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

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

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.

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

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.

来自 cursor.rowcount 的文档:

虽然sqlite3 模块的Cursor 类实现了这个属性，但数据库引擎自身对受影响的行"/选定的行"的确定的支持是古怪的.

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.

对于executemany()语句，修改的次数汇总为rowcount.

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

根据 Python DB API 规范的要求，rowcount 属性为 -1，以防没有对游标或游标的行数执行 executeXX()接口无法确定最后一个操作".这包括 SELECT 语句，因为在获取所有行之前，我们无法确定查询生成的行数.

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.

强调我的.

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

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.

如果您只需要一行，请改用cursor.fetchone():

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

这篇关于如何从python中的sqlite表中获取数据行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！