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
edit: Without the ORM, it would be
session.execute( select( [MyUserTable.c.id, MyUserTable.c.name], MyUserTable.c.id.in_((123, 456)) ) ).fetchall()
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
It’s really simple if you utilize the declarative manner (i.e. ORM classes):
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”.
Answered by Xion
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()
I hope everything goes smoothly for you.
Answered by Jet Yang
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.
SELECT id, name FROM user WHERE id in (123,456)
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 requires user table and conn have been properly defined.
Answered by Carl
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 https://stackoverflow.com/questions/8603088/sqlalchemy-in-clause