Pass Python list as a input to Redshift in SQL

Question

I want to provide a Python list

sql_foramt= ['HP7CXNGSUFEPZCO4GS5RQPY6XY', '9475EFWZCNARPEJEZEMXDFHIBI', 
             '6ECWZUQGEJCR5EZXDH9URCN53M', 'XZ9P6KTDGREM5KIXUO9IHCTKAQ']

as a input to above SQL query in Python.

cur.execute("""CREATE TEMP TABLE ##Validation_Marc  (
Codes varchar(250) );


INSERT INTO ##Validation_Marc
VALUES

{}.format(','.join(list_name);

I'm trying to run the SQL query via Python using psycopg2 library. I have tried the above method, but every time I try I get the error:

Traceback (most recent call last): File "", line 48, in psycopg2.errors.SyntaxError: syntax error at or near "{" LINE 8: {}.format(','.join(list_name);

I'm unable to move forward , plese help

Parfait · Accepted Answer · 2020-09-14 18:53:50Z

1

Two things are at issue in your code:

Running two separate SQL commands in a cursor.execute call. This is a misunderstanding of Python DB-APIs which usually allows only one command per execute call.
Improperly including format keyword inside the string. This is a general Python syntax issue of string interpolation. Specifically, .format is a method to be run on a string. See docs.

Consider separating both SQL statements and properly using string.format to interpolate %s placeholders with parameterization. For illustration, .format is called on a separate line.

conn = psycopg2.connect(...)

sql = "CREATE TEMP TABLE ##Validation_Marc ( Codes varchar(250) )"
cur.execute(sql)
conn.commit()


mylist = ['HP7CXNGSUFEPZCO4GS5RQPY6XY', '9475EFWZCNARPEJEZEMXDFHIBI', 
          '6ECWZUQGEJCR5EZXDH9URCN53M', 'XZ9P6KTDGREM5KIXUO9IHCTKAQ']

sql = "INSERT INTO ##Validation_Marc (Codes) VALUES {}"    
cur.execute(query = sql.format(', '.join(['(%s)' for _ in mylist])), # FORMAT PLACEHOLDERS
            vars = mylist)                                           # BIND PARAMS 
conn.commit()

edited Sep 14, 2020 at 18:53

answered Sep 14, 2020 at 15:38

Parfait

108k19 gold badges103 silver badges138 bronze badges

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

3 Comments

user12149113 Over a year ago

when i'm trying above method , I'm getting Traceback (most recent call last): File "<input>", line 3, in <module> psycopg2.errors.SyntaxError: INSERT has more expressions than target columns

Parfait Over a year ago

Try to explicitly specify target column. See edit adding column name to append query.

Parfait Over a year ago

Actually, I also had a redundant pair of parentheses around all VALUES tuples. See edit.

Maurice Meyer · Accepted Answer · 2020-09-14 16:48:41Z

It is not quite clear how you want the list to be inserted, but let psycopg2/execute format the query for you:

import psycopg2
import json

conn = psycopg2.connect("...")
cur = conn.cursor()

sql = "CREATE TEMP TABLE __Validation_Marc ( Codes varchar(250) )"
cur.execute(sql)

mylist = ['HP7CXNGSUFEPZCO4GS5RQPY6XY', '9475EFWZCNARPEJEZEMXDFHIBI', '6ECWZUQGEJCR5EZXDH9URCN53M', 'XZ9P6KTDGREM5KIXUO9IHCTKAQ']
sql = "INSERT INTO __Validation_Marc VALUES (%s)"

# Option1: Insert each code as record set
for value in mylist:
    cur.execute(sql, (value, ))
conn.commit()

# Option2: Insert codes as one recordset comma separated
cur.execute(sql, (','.join(mylist),) )
conn.commit()

# Option3: insert as string as it is:
cur.execute(sql, (json.dumps(mylist),) )
conn.commit()

cur.execute("select * from __Validation_Marc")

for row in cur.fetchall():
    print(row)

Out:

('HP7CXNGSUFEPZCO4GS5RQPY6XY',)
('9475EFWZCNARPEJEZEMXDFHIBI',)
('6ECWZUQGEJCR5EZXDH9URCN53M',)
('XZ9P6KTDGREM5KIXUO9IHCTKAQ',)
('HP7CXNGSUFEPZCO4GS5RQPY6XY,9475EFWZCNARPEJEZEMXDFHIBI,6ECWZUQGEJCR5EZXDH9URCN53M,XZ9P6KTDGREM5KIXUO9IHCTKAQ',)
('["HP7CXNGSUFEPZCO4GS5RQPY6XY", "9475EFWZCNARPEJEZEMXDFHIBI", "6ECWZUQGEJCR5EZXDH9URCN53M", "XZ9P6KTDGREM5KIXUO9IHCTKAQ"]',)

Note:
I had to change the table name, my postgres db doesn't allow that name:

Collectives™ on Stack Overflow

Pass Python list as a input to Redshift in SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related