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 · Answer 1 · 2020-11-04T12:38:29.670

1

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 04 '20 at 12:38

answered Nov 04 '20 at 12:12

Renato

2,077
1
11
22

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...` – Tom Tom Nov 05 '20 at 04:12

score -1 · Answer 2 · answered Nov 04 '20 at 23:36

-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 04 '20 at 23:36

Belayer

13,578
2
11
22

So this means I need to type for each columns? – Tom Tom Nov 05 '20 at 03:45
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. – Belayer Nov 05 '20 at 03:53
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. – Tom Tom Nov 05 '20 at 04:08
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. – Belayer Nov 05 '20 at 04:28
So do you have new solution? could you type it? @belayer – Tom Tom Nov 05 '20 at 04:48
No I cannot, I gave you a couple ideas you can try and or research. I do not know Python, at least to extent needed. For me I would just type column=excluded.column as needed. By now I would be done. – Belayer Nov 05 '20 at 05:16

Anyway to Upsert database using PostgreSQL in Python

2 Answers2

Linked