Python psycopg2 游标

Question

来自 psycopg2 文档:

When a database query is executed, the Psycopg cursor usually fetches all the records returned by the backend, transferring them to the client process. If the query returned an huge amount of data, a proportionally large amount of memory will be allocated by the client. If the dataset is too large to be practically handled on the client side, it is possible to create a server side cursor.

我想查询一个可能包含数千行的表，并对每一行执行一些操作。普通游标真的会把整个数据集带到客户端吗？这听起来不太合理。代码大致如下:

conn = psycopg2.connect(url)
cursor = conn.cursor()
cursor.execute(sql)
for row in cursor:
    do some stuff
cursor.close()

我希望这是一个流操作。第二个问题是关于游标的范围。在我的循环中，我想更新另一个表。我是否需要每次都打开一个新游标并关闭？每个项目更新都应该在其自己的事务中，因为我可能需要进行回滚。

for row in cursor:
    anotherCursor = anotherConn.cursor()
    anotherCursor.execute(update)
    if somecondition:
        anotherConn.commit()
    else:
        anotherConn.rollback
cursor.close()

======== 编辑:我对第一部分的回答 ========

好的，我将尝试回答问题的第一部分。普通游标实际上会在您调用 execute 时立即带入整个数据集，甚至在开始迭代结果集之前。您可以通过在每个步骤检查进程的内存占用来验证这一点。但是服务器端游标的需要实际上是由于 postgres 服务器而不是客户端，并且在此处记录:http://www.postgresql.org/docs/9.3/static/sql-declare.html

现在，这并不能从文档中立即看出，但实际上可以在事务期间临时创建此类游标。无需使用特定的 SLQ 语句等在数据库中显式创建返回 refcursor 的函数。使用 psycopg2，您只需在获取游标时提供名称，将为该事务创建一个临时游标。所以不是:

 cursor = conn.cursor()

你只需要:

 cursor = conn.cursor('mycursor')

就是这样，它起作用了。我假设在设置 fetchSize 时使用 JDBC 时在幕后完成了同样的事情。它只是更透明一点。请在此处查看文档:https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor

您可以通过查询同一事务内的 pg_cursors View 来测试它是否有效。服务器端游标在获取客户端游标后出现，在关闭客户端游标后消失。所以底线:我很高兴对我的代码进行更改，但我必须说，对于没有 postgres 经验的人来说，这是一个很大的问题。

Answer 1

其实，你已经回答了问题；)。

1. 是的，您应该使用服务器端游标来流式传输记录 http://initd.org/psycopg/docs/usage.html#server-side-cursors

来自文档:

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
$$ LANGUAGE plpgsql;

在代码中:

cur1 = conn.cursor()
cur1.callproc('reffunc', ['curname'])

cur2 = conn.cursor('curname')
for record in cur2:     # or cur2.fetchone, fetchmany...
    # do something with record
    pass

1. 是的，如果您想使用服务器端游标获取行，您应该打开新游标。