Anyway to Upsert database using PostgreSQL in Python

Question

I want to upsert with least effort, for simplicity, i reduce columns, this not work:

sql = '''INSERT INTO temp.tickets
    (id, created_at, updated_at, emails, status)
VALUES
    (%s, %s, %s, %s, %s)
    ON CONFLICT (id)
    DO UPDATE SET ( emails, status) values (%s,%s) 
    
    '''

cursor = cm.cursor()
## cm is a custom module
cursor.execute(sql, (ticket['id'],
                     ticket['created_at'],
                     ticket['updated_at'],
                     ticket['emails'], ticket['status'], )

This code show Error:

    return super(DictCursor, self).execute(query, vars)
IndexError: tuple index out of range

What I need to change in the cursor.execute() to work?

The Bellow code work but I like to use %s instead of type: email = excluded.email for each columns

sql = '''INSERT INTO temp.tickets
    (id, created_at, updated_at, emails, status)
VALUES
    (%s, %s, %s, %s, %s)
    ON CONFLICT (id)
    DO UPDATE SET emails = excluded.eamils, status = excluded.status
    
    '''

cursor = cm.cursor() 
# cm is a custom module

cursor.execute(sql, (ticket['id'],
                     ticket['created_at'],
                     ticket['updated_at'],
                     ticket['emails'], ticket['status'], )

There are two Relevant Questions link1, link2

Renato · Accepted Answer · 2020-11-04 12:38:29Z

2

I would try something like this:

sql = '''INSERT INTO temp.tickets
    (id, created_at, updated_at, emails, status)
VALUES
    (%s, %s, %s, %s, %s)
    ON CONFLICT (id)
    DO UPDATE SET ( emails, status) values (%s,%s) 
    
    '''

cursor = cm.cursor()
## cm is a custom module
cursor.execute(sql, (ticket['id'],
                     ticket['created_at'],
                     ticket['updated_at'],
                     ticket['emails'], 
                     ticket['status'],
                     ticket['emails'], 
                      ticket['status'] )

Thre number of %s must match the number of parameters.

edited Nov 4, 2020 at 12:38

answered Nov 4, 2020 at 12:12

Renato

2,1871 gold badge13 silver badges25 bronze badges

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

1 Comment

Tom Tom Over a year ago

when I change the cursor.execute() it show:

Error: psycopg2.errors.SyntaxError: syntax error at or near "values" LINE 6:         DO UPDATE SET ( emails, status) values (ARRAY['su...

Belayer · Accepted Answer · 2020-11-04 23:36:56Z

-1

When Postgres encounters a captured conflict it basically creates a record called EXCLUDED that contains the values you attempted to insert, You can refer to this record in DO UPDATE. Try the following:

INSERT INTO temp.tickets
    (id, created_at, updated_at, emails, status)
VALUES
    (%s, %s, %s, %s, %s)
    ON CONFLICT (id)
    DO UPDATE 
          SET emails = excluded.emails
            , status = excluded.status
            , updated_at = excluded.updated_at    -- my assumption.  
 ...

You will have to format is into the requirements of your source language.

answered Nov 4, 2020 at 23:36

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

6 Comments

Tom Tom Over a year ago

So this means I need to type for each columns?

Belayer Over a year ago

I am not sure what you mean by "type each column". You have coded %s which I presume each one is a parameter to your insert. This should not change how you pass them in your original statement. If you mean that you must assign a data type again that would be a feature of your source language. The only thing different from your original is the code after DO UPDATE.

Tom Tom Over a year ago

Your answer is same as the second code block of mine that works, I mean any other way not to type like this emails = excluded.emails for each parameter, but use %s instead and change the python code below.

Belayer Over a year ago

Unfortunately not. Postgres in totally unaware of the meaning of ant of the particular place holder (%s). It knows only the position, if you use the same variable from your source more than once you need a place holder for each occurrence. That is what @Renato has suggested. You may be able to a numeric notation, so for email is %4s and status is %5s for status, and repeat the same in the DO Update, but I do not know if Postgres supports that. I never tried it just to avoid typing.

Tom Tom Over a year ago

So do you have new solution? could you type it? @belayer

|

Collectives™ on Stack Overflow

Anyway to Upsert database using PostgreSQL in Python

2 Answers 2

1 Comment

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related