python sqlite3 如何得到select 语句返回的记录集的行数？

最新推荐文章于 2023-12-22 07:52:53 发布

bigcarp

最新推荐文章于 2023-12-22 07:52:53 发布

阅读量5.5k

点赞数 1

分类专栏：编程文章标签： python rowcount

本文链接：https://blog.csdn.net/bigcarp/article/details/114193423

版权

编程专栏收录该内容

92 篇文章 6 订阅

订阅专栏

官方文档有cursor.rowcount ，但通常返回值是-1。

查询相关资料，得到答案如下：

没有可以直接得到行数的函数。sqlite事先不知道行数，遍历时才逐行返回。--->遍历完才知道的话，是否意味着并没有一次过将全部数据返回写入内存？

可以用 : result=cursor.fetchall() 然后 count=len(result) 获取。

或者执行一次 select count(*) from x where y 然后 fetchone 获取

参考：https://stackoverflow.com/questions/3890517/how-do-i-use-num-rows-function-in-the-mysqldb-api-for-python

From the SQLite3 FAQ here

Q) Which func could get the number of rows?

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.

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;

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

-------------

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. [官方文档]

在Python实现的DB API中，rowcount属性“当在特定类型数据库接口未实现cursor的rowcount属性时会返回-1”，sqlite就属于这种情况。

------------

rowcount返回的仅仅是受影响的行，select不会影响这些行。所有的SELECT语句都不会有rowcount

-------------

另有不少文章说 cursor执行过fetchall() 一次之后可以用 cursor.rowcount 获得，但我测试也是-1，那么是不是sqlite不行，但其他数据库可以呢？