Passing a List of Values from Python to the in Clause of an SQL Query

Passing a list of values from Python to the IN clause of an SQL query

As stated in the comment to the other answer, that approach can fail for a variety of reasons. What you really want to do is create an SQL statement with the required number of parameter placeholders and then use the params= parameter of .read_sql_query() to supply the values:

x = ['1000000000164774783','1000000000253252111']
placeholders = ','.join('?' for i in range(len(x)))  # '?,?'
sql = f"select * from Pretty_Txns where Send_Customer in ({placeholders})"
df = pd.read_sql_query(sql, cnx, params=x)

How can I pass parameters for the IN clause of a SELECT query to retrieve a pandas DataFrame?

You are using the string representation of a tuple to inject into your SQL statement. It works for tuple(["N","M"]) because you get … IN ('N', 'M'). However, it fails for tuple(["N"]) because the result is … IN ('N',) and the trailing comma is not valid SQL syntax. Consider an approach that avoids SQL injection and uses a parameterized query.

First, if you are using pandas with any database other than SQLite you should be using SQLAlchemy. SQLAlchemy will automatically build the required SQL statement when you pass the select() object to pandas .read_sql_query() method:

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine(
    "mssql+pyodbc://scott:tiger^5HHH@mssql_199",
)
tbl_tab = sa.Table("tbl_tab", sa.MetaData(), autoload_with=engine)

# full contents of table:
with engine.begin() as conn:
    print(conn.execute(sa.select(tbl_tab)).fetchall())
    # [(1, 'Alicia'), (2, 'Brandon'), (3, 'Candace')]

# retrieve subset of rows into a DataFrame
name_list = ["Alicia", "Brandon"]
my_select = sa.select(tbl_tab).where(tbl_tab.c.name.in_(name_list))
df = pd.read_sql_query(my_select, engine)
print(df)
"""
   id     name
0   1   Alicia
1   2  Brandon
"""

python list in sql query as parameter

Answers so far have been templating the values into a plain SQL string. That's absolutely fine for integers, but if we wanted to do it for strings we get the escaping issue.

Here's a variant using a parameterised query that would work for both:

placeholder= '?' # For SQLite. See DBAPI paramstyle.
placeholders= ', '.join(placeholder for unused in l)
query= 'SELECT name FROM students WHERE id IN (%s)' % placeholders
cursor.execute(query, l)

Passing a list of lists as a parameter to a select (x,y) in clause

Here are a few ways you could do this.

If you need to execute from the cursor directly, then this approach works. You need to manually create the placeholders to match the length of items, which is not ideal. I found this worked when the engine connected using pymysql or MySQLdb, but not mysql.connector.

items = [(1, 2), (12, 10)]

dbapi_conn = engine.raw_connection()
cursor = dbapi_conn.cursor()

cursor.execute("SELECT * FROM username WHERE (user_id, batch_id) IN (%s, %s)",
               items)
res = cursor.fetchall()
for row in res:
    print(row)
print()
dbapi_conn.close()

If a raw connection method is not a requirement, this is how you might execute a raw SQL query in SQLAlchemy 1.4+. Here we can expand the bind parameters to handle a variable number of values.This approach also does not work with mysql.connector.

with engine.connect() as conn:

    query = sa.text("""SELECT * FROM username WHERE (user_id, batch_id) IN :values""")
    query = query.bindparams(sa.bindparam('values', expanding=True))
    res = conn.execute(query, {'values': items})

    for row in res:
        print(row)
    print()

Finally, this approach is pure SQLAlchemy, using the tuple_() construct. It does not require any special handling for values placeholders, but the tuple_ must be configured. This method is the most portable: it worked with all three connectors that I tried.

metadata = sa.MetaData()
username = sa.Table('username', metadata, autoload_with=engine)

tup = sa.tuple_(sa.column('user_id', sa.Integer), 
                sa.column('batch_id', sa.Integer))

stmt = sa.select(username).where(tup.in_(items))

with engine.connect() as conn:
    res = conn.execute(stmt)
    for row in res:
        print(row)
    print()

All of these methods delegate escaping of values to the DBAPI connector to mitigate SQL injections.

Passing a list in a WHERE clause python mysql

You can use IN keyword for filtering based on list.

party_ids= [4, 3, 1, 2]

def initializegame(party_ids):
    #Get selected party members IDS
    print(party_ids)
    #Obtain Speeds

    ids_string = ','.join(str(id) for id in party_ids)
    mycursor.execute("SELECT startspeed FROM characters WHERE CharID IN ({0})".format(ids_string))

    myspeeds=mycursor.fetchall()
    print(myspeeds)
    print("done")

You can find more information on IN keyword and how it works from this MySQL blog tutorial.

How to pass python list of string to sql query

Join by ',', and enclose everything by ' (don't forget to also replace ' in names with \' to escape them):

"'" + "','".join(name.replace("'", r"\'") for name in name_list) + "'") + "'"

Or you can just use str.format and get the str of the list (minus the [], hence the slicing), using this way will change the quotations surrounding the string, i.e., if the string is 'O\'Hara', it will be transformed to "O'Hara":

query = 'select * from table where name in ({})'.format(str(name_list)[1:-1])

How to pass list values to SQL Select query?

If you want to keep it similar to your original code, you can use string formatting

Python 2

import cx_oracle
List= [123, 234,345,....]
List1 = []
masterQuery = " Select * from table_name where ID in (%s) 
     and date in (Select max(date) from table_name where ID in (%s))"

for j in List:
    cursor1 = db_ora.cursor()
    newQuery = masterQuery % (j, j)
    tb = cursor1.execute(newQuery)
    for i in tb:
        List1.append(i)

Python 3

import cx_oracle
List= [123, 234,345,....]
List1 = []
masterQuery = " Select * from table_name where ID in {} 
     and date in (Select max(date) from table_name where ID in {})"

for j in List:
    cursor1 = db_ora.cursor()
    newQuery = masterQuery.format(j, j)
    tb = cursor1.execute(newQuery)
    for i in tb:
        List1.append(i)

Pass list values to MySQL query using Python

The trick here is the WHERE IN clause, which isn't really amenable to being parameterized. One option generates an IN clause with the exact number of placeholders in your list:

codes = ('AFG', 'IND')
continent = 'Asia'
params = codes + (continent,)
where_in = ','.join(['%s'] * len(codes))
sql = "SELECT * FROM country WHERE Code IN (%s) AND Continent = %s" % (where_in, '%s')
cursor.execute(sql, params)

To see what the above script actually did, lets look at the various parts:

print(where_in)
print(sql)

%s,%s
SELECT * FROM country WHERE Code IN (%s,%s) AND Continent = %s

The trick here is that we actually use a %s placeholder twice, once for the Python string, and a second time for the SQL query string. Also, we bind a single level tuple containing all bound values:

('AFG', 'IND', 'ASIA')

---

Related Topics