如何从python中的sqlite表中获取数据行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21829266/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the numbers of data rows from sqlite table in python
提问by Temitayo
I am trying to get the numbers of rows returned from an sqlite3 database in python but it seems the feature isn't available:
我正在尝试获取从 python 中的 sqlite3 数据库返回的行数，但该功能似乎不可用：
Think of phpmysqli_num_rows()in mysql
想到phpmysqli_num_rows()在mysql
Although I devised a means but it is a awkward: assuming a class execute sqland give me the results:
虽然我设计了一个方法但是很尴尬：假设一个类执行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")
Is there a function or property that can do this without stress.
是否有可以在没有压力的情况下执行此操作的功能或属性。
回答by Martijn Pieters
Normally, cursor.rowcountwould give you the number of results of a query.
通常，cursor.rowcount会为您提供查询结果的数量。
However, for SQLite, that property is often set to -1 due to the nature of how SQLite produces results. Short of a COUNT()query firstyou often won't know the number of results returned.
但是，对于 SQLite，由于 SQLite 生成结果的方式，该属性通常设置为 -1。短的COUNT()查询第一，你经常会不知道返回结果的数量。
This is because SQLite produces rows as it finds them in the database, and won't itselfknow how many rows are produced until the end of the database is reached.
这是因为 SQLite 在数据库中找到行时会生成行，并且在到达数据库末尾之前它自己不知道生成了多少行。
From the documentation of cursor.rowcount:
从文档cursor.rowcount：
Although the
Cursorclass of thesqlite3module 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 intorowcount.As required by the Python DB API Spec, the
rowcountattribute “is -1 in case noexecuteXX()has been performed on the cursor or the rowcount of the last operation is not determinable by the interface”. This includesSELECTstatements because we cannot determine the number of rows a query produced until all rows were fetched.
尽管模块的
Cursor类sqlite3实现了这个属性，但数据库引擎自身对“受影响的行”/“选择的行”的确定的支持是古怪的。对于
executemany()语句，修改次数合计为rowcount。根据 Python DB API 规范的要求，该
rowcount属性“为 -1，以防未executeXX()对游标执行任何操作或接口无法确定最后一次操作的行数”。这包括SELECT语句，因为在获取所有行之前，我们无法确定查询产生的行数。
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 onerow, use cursor.fetchone()instead:
如果你需要的是一个排，使用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"])
回答by user3273866
Use following:
使用以下：
dataCopy = sql.sqlExec("select count(*) from user")
values = dataCopy.fetchone()
print values[0]
回答by WeizhongTu
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
len(results) 正是你想要的
回答by user3510563
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:
这里一个简单的替代方法是使用 fetchall 将一列拉入 python 列表，然后计算列表的长度。我不知道这是 pythonic 还是特别有效，但它似乎有效：
rowlist = []
c.execute("SELECT {rowid} from {whichTable}".\
format (rowid = "rowid", whichTable = whichTable))
rowlist = c.fetchall ()
rowlistcount = len(rowlist)
print (rowlistcount)
回答by Nir
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]
回答by alexs
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.
我发现带有 count() 的 select 语句在非常大的数据库上很慢。此外，使用fetch all()可能非常占用内存。
Unless you explicitly design your database so that it does not have a rowid, you can always try a quick solution
除非你明确设计你的数据库使其没有rowid，否则你总是可以尝试快速解决方案
cur.execute("SELECT max(rowid) from Table")
n = cur.fetchone()[0]
This will tell you how many rows your database has.
这将告诉您数据库有多少行。
回答by kxr
When you just want an estimate beforehand, then simple use COUNT():
如果您只是想事先进行估算，那么简单地使用 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 SELECTalso locks - but just until it itself is completely fetched or the cursor closes or commit()/ ENDor other actions implicitely end the transaction ...
注意：正常SELECT也会锁定 - 但直到它本身被完全获取或游标关闭或commit()/END或其他操作隐式结束事务......
