CODEWITHSUNDEEP
pythonsqlamazon-redshiftpsycopg2
user12149113
user12149113

Reputation:

Pass Python list as a input to Redshift in SQL

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

Upvotes: 0

Views: 1724

Answers (2)

Parfait
Parfait

Reputation: 107687

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()

Upvotes: 1

Maurice Meyer
Maurice Meyer

Reputation: 18126

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:

Upvotes: 0

Related Questions