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 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.

Just wanted to share my solution using sqlalchemy and pandas in python 3. Perhaps, one 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)

With the expression API, which based on the comments is what this question is asking for, you can use the in_ method of the relevant column.

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 assumes that user_table and conn have been defined appropriately.

Or maybe use .in_(list), similar to what @Carl has already suggested as

 stmt = select(
 id,
 name
 ).where(
 id.in_(idlist),
 )

Complete code assuming you have the data model in User class:

def fetch_name_ids(engine, idlist):
 # create an empty dataframe
 df = pd.DataFrame()
 try:
 # create session with engine
 session = Session(engine, future=True)
 stmt = select(
 User.id,
 User.name
 ).where(
 User.id.in_(idlist),
 )
 data = session.execute(stmt)
 df = pd.DataFrame(data.all())
 if len(df) > 0:
 df.columns = data.keys()
 else:
 columns = data.keys()
 df = pd.DataFrame(columns=columns)
 except SQLAlchemyError as e:
 error = str(e.__dict__['orig'])
 session.rollback()
 raise error
 else:
 session.commit()
 finally:
 engine.dispose()
 session.close()
 return df

On my postrgres it only works if you use ANY.

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 = ANY(:values); 
""")
query = query.bindparams(values=tuple(values))
df = pd.read_sql(query, engine)

Some people think it is unsafe because of SQL Injection:

But this is only working with Native Query:

This question posted a solution to the select query, unfortunately, it is not working for the update query. Using this solution would help even in the select conditions.

Update Query Solution:

id_list = [1, 2, 3, 4, 5] # in most cases we have an integer list or set
query = 'update myTable set content = 1 WHERE id IN {id_list}'.format(id_list=tuple(id_list))
conn.execute(query)

Note: Use a tuple instead of a list.

• python
• sqlalchemy
• in-clause