CODEWITHSUNDEEP
pythonsqlpython-2.7rawsql
MaNKuR
MaNKuR

Reputation: 2704

Python: Create Sql raw query with In clause with list data

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.

Upvotes: 1

Views: 2118

Answers (2)

Milad Mahmoodi
Milad Mahmoodi

Reputation: 1

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

Upvotes: 0

Roel Zantinge
Roel Zantinge

Reputation: 21

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}');")

Upvotes: 0

Related Questions