psycopg2: Update multiple rows in a table with values from a tuple of tuples

Question

I'm attempting to update several rows at once using a tuple of tuples. I figured out how to construct the sql statement from this post, but implementing it in psycopg2 has proven to be more challenging. Here's what I have:

c = db.cursor()

new_values = (("Richard",29),("Ronald",30))

sql = """UPDATE my_table AS t 
         SET name = e.name 
         FROM (VALUES %s) AS e(name, id) 
         WHERE e.id = t.id;"""

c.execute(sql, (new_values,))

The result is an error: ProgrammingError: table "e" has 1 columns available but 2 columns specified This is because the FROM clause is being interpreted as:

FROM (VALUES (("Richard",29),("Ronald",30)))

instead of:

FROM (VALUES ("Richard",29),("Ronald",30))

I can work around this by doing the following but it seems unsafe:

import re
c = db.cursor()

sql = """UPDATE my_table AS t 
         SET name = e.name 
         FROM (VALUES %s) AS e(name, id) 
         WHERE e.id = t.id;"""
sql = c.mogrify(sql, (new_values,))

# Replace the first occurance of '((' with '('.
sql = sql.replace('((', '(',1)

# Replace the last occurance of '))' with ')'.
sql = re.sub(r'(.*)\)\)', r'\1)', sql)

sql = c.execute(sql)

Is there a better way to do this?

Micamicaela

Answer 1

This post pointed me in the right direction. The documentation for extras.execute_values also contains a great example using the UPDATE clause.

c = db.cursor()
update_query = """UPDATE my_table AS t 
                  SET name = e.name 
                  FROM (VALUES %s) AS e(name, id) 
                  WHERE e.id = t.id;"""

psycopg2.extras.execute_values (
    c, update_query, new_values, template=None, page_size=100
)

Micamicaela

ugh this seems so roundabout. Anyone else feel like anytime something fails in psycopg2 it's guaranteed to take over an hour of googling to find the exact syntax to make it work? Like why the huge difference in syntax between a bulk insert and bulk update Appreciate the answer, just venting – Adrastus

if someone has connection already closed error, try to move the id before name. It'll save you 3 days :) – Kaylenekayley

Answer 2

Additionally for @tdnelson2 answer - if you use some kind of list comprehension to generate parameters for SET, you can occur a problem with timestamps (this problem doesn't appear when you work with psycopg2 insert). Then you will need to additionally add ::timestamp to set block. In my case it looked like this:

UPDATE ods.dict_adr_spb as t
SET adrspb_house_id=e.adrspb_house_id, adrspb_t_creation_date=e.adrspb_t_creation_date::timestamp
FROM (VALUES %s) as e(adrspb_house_id, adrspb_t_creation_date)
WHERE e.adrspb_house_id = t.adrspb_house_id;

Jujitsu

It's not very clear how this relates to the question that was asked. – Negligent