psycopg2 - 为什么 psycopg2 不允许我们在同一连接中打开多个服务器端游标？

Question

我很好奇为什么 psycopg2 不允许在同一连接中打开多个服务器端游标 ( http://initd.org/psycopg/docs/usage.html#server-side-cursors )。我最近遇到了这个问题，我必须通过用客户端游标替换第二个游标来解决它。但是我还是想知道有没有办法做到这一点。

例如，我在 Amazon Redshift 上有这两个表:

CREATE TABLE tbl_account (
acctid varchar(100),
regist_day date
);

CREATE TABLE tbl_my_artist (
user_id varchar(100),
artist_id bigint
);

INSERT INTO tbl_account
(acctid, regist_day)
VALUES
('TEST0000000001', DATE '2014-11-23'),
('TEST0000000002', DATE '2014-11-23'),
('TEST0000000003', DATE '2014-11-23'),
('TEST0000000004', DATE '2014-11-23'),
('TEST0000000005', DATE '2014-11-25'),
('TEST0000000006', DATE '2014-11-25'),
('TEST0000000007', DATE '2014-11-25'),
('TEST0000000008', DATE '2014-11-25'),
('TEST0000000009', DATE '2014-11-26'),
('TEST0000000010', DATE '2014-11-26'),
('TEST0000000011', DATE '2014-11-24'),
('TEST0000000012', DATE '2014-11-24')
;

INSERT INTO tbl_my_artist
(user_id, artist_id)
VALUES
('TEST0000000001', 2000011247),
('TEST0000000001', 2000157208),
('TEST0000000001', 2000002648),
('TEST0000000002', 2000383724),
('TEST0000000003', 2000002546),
('TEST0000000003', 2000417262),
('TEST0000000004', 2000076873),
('TEST0000000004', 2000417266),
('TEST0000000005', 2000077991),
('TEST0000000005', 2000424268),
('TEST0000000005', 2000168784),
('TEST0000000006', 2000284581),
('TEST0000000007', 2000284581),
('TEST0000000007', 2000000642),
('TEST0000000008', 2000268783),
('TEST0000000008', 2000284581),
('TEST0000000009', 2000088635),
('TEST0000000009', 2000427808),
('TEST0000000010', 2000374095),
('TEST0000000010', 2000081797),
('TEST0000000011', 2000420006),
('TEST0000000012', 2000115887)
;

我想从这 2 个表中进行选择，然后对查询结果进行处理。
我使用 2 个服务器端游标，因为我的查询中需要 2 个嵌套循环。我想使用服务器端游标，因为结果可能非常大。
我使用 fetchmany() 而不是 fetchall() 因为我在单节点集群上运行。

这是我的代码:

import psycopg2
from psycopg2.extras import DictCursor

conn = psycopg2.connect('connection parameters')

cur1 = conn.cursor(name='cursor1', cursor_factory=DictCursor)
cur2 = conn.cursor(name='cursor2', cursor_factory=DictCursor)

cur1.execute("""SELECT acctid, regist_day FROM tbl_account
                WHERE regist_day <= '2014-11-25'
                ORDER BY 1""")
for record1 in cur1.fetchmany(50):
    cur2.execute("""SELECT user_id, artist_id FROM tbl_my_artist
                    WHERE user_id = '%s'
                    ORDER BY 1""" % (record1["acctid"]))
    for record2 in cur2.fetchmany(50):
        print '(acctid, artist_id, regist_day): (%s, %s, %s)' % (
            record1["acctid"], record2["artist_id"], record1["regist_day"])
        # do something with these values

conn.close()

运行时报错:

Traceback (most recent call last):
  File "C:\Users\MLD1\Desktop\demo_cursor.py", line 20, in <module>
    for record2 in cur2.fetchmany(50):
  File "C:\Python27\lib\site-packages\psycopg2\extras.py", line 72, in fetchmany
    res = super(DictCursorBase, self).fetchmany(size)
InternalError: opening multiple cursors from within the same client connection is not allowed.

该错误发生在第 20 行，当时我试图从第二个游标获取结果。

Answer 1

四年后的答案，但可以从同一连接打开多个游标。 (可能是更新了库，修复了上面的问题。)

需要注意的是，您只能使用命名游标调用一次 execute()，因此如果您在 fetchmany 循环中重复使用其中一个游标，您需要删除名称或创建另一个“匿名”游标。