Coder Perfect

SQLAlchemy IN clause


In sqlalchemy, I’m attempting to perform this query.

SELECT id, name FROM user WHERE id IN (123, 456)

At execution time, I’d like to bind the list [123, 456].

Asked by wonzbak

Solution #1

How about


It would be impossible without the ORM.

        [,],, 456))

The first parameter is a list of fields to get, and the second parameter is the where condition. The c (or columns) property gives you access to all fields on a table object.

Answered by Simon

Solution #2

It’s really simple if you utilize the declarative manner (i.e. ORM classes):

query = db_session.query(,[123,456]))
results = query.all()

User is the ORM class with __tablename__ equal to “users” and db session is your database session.

Answered by Xion

Solution #3

Another option is to use SQLAlchemy in raw SQL mode; I’m using SQLAlchemy 0.9.8, Python 2.7, MySQL 5.X, and MySQL-Python as the connector; in this case, a tuple is required. My code is as follows:

id_list = [1, 2, 3, 4, 5] # in most case we have an integer list or set
s = text('SELECT id, content FROM myTable WHERE id IN :id_list')
conn = engine.connect() # get a mysql connection
rs = conn.execute(s, id_list=tuple(id_list)).fetchall()

Hope everything works for you.

Answered by Jet Yang

Solution #4

You can utilize the in_ method of the relevant column with the expression API, which is what this question is asking for based on the remarks.

To query

SELECT id, name FROM user WHERE id in (123,456)


myList = [123, 456]
select =[,],
result = conn.execute(select)
for row in result:

This requires user table and conn have been properly defined.

Answered by Carl

Solution #5

I just wanted to share my approach in Python 3 that uses sqlalchemy and pandas. It’s possible that someone would find it useful.

import sqlalchemy as sa
import pandas as pd
engine = sa.create_engine("postgresql://postgres:my_password@my_host:my_port/my_db")
values = [val1,val2,val3]   
query = sa.text(""" 
                SELECT *
                FROM my_table
                WHERE col1 IN :values; 
query = query.bindparams(values=tuple(values))
df = pd.read_sql(query, engine)

Answered by dmitry

Post is based on