Problem
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
Solution #1
How about
session.query(MyUserClass).filter(MyUserClass.id.in_((123,456))).all()
It would be impossible without the ORM.
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
Solution #2
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()
User is the ORM class with __tablename__ equal to “users” and db session is your database session.
Answered by Xion
Solution #3
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()
Hope everything works for you.
Answered by Jet Yang
Solution #4
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.
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 requires user table and conn have been properly defined.
Answered by Carl
Solution #5
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