SQLAlchemy IN子句

I’m trying to do this query in sqlalchemy

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

I would like to bind the list [123, 456] at execution time.

How about

session.query(MyUserClass).filter(MyUserClass.id.in_((123,456))).all()

edit: Without the ORM, it would be

session.execute(
    select(
        [MyUserTable.c.id, MyUserTable.c.name], 
        MyUserTable.c.id.in_((123, 456))
    )
).fetchall()

takes two parameters, the first one is a list of fields to retrieve, the second one is the where condition. You can access all fields on a table object via the c (or columns) property.

Assuming you use the declarative style (i.e. ORM classes), it is pretty easy:

query = db_session.query(User.id, User.name).filter(User.id.in_([123,456]))
results = query.all()

db_session is your database session here, while User is the ORM class with __tablename__ equal to "users".

An alternative way is using raw SQL mode with SQLAlchemy, I use SQLAlchemy 0.9.8, python 2.7, MySQL 5.X, and MySQL-Python as connector, in this case, a tuple is needed. My code listed below:

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.

With the expression API, which based on the comments is what this question is asking for, you can use the in_ method of the relevant column.

To query

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

use

myList = [123, 456]
select = sqlalchemy.sql.select([user_table.c.id, user_table.c.name], user_table.c.id.in_(myList))
result = conn.execute(select)
for row in result:
    process(row)

This assumes that user_table and conn have been defined appropriately.

Just wanted to share my solution using sqlalchemy and pandas in python 3. Perhaps, one 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)

Just an addition to the answers above.

If you want to execute a SQL with an “IN” statement you could do this:

ids_list = [1,2,3]
query = "SELECT id, name FROM user WHERE id IN %s" 
args = [(ids_list,)] # Don't forget the "comma", to force the tuple
conn.execute(query, args)

Two points:

• There is no need for Parenthesis for the IN statement(like “… IN(%s) “), just put “…IN %s”
• Force the list of your ids to be one element of a tuple. Don’t forget the ” , ” : (ids_list,)

EDIT Watch out that if the length of list is one or zero this will raise an error!