Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
690 views
in Technique[技术] by (71.8m points)

python - SQL Alchemy ResultProxy.rowcount should not be zero

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...
' % results.rowcount
print results.keys()

for i in results:
    print repr(i)

Output:

0 rows returned by query...

[u'val']
(1,)
(2,)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.

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.

The notes at ResultProxy.rowcount discuss this further (http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=rowcount#sqlalchemy.engine.ResultProxy.rowcount):

Notes regarding ResultProxy.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 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 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.

  • Statements that use RETURNING may not return a correct rowcount.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...