Python List in SQL Query as Parameter

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)

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

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)

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.

give parameter(list or array) to in operator - python, sql

The idea is to have a query like this one:

cursor.execute("SELECT ... IN (%s, %s, %s)", (1, 2, 3))

where each %s will be substituted by elements in your list. To construct this string query you can do:

placeholders= ', '.join(['%s']*len(article_ids))  # "%s, %s, %s, ... %s"
query = 'SELECT name FROM table WHERE article_id IN ({})'.format(placeholders)

finally

cursor.execute(query, tuple(article_ids))

---

Related Topics