Constructing an SQL query with a list of variable length with sqlalchemy and pandas in python

Question

I would like to construct a SQL statement with an IN operator that works on a list of arbitrary length. I am working with python, pandas, and sqlalchemy.

For example, If the query I'd like to execute is

SELECT * FROM users WHERE age IN (25, 26, 27)"

I have tried:

import pandas as pd
from sqlalchemy.sql import text

ages = (25, 26, 27)
query = text("SELECT * FROM users WHERE age IN (:x)")
df = pd.read_sql_query(query, conn, params={"x":ages})

But this results in errors. How can I construct the query properly? I would like the solution to work in the case that there is only one value in the list for the IN operator, i.e. ages = tuple(25).

You may want to try this. PS it didn't work for me for SQLite and currently i can't check it against other SQL DBs... — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Nov 4, 2016 at 22:47

Parfait · Accepted Answer · 2016-11-04 22:41:23Z

1

Consider a dynamic SQL string build for the placeholders and a dynamic dictionary build using dictionary comprehension for the parameters. Below assumes your RDMS is SQLite with the colon named parameters:

import pandas as pd
from sqlalchemy.sql import text

ages = (25, 26, 27)
placeholders = ', '.join([':param'+str(i) for i in range(len(ages))])
paramdict = {'param'+str(i): ages[i] for i in range(len(ages))}

query = text("SELECT * FROM users WHERE age IN ({})".format(placeholders))
# SELECT * FROM users WHERE age IN (:param0, :param1, :param2)

df = pd.read_sql_query(query, conn, params=paramdict)

answered Nov 4, 2016 at 22:41

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

Comments

mdeceglie · Accepted Answer · 2016-11-04 20:54:55Z

0

One solution is to use python's string join() method.

The example would read:

import pandas as pd
from sqlalchemy.sql import text

ages = (25, 26, 27)
ages = (str(n) for n in ages)
ages = ','.join(ages)
query = text("SELECT * FROM users WHERE age IN (:x)")
df = pd.read_sql_query(query, conn, params={"x":ages})

answered Nov 4, 2016 at 20:54

mdeceglie

1591 silver badge8 bronze badges

1 Comment

univerio Over a year ago

That doesn't seem right. It results in WHERE age IN ('25, 26, 27') instead of WHERE age IN (25, 26, 27).

Collectives™ on Stack Overflow

Constructing an SQL query with a list of variable length with sqlalchemy and pandas in python

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related