SQL Alchemy ResultProxy.rowcount 不应为零 [英] SQL Alchemy ResultProxy.rowcount should not be zero
问题描述
有谁知道如何在不遍历结果集的情况下从 SQL Alchemy 查询 ResultProxy 对象中获取行数?ResultProxy.rowcount 属性显示为 0，我希望它的值为 2.对于更新，它显示受影响的行数，这正是我所期望的.
Does anyone know how to get the row count from an SQL Alchemy query ResultProxy object without looping through the result set? The ResultProxy.rowcount attribute shows 0, I would expect it to have a value of 2. For updates it shows the number of rows affected which is what I would expect.
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine(
'oracle+cx_oracle://user:pass@host:port/database'
)
session = sessionmaker(
bind = engine
, autocommit = False
, autoflush = False
)()
sql_text = u"""
SELECT 1 AS Val FROM dual UNION ALL
SELECT 2 AS Val FROM dual
"""
results = session.execute(sql_text)
print '%s rows returned by query...\n' % results.rowcount
print results.keys()
for i in results:
print repr(i)
输出:
0 rows returned by query...
[u'val']
(1,)
(2,)
推荐答案
resultproxy.rowcount 最终是 DBAPI 属性 cursor.rowcount 的代理.大多数 DBAPI 不通过此属性为 SELECT 查询提供行数"；它的主要目的是提供与 UPDATE 或 DELETE 语句匹配的行数.事实上，关系数据库在完成所有这些行的定位之前，并不知道特定语句将返回多少行；许多 DBAPI 实现将在数据库找到行时开始返回行，没有缓冲，因此在这些情况下甚至没有这样的计数.
resultproxy.rowcount is ultimately a proxy for the DBAPI attribute cursor.rowcount. Most DBAPIs do not provide the "count of rows" for a SELECT query via this attribute; its primary purpose is to provide the number of rows matched by an UPDATE or DELETE statement. A relational database in fact does not know how many rows would be returned by a particular statement until it has finished locating all of those rows; many DBAPI implementations will begin returning rows as the database finds them, without buffering, so no such count is even available in those cases.
要获取 SELECT 查询将返回的行数，您需要预先执行 SELECT COUNT(*)，或者需要将所有行提取到一个数组中并对数组执行 len().
To get the count of rows a SELECT query would return, you either need to do a SELECT COUNT(*) up front, or you need to fetch all the rows into an array and perform len() on the array.
ResultProxy.rowcount 上的注释对此进行了进一步讨论 (http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=rowcount#sqlalchemy.engine.ResultProxy.rowcount):
The notes at ResultProxy.rowcount discuss this further (http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=rowcount#sqlalchemy.engine.ResultProxy.rowcount):
关于 ResultProxy.rowcount 的说明:
Notes regarding ResultProxy.rowcount:
该属性返回匹配的行数，不一定与实际修改的行数相同 - 例如，如果 SET 语句，UPDATE 语句可能对给定行没有净更改给定的值与行中的值相同已经.这样的行将被匹配但不会被修改.在后端兼具两种风格，如 MySQL，rowcount 由默认在所有情况下都返回匹配计数.
This attribute returns the number of rows matched, which is not necessarily the same as the number of rows that were actually modified - an UPDATE statement, for example, may have no net change on a given row if the SET values given are the same as those present in the row already. Such a row would be matched but not modified. On backends that feature both styles, such as MySQL, rowcount is configured by default to return the match count in all cases.
ResultProxy.rowcount 仅与 UPDATE 或 DELETE 语句结合使用.与 Python DBAPI 所说的相反，它确实不返回 SELECT 结果中可用的行数语句，因为当行是无缓冲.
ResultProxy.rowcount is only useful in conjunction with an UPDATE or DELETE statement. Contrary to what the Python DBAPI says, it does not return the number of rows available from the results of a SELECT statement as DBAPIs cannot support this functionality when rows are unbuffered.
ResultProxy.rowcount 可能并非所有方言都完全实现.特别是，大多数 DBAPI 不支持聚合行计数结果来自 executemany 调用.ResultProxy.supports_sane_rowcount() 和 ResultProxy.supports_sane_multi_rowcount() 方法将从方言(如果已知每种用法都受支持).
ResultProxy.rowcount may not be fully implemented by all dialects. In particular, most DBAPIs do not support an aggregate rowcount result from an executemany call. The ResultProxy.supports_sane_rowcount() and ResultProxy.supports_sane_multi_rowcount() methods will report from the dialect if each usage is known to be supported.
这篇关于SQL Alchemy ResultProxy.rowcount 不应为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
