Passing single and multiple value in a prepared SQL Query in Python

Question

I am trying to create a SQL Query based on the dynamic inputs. It can be single or multiple. However, when I am trying to pass the parameters to the prepared parameters I see that since comma separated values are tuples, it is adding an extra bracket in IN clause. Any idea how to solve it ?

import pyodbc

#engine_ids = 1
engine_ids = 1,2
 
#platform_ids = 1
platform_ids = 1,2

sql_query = ("select column_A from table where ENGINE IN ({eng_ids}) AND PLATFORM IN({plat_ids})").format(eng_ids=engine_ids, plat_ids=platform_ids)

print(sql_query)

Expected Result - select column_A from table where ENGINE IN (1, 2) AND PLATFORM IN(1, 2)

Actual Result - select column_A from table where ENGINE IN ((1, 2)) AND PLATFORM IN((1, 2))

it doesnt work with single inputs :( .. it produces a SQL Query -> select column_A from table where ENGINE IN 1 AND PLATFORM IN 1 and this throws an error in my DB — Ezio
– Ezio, Commented Jul 27, 2020 at 19:23
I have found a quick workaround though- sql_query.replace('((',"(").replace("))",")") but not really sure if it is a good way to deal with this problem — Ezio
– Ezio, Commented Jul 27, 2020 at 19:26

Gord Thompson · Accepted Answer · 2020-07-27 19:48:48Z

If you want to avoid the pitfalls of SQL injection and use a proper parameterized query then you could do something like this:

def create_in_placeholders(thing):
    if isinstance(thing, tuple):
        num_values = len(thing)
    else:
        num_values = 1
    return "(" + ",".join("?" * num_values) + ")"


def to_tuple(thing):
    if isinstance(thing, tuple):
        return thing
    else:
        return (thing, )

# test data
engine_ids = (1, 2)
platform_ids = (1, 2)

engine_in_placeholders = create_in_placeholders(engine_ids)
platform_in_placeholders = create_in_placeholders(platform_ids)

sql = (
    f"select column_A from table "
    f"where ENGINE IN {engine_in_placeholders} "
    f"and PLATFORM IN {platform_in_placeholders}"
)
print(sql)
# select column_A from table where ENGINE IN (?,?) and PLATFORM IN (?,?)
params = to_tuple(engine_ids) + to_tuple(platform_ids)
print(params)
# (1, 2, 1, 2)

crsr.execute(sql, params)

Ezer K · Accepted Answer · 2020-07-27 19:28:49Z

0

try lists, f strings and unpacking:

import pyodbc
engine_ids = [1,2]

platform_ids = [1,2]

sql_query = f"select column_A from table where ENGINE IN {*engine_ids, } AND PLATFORM IN {*platform_ids,}"
print(sql_query)

let me know if it worked

answered Jul 27, 2020 at 19:28

Ezer K

3,7615 gold badges25 silver badges50 bronze badges

1 Comment

Gord Thompson Over a year ago

For engine_ids = 1 that will fail with TypeError: 'int' object is not iterable. For engine_ids = (1, ) it will render ... where ENGINE IN (1,) AND ... which might give some SQL parsers indigestion.

SamR · Accepted Answer · 2020-07-27 20:17:45Z

You need to either coerce the non-tuple to a tuple or explicitly handle tuples differently from non-tuples.

The simplest (and, arguably, clearest) approach might be to coerce your ID parameters to tuples, and remove the extra parentheses in your sql query template. E.g.,

def to_tuple(arg):
    if isinstance(arg, (tuple,list)):
        return tuple(arg)
    return (arg,)
...
engine_ids = to_tuple(engine_ids)

If your application is not a one-off, it might be better to make the SQL generation a little smarter. This example uses variant templates for the individual where clauses.

def equals_or_in(colname, ids):
    if isinstance(ids, (tuple,list)):
        return '{} IN {}'.format(colname, ids)
    return '{} = {}'.format(colname, ids)
...
sql_query = ' '.join([
        'select column_A from table where',
        ' and '.join([
            equals_or_in('ENGINE', engine_ids),
            equals_or_in('PLATFORM', platform_ids)
        ])
    ])

I have used both these approaches at various times.

Collectives™ on Stack Overflow

Passing single and multiple value in a prepared SQL Query in Python

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related