python list in sql as parameter

Question

I am trying to pass a python list of integers in SQL components. I already looked in the previous questions but none helped me to solve the error. Here is what I am doing:

import cx_Oracle
SQL_Components = '''
SELECT /*+ FIRST_ROWS materialize */
    xxx,
    xxx
FROM
    xxx
    INNER JOIN xxx
    INNER JOIN xxx
    INNER JOIN xxx
    INNER JOIN xxx
WHERE
    tpr.tpr_name LIKE 'M39AAA%' AND mml.mml_name IN (%s)
'''

def get_TPM_IDsDict():
    global chosen_lot
    chosen_lot=[]
    print ("Executing: get_TPM_IDsDict")
    f = open("XXX.txt", "r")
    for line in f:
        chosen_lot.append(line.strip())
    chosen_lot = ["'%s'" % x for x in chosen_lot]
    Cursor.execute(SQL_Components % ("','".join(chosen_lot)))

I get this error:

Cursor.execute(SQL_Components % ("','".join(chosen_lot)))
ValueError: unsupported format character ''' (0x27) at index 580

you gave a SQL statement, so you're using a DBMS such as Oracle, Postgres, ... I've meant you tag that DBMS for the question. Btw, it's better to share this array within the question , not as a comment. — Barbaros Özhan
– Barbaros Özhan, Commented Jun 21, 2020 at 11:43
Looks like your query has more characters than what is posted. Do you have %\' anywhere in SQL_Components? This will cause the error you face. — rinz1er
– rinz1er, Commented Jun 21, 2020 at 13:51
@ShriramRamesh No I don't have any %\' in SQL_Components — Omar Charfeddine
– Omar Charfeddine, Commented Jun 21, 2020 at 14:46

Parfait · Accepted Answer · 2020-06-21 17:49:12Z

2

Consider numbered parameters which cxOracle supports. Also, be sure to actually pass in parameter values in second argument of cursor.execute. Do not overwrite your very values!

And in all Python DB-APIs (cxOracle, ibm_db, psycopg2, pyodbc, etc.), parameter placeholders should not be quoted. Also, in Python generally avoid using % for string interpolation as it is de-emphasised (not officially deprecated yet). Instead, use str.format or F-strings (Python 3.6+).

SQL_Components = '''
                 SELECT /*+ FIRST_ROWS materialize */
                     xxx,
                     xxx
                 FROM
                     xxx
                     INNER JOIN xxx
                     INNER JOIN xxx
                     INNER JOIN xxx
                     INNER JOIN xxx
                 WHERE
                     tpr.tpr_name LIKE 'M39AAA%' AND mml.mml_name IN ({})
                 '''
# BUILD NUMBERED PLACEDHOLERS WITH enumerate
prms = [":" + str(i+1) for i,_ in enumerate(chosen_lot)] 

# INTERPOLATE WITH str.format
Cursor.execute(SQL_Components.format(", ".join(prms)), chosen_lot)

answered Jun 21, 2020 at 17:49

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

1 Comment

Christopher Jones Over a year ago

For general reference the cx_Oracle documentation on binding multiple values in IN clauses is here.

Collectives™ on Stack Overflow

python list in sql as parameter

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related