SQLAlchemy IN clause
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()
select()
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 sets = text('SELECT id, content FROM myTable WHERE id IN :id_list')conn = engine.connect() # get a mysql connectionrs = conn.execute(s, id_list=tuple(id_list)).fetchall()
Hope everything works for you.