Question 217977 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to parameterise multiple tuples using psycopg2, without using an f-string

I’m wondering how I would parameterise the following postgres:

UPDATE your_table as t
SET val = x.val_to
FROM ( VALUES 
  (1, 20),
  (3, 44)
) as x (val_from, val_to)
WHERE t.val = x.val_from
AND
your_table.other_id = %(other_id_value)
;

where (1, 20), (3, 44) would be parameterised using psycopg2.

Using :

cur.execute("""
UPDATE your_table as t
SET val = x.val_to
FROM ( VALUES 
  $(vals)s
) as x (val_from, val_to)
WHERE t.val = x.val_from
AND
your_table.other_id = %(other_id_value)s
;
""", { 
    'other_id_value' : 3843,
    'vals' : [(1, 20), (3, 44)] 
}
)

Doesn’t work.

In psycopg2 https://www.psycopg.org/docs/extras.html they have:

>>> execute_values(cur,
... """UPDATE test SET v1 = data.v1 FROM (VALUES %s) AS data (id, v1)
... WHERE test.id = data.id""",
... [(1, 20), (4, 50)])

But I need to parameterise the data.id there as well, not just the VALUES.

Tags:

2

Answers


  1. 0

    Split the SQL into two parts. Construct the VALUES separately. Concatenate the strings:

    sql_1 = 'UPDATE your_table as t SET val = x.val_to FROM (VALUES'

sql_2 = ') as x (val_from, val_to) WHERE t.val = x.val_from;'

values = (1, 20), (3, 44)

sql = sql_1 + ','.join(map(repr, values)) + sql_2

print(sql)

    Output:

    UPDATE your_table as t SET val = x.val_to FROM (VALUES(1, 20),(3, 44)) as x (val_from, val_to) WHERE t.val = x.val_from;
    Login or Signup to reply.
  2. 0

    psycopg2.extras provides the function psycopg2.extras.execute_values just for this purpose.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search