SQLAlchemy IN clause SQLAlchemy IN clause python python

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.


matomo