How can I update multiple columns on multiple rows in postgresql using psycopg2

Question

I have a somewhat complex sql query that should update multiple columns on multiple rows in a table. Am trying to pass the multiple parameters to the query and also loop though the data to be updated through psycopg2 but I can't figure out a way to do this.

Here is the sample data I want to loop through.

 data = [(214, 'Feb', 545), (215, 'March', 466)]

So far here is the sql query I have

    query = """
      UPDATE table_1
      SET 
      date_from = 
       (CASE version 
           WHEN 1 THEN '1900-01-01' ELSE 
       ( SELECT date_to 
             FROM table_1 
             WHERE month = data.month
             AND cust_key = data.cust_key 
             AND prod_key = data.prod_key
         AND version = (
               SELECT version-1 
               FROM table_1 
               WHERE month = data.month 
               AND cust_key = data.cust_key 
               AND prod_key = data.prod_key
           ORDER BY version DESC LIMIT 1)
        ) 
    END), 
      date_to = current_date
      FROM (VALUES %s) AS data(cust_key, month, prod_key)
      WHERE month = data.month
      AND cust_key = data.cust_key 
      AND prod_key = data.prod_key
     """

Here is how I am passing my parameters

    WHERE month = data.month
    AND cust_key = data.cust_key 
    AND prod_key = data.prod_key

    FROM (VALUES %s) AS data(cust_key, month, prod_key)

And this is how I am executing the query

    cursor = db.cursor()
    execute_values(cursor, query, (data,))
    db.commit()
    return True

When I execute the query, I get this error psycopg2.errors.InvalidColumnReference: table "data" has 2 columns available but 3 columns specified I have gone through multiple solutions on this site but none seems to work for me.

Is there a way around this?

score 2 · Accepted Answer · answered Jun 25 '19 at 07:59

2

Your data is already in the format as expected by psycopg2.extras.execute_values.

So, do not convert it to another tuple, simply do

execute_values(cursor, query, data)

answered Jun 25 '19 at 07:59

Kaushik Nayak

30,772
5
32
45

Actually @Kaushik Nayak, I tried that before but it still won't work it raises another error saying ***UndefinedFunction: operator does not exist: date = text WHERE month = data.month_name*** It is complaining about the " = " sign. – P.Gichia Jun 25 '19 at 08:13
@P.Gichia : The error is unrelated to the way data is being passed from python. The substitution part works perfectly with the suggested change.You must first fix the query and see if everything works fine logging in through a postgres connection (psql/ pgadmin etc) before using python to run it. If you can't you must ask another question related to the query with sample data and table definitions. My post answers your original question. Consider accepting it if you think it helped you. – Kaushik Nayak Jun 25 '19 at 08:44
You are very right @Kaushik Nayak, thanks for pointing that out, it worked fine.. – P.Gichia Jun 25 '19 at 09:32

How can I update multiple columns on multiple rows in postgresql using psycopg2

1 Answers1

Linked