在Python的sqlite3模块中，为什么cursor.rowcount()不能告诉我select语句返回的行数 [英] In Python's sqlite3 module, why can't cursor.rowcount() tell me the number of rows returned by a select statement

问题描述

所以我已经阅读了文档，它说:

So I've read the documentation and it says:

Cursor.rowcount¶

Cursor.rowcount¶

虽然 Cursor 类的sqlite3 模块实现了这个属性，数据库引擎自己的支持确定行受影响的"/选择的行"很奇怪.

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.

这包括 SELECT 语句因为我们无法确定数量查询产生的行数，直到所有已获取行.

This includes SELECT statements because we cannot determine the number of rows a query produced until all rows were fetched.

为什么，在执行 SELECT 之后，我无法立即使用它来确定返回的行数.查询执行完毕后，肯定已经获取了所有行吗?

How come, immediately after executing a SELECT, I can't use this to determine the number of rows returned. Surely the query, having been executed, has now fetched all the rows?

推荐答案

SQLite 在最初处理查询时不会获取"所有行.每次执行准备好的查询时，它都会返回一行.

SQLite does not "fetch" all the rows when initially processing the query. It returns a single row each time the prepared query is stepped.

来自 SQLite3 常见问题解答这里

From the SQLite3 FAQ here

Q) 哪个 func 可以获得行数?

Q) Which func could get the number of rows?

A) 没有检索结果集中行数的函数.SQLite 事先不知道数字，而是在遍历表时逐行返回.应用程序可以在每次成功的 sqlite3_step() 时根据需要增加行计数器.

A) There is no function to retrieve the number of rows in a result set. SQLite doesn't know the number in advance, but returns row by row while iterating through the tables. The application can increment a row counter as needed at every successful sqlite3_step() .

一些包装器能够将结果集中的所有行收集到内存表中，因此它们可以返回行数.

Some wrappers are able to collect all rows in a resultset in a in-memory table, so they can return the number of rows.

您总是可以以牺牲某些性能为代价获得某个 SELECT 语句将返回的行数:

You can always get the number of rows that a certain SELECT statement would return at the cost of some performance:

BEGIN IMMEDIATE TRANSACTION;
SELECT COUNT(*) FROM x WHERE y;
SELECT a,b,c FROM x WHERE y;
ROLLBACK TRANSACTION;

您必须将其包装在一个事务中，以防止其他连接在两个 SELECT 语句之间插入/删除行.

You have to wrap this in a transaction to prevent other connections from inserting / deleting rows between the two SELECT statements.

这篇关于在Python的sqlite3模块中，为什么cursor.rowcount()不能告诉我select语句返回的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！