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, Jun 21 '20 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, Jun 21 '20 at 13:51
@ShriramRamesh No I don't have any `%\'` in `SQL_Components` — Omar Charfeddine, Jun 21 '20 at 14:46

score 2 · Accepted Answer · answered Jun 21 '20 at 17:49

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)

For general reference the cx_Oracle documentation on binding multiple values in IN clauses is [here](https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html#binding-multiple-values-to-a-sql-where-in-clause). — Christopher Jones, Jun 21 '20 at 23:25

python list in sql as parameter

1 Answers1