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?

Kaushik Nayak · Accepted Answer · 2019-06-25 07:59:03Z

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, 2019 at 7:59

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

P.Gichia Over a year ago

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.

Kaushik Nayak Over a year ago

@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.

P.Gichia Over a year ago

You are very right @Kaushik Nayak, thanks for pointing that out, it worked fine..

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related