Python list in SQL query as parameter

python

parameterization

sql-injection

sqlalchemy

byAnton Shumikhin·Oct 27, 2024

Running a SQL query based on a Python list? Use parameterized queries. In psycopg2 for PostgreSQL, put your list in a tuple:

query = "SELECT * FROM table WHERE column = ANY(%s);"
cursor.execute(query, ([1, 2, 3],)) # Switch numbers to your parameters

In SQLite, use the ? placeholder and expand the list in the query:

params = '?' + ',?' * (len(list) - 1) # SQLite's way of saying "Hey, I am expecting multiple ?'s here"
query = f"SELECT * FROM table WHERE column IN ({params});"
cursor.execute(query, list) # This makes SQL and Python talk like besties!

Warning! Avoid SQL injection. Don't concatenate list items directly into your query. Play safe with your database library's parameterization.

Parameterization differs per SQL engine

Every SQL Engine has its own language (annoying, right?). Whether MySQL, SQL Server, or any other, you must adapt to their spoken language (placeholders).

MySQL with pymysql

query = "SELECT * FROM table WHERE column IN ({});".format(', '.join(['%s']*len(your_list))) # MySQL is hipster and uses %s 
cursor.execute(query, tuple(your_list)) # but hey, we're all friends with tuples

SQL Server with pyodbc

params = ', '.join('?' for _ in your_list)  # SQL Server also uses ? but sometimes, it's good to be explicit
query = f"SELECT * FROM table WHERE column IN ({params});"
cursor.execute(query, tuple(your_list)) # probably, SQL Server is just old-school and does not trust %s

Each of these examples keeps you safe from the bad world of SQL injection. Just like how a seatbelt keeps you safe in a rollercoaster ride!

Invoking powers with sqlalchemy

Confused with raw SQL? No worries! SQLAlchemy is your antidote. Let's see:

Text is mightier than sword

With bind parameters:

from sqlalchemy import text
stmt = text("SELECT * FROM table WHERE column IN :param").bindparams(param=tuple(your_list))
result = connection.execute(stmt) # Let SQLAlchemy do the magic!

Controlling with a list

Using ORM sessions, the mighty in_ operator comes to action:

from sqlalchemy.orm import session
result = session.query(MyTable).filter(MyTable.column.in_(your_list)).all() # SQLAlchemy turns you into a SQL Wizard!

Beware of pitfalls!

Large lists? No problem!

Working with a large number of items in a list, you might hit database's parameter limits. So, don't let that surprise you. Write your logic to split large lists into manageable chunks. Like how you would eat a large pizza, one slice at a time.

No data? No issue!

Empty lists might lead to a failed quest or unexpected results. Handle edge cases well:

if not your_list:
    # Handle the 'no data, no problem' case

Like they say, better safe than sorry!

Different strokes for different folks

Double-check that the Python list data type matches the SQL column. You can't mix apples and oranges, right?

Optimise for success

Faster results with indexing

Performance is key! Make sure your database column is indexed. It's the difference between finding your favourite shirt in an organised versus disorganised closet.