服务器端游标的 Psycopg2 行数 [英] Psycopg2 rowcount for server side cursor

问题描述

我正在查询 Postgres 数据库以获取大量结果，并希望使用服务器端游标将结果流式传输到我的客户端.看起来当我这样做时，游标的 rowcount 属性现在在我执行查询后设置为 -1.我正在像这样创建光标:

I am querying a Postgres database for a large number of results and want to use server side cursors to stream the results to my client. It looks like when I do this, the rowcount attribute of the cursor is now set to -1 after I execute the query. I'm creating the cursor like so:

以 db.cursor('cursor_name') 作为游标:

有没有办法在从数据库流式传输结果时找到查询的结果数量?(我可以做一个 SELECT COUNT(*)，但我想避免这种情况，因为我试图抽象出查询周围的代码，这会使 API 复杂化).

Is there a way to find the number of results of my query while streaming results from the database? (I could do a SELECT COUNT(*), but I'd like to avoid that because I'm trying to abstract away the code around the query and that would complicate the API).

推荐答案

在服务器端游标的情况下，虽然 cursor.execute() 返回，但查询不一定被执行那个时候的服务器，所以行数对 psycopg2 不可用.这与 DBAPI 2.0 规范 一致，其中指出 rowcount 应该是 -1.

In the case of a server-side cursor, although cursor.execute() returns, the query has not necessarily been executed by the server at that point, and so the row count is not available to psycopg2. This is consistent with the DBAPI 2.0 spec which states that rowcount should be -1 if the row count of the last operation is indeterminate.

尝试用cursor.fetchone()强制它，例如更新cursor.rowcount，但只通过检索的项目数，所以没有用.cursor.fetchall() 将导致 rowcount 被正确设置，但是，执行您试图避免的完整查询和数据传输.

Attempts to coerce it with cursor.fetchone(), for example, updates cursor.rowcount, but only by the number of items retrieved, so that is not useful. cursor.fetchall() will result in rowcount being correctly set, however, that performs the full query and transfer of data that you seek to avoid.

避免使用完全独立的查询来获取计数并且应该给出准确结果的可能解决方法是:

A possible workaround that avoids a completely separate query to get the count, and which should give accurate results is:

select *, (select count(*) from test) from test;

这将导致每一行都将表格行数作为最后一列附加.然后，您可以使用 cursor.fetchone() 获取表格行数，然后取最后一列:

This will result in each row having the table row count appended as the final column. You can then get the table row count using cursor.fetchone() and then taking the final column:

with db.cursor('cursor_name') as cursor:
    cursor.execute('select *, (select count(*) from test) from test')
    row = cursor.fetchone()
    data, count = row[:-1], row[-1]

现在 count 将包含表中的行数.您可以使用 row[:-1] 来引用行数据.

Now count will contain the number of rows in the table. You can use row[:-1] to refer to the row data.

这可能会减慢查询速度，因为将执行可能很昂贵的 SELECT COUNT(*)，但一旦完成检索数据应该很快.

This might slow down the query because a possibly expensive SELECT COUNT(*) will be performed, but once done retrieving the data should be fast.

这篇关于服务器端游标的 Psycopg2 行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！