SQLAlchemy IN clause

python sqlalchemy
wonzbak picture wonzbak · Dec 22, 2011 · Viewed 199.8k times · Source

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.

Answer

Simon picture Simon · Dec 22, 2011

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.

Related questions

SQLAlchemy ORDER BY DESCENDING?

How can I use ORDER BY descending in a SQLAlchemy query like the following? This query works, but returns them in ascending order: query = (model.Session.query(model.Entry) .join(model.ClassificationItem) .join(model.EnumerationValue) .filter_by(id=c.row.…

Read More
How to execute raw SQL in Flask-SQLAlchemy app

How do you execute raw SQL in SQLAlchemy? I have a python web app that runs on flask and interfaces to the database through SQLAlchemy. I need a way to run the raw SQL. The query involves multiple table joins …

Read More
Convert sqlalchemy row object to python dict

Is there a simple way to iterate over column name and value pairs? My version of sqlalchemy is 0.5.6 Here is the sample code where I tried using dict(row), but it throws exception , TypeError: 'User' object is not iterable import …

Read More