CODEWITHSUNDEEP
pythonsqlpostgresql
Young
Young

Reputation: 21

Cursor.execute with

So I'm trying to run a sql command using cursor.execute where

cid = data[key]['customerid']
name = data[key]['name']
bdate = data[key]['birthdate']
ffon = data[key]['frequentflieron']

curs.execute("INSERT INTO customers (c,n,b,f) VALUES (%s,%s,%s,%s)) WHERE NOT EXISTS (Select 1 FROM customers WHERE customerid = (c) VALUES (%s));",(cid,name,bdate,ffon,cid))

Tried 

curs.execute("""INSERT INTO customers (c,n,b,f) 
            VALUES (%s,%s,%s,%s) WHERE NOT EXISTS (
                Select 1 
                FROM customers 
                where customerid = %s);""",(cid,name,bdate,ffon,cid)

I'm getting this error now

   Traceback (most recent call last):
File "psy.py", line 44, in <module>
  custinfo(data,key)
File "psy.py", line 28, in custinfo
  where customerid = %s);""",(cid,name,bdate,ffon,cid))
psycopg2.ProgrammingError: syntax error at or near "WHERE"
LINE 2: ...ust1000','XYZ','1991-12-06','Southwest Airlines') WHERE NOT ...

How do I do both an insert with a where clause?

Upvotes: 1

Views: 550

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169544

You have an extra close-bracket after the placeholders and your subquery needs some touching up. Try instead:

curs.execute("""INSERT INTO customers (c,n,b,f) 
                VALUES (%s,%s,%s,%s) WHERE NOT EXISTS (
                    Select 1 
                    FROM customers 
                    where customerid = %s);""",(cid,name,bdate,ffon,cid))

Hrm, maybe that syntax is illegal. Try this instead:

curs.execute("""INSERT INTO customers (c,n,b,f) 
                VALUES (%s,%s,%s,%s) 
                ON CONFLICT (customerid) DO NOTHING);""",(cid,name,bdate,ffon,cid))

Upvotes: 1

Related Questions