PostgreSQL INSERT ON CONFLICT and TKinter-CodePudding

I have problem with syntax within python/Tkinter when updating PostgreSQL table.

Syntax works for function code below without ON CONFLICT option:

def myclick_start():
# Create a database or connect to one
conn = psycopg2.connect(database="*",# hidden credentials here 
                    host="*",
                    user="*",
                    password="*",
                    port="*")

# Create cursor
c = conn.cursor()

# Insert Into Database Table
thing1 = o_num.get()
thing2 = op_id.get()
thing3 = proc_name_cb.get()

# this works
c.execute('''INSERT INTO orders (order_id, op_id, status_id) VALUES (%s, %s, %s)''',
          (thing1, thing2, thing3)
          )

# Commit Changes
conn.commit()

# Close Connection
conn.close()

but not working when I want to UPDATE table ON CONFLICT of order_id value:

# but this is not working    
c.execute('''INSERT INTO orders (order_id, op_id, status_id) VALUES (%s, %s, %s)''',
              (thing1, thing2, thing3),
              ON CONFLICT (order_id)
                 DO UPDATE SET op_id = EXCLUDED.op_id, status_id = EXCLUDED.status_id;
              )

Resulted error:

File "E:\***.py", line 229
'''c.execute('''INSERT INTO orders (order_id, op_id, status_id) VALUES (%s, %s, %s)''',
                ^^^^^^
SyntaxError: invalid syntax

I've tried many syntax variants and kind of stuck with my error. Appreciate your help.

CodePudding user response：

If you take a closer look at the syntax highlight, you will notice that your ON CONFLICT ... isn't part of the SQL query (i.e. it's not part of the string that makes up the query).

Moving that part inside the string should solve the problem, like this

c.execute('''INSERT INTO orders (order_id, op_id, status_id) VALUES (%s, %s, %s)
             ON CONFLICT (order_id)
             DO UPDATE SET op_id = EXCLUDED.op_id, status_id = EXCLUDED.status_id;''',
             (thing1, thing2, thing3)
          )