使用cursor.rowcount和SSDictCursor返回错误计数的Python MysqlDB [英] Python MysqlDB using cursor.rowcount with SSDictCursor returning wrong count

问题描述

我有以下代码

cur = db.cursor(cursors.SSDictCursor)
cur.execute("SELECT * FROM large_table")
result_count = cur.rowcount
print result_count

这将打印数字18446744073709551615，这显然是错误的.如果删除cursors.SSDictCursor，则会显示正确的数字.谁能告诉我在保持SSDictCursor的情况下如何获取返回的记录数?

解决方案

要获取SSDictCursor或SSCursor返回的记录数，您唯一的选择是:

1. 获取整个结果并使用len()对其进行计数，这违背了首先使用SSDictCursor或SSCursor的目的；

2. 在遍历行时自己对行进行计数，这意味着直到行末才知道计数(不太可能)；或

3. 运行另一个单独的COUNT(*)查询.

我强烈建议您选择第三个选项.如果您要做的只是SELECT COUNT(*) FROM table;，这将非常快.对于某些更复杂的查询，它会比较慢，但是通过适当的索引编制，对于大多数用途而言，它应该仍然足够快.

---

顺便说一句，您看到的返回值是种正确的；至少就MySQL C API而言.

根据PEP 249中定义的Python DB API，行数属性为-如果接口无法确定最后一个操作的行数，则为1. @glglgl解释了为什么无法在他们的答案中确定行数:

在内部，SSDictCursor使用mysql_use_result()，它允许服务器在采集完成之前开始传输数据.

换句话说，服务器不知道它最终将要获取多少行.执行查询时，MySQLdb存储rowcount属性中的> mysql_affected_rows() .由于计数不确定，因此此函数以无符号长整型整数(-1. html"rel =" noreferrer> my_ulonglong )，这是标准库的ctypes模块中可用的数字类型:

>>> from ctypes import c_ulonglong
>>> n = c_ulonglong(-1)
>>> n.value
18446744073709551615L

ctypes的一种替代方法是:

>>> -1 & 0xFFFFFFFFFFFFFFFF
18446744073709551615L

如果MySQLdb检查此返回值并给了您期望看到的带符号整数，那就太好了，但是不幸的是，没有.

I have the following code

cur = db.cursor(cursors.SSDictCursor)
cur.execute("SELECT * FROM large_table")
result_count = cur.rowcount
print result_count

This prints the number 18446744073709551615 which is obviously wrong. If I remove the cursors.SSDictCursor the correct number is shown. Can anyone tell me how I can get the number of records returned while keeping the SSDictCursor?

解决方案

To get the number of records returned by SSDictCursor or SSCursor, your only options are:

1. Fetch the entire result and count it using len(), which defeats the purpose of using SSDictCursor or SSCursor in the first place;

2. Count the rows yourself as you iterate through them, which means you won't know the count until hit the end (not likely to be practical); or,

3. Run an additional, separate COUNT(*) query.

I highly recommend the third option. It's extremely fast if all you're doing is SELECT COUNT(*) FROM table;. It would be slower for some more complex query, but with proper indexing it should still be quick enough for most purposes.

---

As an aside, the return value you're seeing is sort of correct; at least, as far as the MySQL C API is concerned.

Per the Python DB API defined in PEP 249, the rowcount attribute is -1 if the rowcount of the last operation cannot be determined by the interface. @glglgl explained why the rowcount can't be determined in their answer:

Internally, SSDictCursor uses mysql_use_result() which allows the server to start transferring the data before the acquiring is complete.

In other words, the server doesn't know how many rows it's ultimately going to fetch. When you execute a query, MySQLdb stores the return value of mysql_affected_rows() in the cursor's rowcount attribute. Because the count is indeterminate, this function returns -1 as an unsigned long long integer (my_ulonglong), a numeric type that's available in the ctypes module of the standard library:

>>> from ctypes import c_ulonglong
>>> n = c_ulonglong(-1)
>>> n.value
18446744073709551615L

A quick-and-dirty alternative to ctypes, when you know you'll always be dealing with a 64-bit unsigned integer, is:

>>> -1 & 0xFFFFFFFFFFFFFFFF
18446744073709551615L

It would be great if MySQLdb checked for this return value and gave you the signed integer you expect to see, but unfortunately it doesn't.

这篇关于使用cursor.rowcount和SSDictCursor返回错误计数的Python MysqlDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！