SQLAlchemy IN clause

Question

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

User · Answer

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

User · Answer

Just an addition to the answers above    If you want to execute a SQL with an  IN  statement you could do this     ids list    1 2 3  query    SELECT id  name FROM user WHERE id IN  s   args     ids list      Don t forget the  comma   to force the tuple conn execute query  args    Two points     There is no need for Parenthesis for the IN statement like      IN  s      just put     IN  s   Force the list of your ids to be one element of a tuple  Don t forget the          ids list      EDIT Watch out that if the length of list is one or zero this will raise an error

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[python] SQLAlchemy IN clause

Examples related to python

Examples related to sqlalchemy

Examples related to in-clause