如何在django和psycopg2中使用服务器端游标？ [英] How can I use server-side cursors with django and psycopg2?

问题描述

我正尝试在psycop2中使用服务器端光标，如此博客文章。本质上，这是通过django.db中的

I'm trying to use a server-side curser in psycop2 as detailed in this blog post. In essence, this is achieved with

from django.db import connection

if connection.connection is None:
    cursor = connection.cursor()
    # This is required to populate the connection object properly

cursor = connection.connection.cursor(name='gigantic_cursor')

当我执行查询时：

cursor.execute('SELECT * FROM %s WHERE foreign_id=%s' % (table_name, id))

我收到 ProgrammingError ：

psycopg2.ProgrammingError: can't use a named cursor outside of transactions

---

$之外使用命名游标b $ b

我天真地尝试使用

---

I've naively tried to create a transaction using

cursor.execute('BEGIN')

在执行 SELECT 语句之前。但是，这会导致 cursor.execute（'BEGIN'）行生成相同的错误。

before executing the SELECT statement. However, that results in the same error generated from the cursor.execute('BEGIN') line.

I '也尝试过使用

cursor.execute('OPEN gigantic_cursor FOR SELECT * FROM %s WHERE foreign_id=%s' % (table_name, id))

但我得到的结果相同。

如何在django中进行交易？

How do I make a transaction in django?

推荐答案

正如您在问题中提到的，但我会在这里为将来的读者重申：还可以使用显式命名的游标，而无需绕过Django的公共API：

As you mention in your question but I'll reiterate here for future readers: it's also possible to use explicitly named cursors without bypassing Django's public API:

from django.db import connection, transaction

with transaction.atomic(), connection.cursor() as cur:
    cur.execute("""
        DECLARE mycursor CURSOR FOR
        SELECT *
        FROM giant_table
    """)
    while True:
        cur.execute("FETCH 1000 FROM mycursor")
        chunk = cur.fetchall()
        if not chunk:
            break
        for row in chunk:
            process_row(row)

这篇关于如何在django和psycopg2中使用服务器端游标？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！