CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
user1513388
user1513388

Reputation: 7451

Updating a postgresql after retrieving it

Hi I'm using the psycopg2 server side cursor feature to retrieve a large dataset from PostgreSQL. I need to get the row (then do something with it) and then update a boolean column to indicate that its been processed. This is what I've tried so far:-

import psycopg2

import psycopg2.extras
import sys

query = """
    select * from exports;
"""


con = psycopg2.connect("dbname='test' user='postgres' password='*****'")

cursor = con.cursor('server_side_cursor', cursor_factory=psycopg2.extras.DictCursor)
cursor.execute(query)

while True:
    rows = cursor.fetchmany(5000)
    if not rows:
        break
    row_count = 0
    for row in rows:
        row_count += 1
        print "row: %s    %s\n" % (row_count, row['id'])
        cursor.execute("UPDATE exports SET (status) = (true), WHERE id = row['id']")
        pass

con.close()

However I'm getting the following error:-

psycopg2.ProgrammingError: can't call .execute() on named cursors more than once

Upvotes: 0

Views: 1583

Answers (1)

Daniele Pantaleone
Daniele Pantaleone

Reputation: 2733

You need a new cursor to execute the UPDATE query:

import psycopg2
import psycopg2.extras
import sys

query = """select * from exports;"""

con = psycopg2.connect("dbname='test' user='postgres' password='*****'")
cursor = con.cursor('server_side_cursor', cursor_factory=psycopg2.extras.DictCursor)
cursor.execute(query)

while True:
    rows = cursor.fetchmany(5000)
    if not rows:
        break
    row_count = 0
    for row in rows:
        row_count += 1
        print "row: %s    %s\n" % (row_count, row['id'])
        cursor2 = con.cursor()
        cursor2.execute("UPDATE exports SET (status) = (true), WHERE id = row['id']")
        cursor2.close()

con.close()

Upvotes: 1

Related Questions