Python: Create Sql raw query with In clause with list data [duplicate]

Question

Recently I stuck for a moment while preparing the raw Sql Query having In clause to it and the In clause data is a python list. Okay Let me put my example here.

Sql Query that I wanted

sql_query = 'SELECT * FROM student WHERE first_name IN ("Dean");'

From the data I was having

data = ["Dean"]
query = 'SELECT * FROM student WHERE first_name IN %s;' % str(tuple(data))
# result was "SELECT * FROM student WHERE first_name IN ('Dean',) "
# see the *Comma* just before close parentheses which is a Sql error

But After doing some practice I came up with a solution of something like this

str_data = ','.join(repr(x) for x in data)
query = 'SELECT * FROM student WHERE first_name IN (%s);' % str_data

# Gives proper result i.e "SELECT * FROM student WHERE first_name IN ('Dean');"

Now my question is, is this a elegant solution or we have several other optimized approaches out there in python. Would be appreciable to have your views on this :).

Edit Reached to another solution

data = tuple(data) if len(data) > 1 else "('%s')" % data[0] # Assumption: data  should not be empty (in my case it is true)
query = 'SELECT * FROM student WHERE first_name IN {};'.format(data)

Note: Still looking for some views from you guys if it can be optimized further.

What database? What client library? Also, doing anything besides a parametrized query (like the string formatting you use above) for data that could potentially come from the user carries an enormous risk of making you vulnerable to SQL injection. — jpmc26
– jpmc26, Commented Apr 4, 2015 at 8:23
Its DB2 and we are using SqlAlchemy as toolkit. And here data is having set of unique varchar that we are being taken care of. — MaNKuR
– MaNKuR, Commented Apr 4, 2015 at 9:01

Roel Zantinge · Accepted Answer · 2020-12-15 09:12:04Z

0

I used this in python3 on a postgres database, specifically if you want strings after the IN operator. please pay attention to the double quotes vs single quotes:

data = ['test', 'test2', 'test3']
data_str = "', '".join(data)
query = "SELECT * FROM student WHERE first_name IN ('{}');".format(data_str))

or like this if you prefer f-strings:

print(f"SELECT * FROM student WHERE first_name IN ('{data_str}');")

answered Dec 15, 2020 at 9:12

Roel Zantinge

215 bronze badges

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

Comments

Milad Mahmoodi · Accepted Answer · 2024-01-30 06:19:04Z

0

It would help if you emphasized the importance of preventing SQL injection. Safeguarding against such vulnerabilities is highly important. If you have a method for dynamically managing multiple values for the IN expression and protecting against SQL injection, that method is indeed good.

    data = ['test', 'test2', 'test3']
    data_str = {','.join(['%s'] * len(data))}
    query = f"SELECT * FROM student WHERE first_name IN ({data_str});"
    ...
    with connection.cursor() as cursor:
        cursor.execute(query, data)
    ...

answered Jan 30, 2024 at 6:19

Milad Mahmoodi

1

Collectives™ on Stack Overflow

Python: Create Sql raw query with In clause with list data [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related