python postgres change values of rows while reading results

Question

I am using python3, postgress 10 and Psycopg2 to query multiple records like so

import psycopg2
conn = psycopg2.connect(<my connection string>)
with conn:
    with conn.cursor() as cur:        
        cur.execute('select id,field1 from table1')
        for id, field1 from cur.fetchall():
           print(id,field1)
           #todo: how up update field1 to be f(field1) where f is an arbitrary python function

My question is: how do i update the value of the rows that I am reading and set the value of field1 to some arbitrary python-based calculation

edit: the purpose is to update the rows in the table

Have you tried opening another connection and running the update statements through that? — Keith John Hutchison
– Keith John Hutchison, Commented Dec 9, 2018 at 3:59

klin · Accepted Answer · 2023-01-06 13:35:30Z

You need another cursor, e.g.:

with conn:
    with conn.cursor() as cur:        
        cur.execute('select id,field1 from table1')
        for id, field1 in cur.fetchall():
            print(id,field1)
            with conn.cursor() as cur_update:
                cur_update.execute('update table1 set field1 = %s where id = %s', (f(field1), id))

Note however that this involves as many updates as selected rows, which is obviously not efficient. The update can be done in a single query using psycopg2.extras.execute_values():

from psycopg2.extras import execute_values
    
with conn:
    with conn.cursor() as cur:        
        cur.execute('select id,field1 from table1')
        rows = cur.fetchall()
        for id, field1 in rows:
            print(id,field1)
                
    # convert rows to new values of field1
    values = [(id, f(field1)) for id, field1 in rows]
    sql = '''
        with upd (id, field1) as (values %s)
        update table1 t
        set field1 = upd.field1
        from upd
        where upd.id = t.id
        '''
    with conn.cursor() as cur:
        execute_values(cur, sql, values)

Collectives™ on Stack Overflow

python postgres change values of rows while reading results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related