CODEWITHSUNDEEP
pythonsqlalchemy
jim jarnac
jim jarnac

Reputation: 5152

Python, sqlalchemy, Select with WHERE IN clause

Using Python and sqlalchemy:

How can I achieve the following MySQL statement with a WHERE ... IN clause using a tuple?

SELECT * FROM mytable WHERE (symbol, volume) IN (('ES', 238 ),('GB', 500 ),('OH', 800 ));

in sqlalchemy core (ie not the ORM version)

I looked in the documentation and generally on SO/google, this is nowhere to be found...

Upvotes: 5

Views: 23651

Answers (3)

dangelsaurus
dangelsaurus

Reputation: 7562

from sqlalchemy import tuple_
stmt = select([mytable]).where(tuple_(mytable.c.symbol,mytable.c.volume)
                               .in_([('ES',238),('GB',500),('OH',800)]))

Upvotes: 0

Brailo
Brailo

Reputation: 350

Try reading this part of the documentation -
http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.in_

Upvotes: 1

Forhadul Islam
Forhadul Islam

Reputation: 1237

Assuming you use the declarative style (i.e. ORM classes). For your table there should be a orm class. I am assuming it as MyTable. Then the code will be like this:

keys = [
    ('ES', 238 ),('GB', 500 ),('OH', 800 )
]

select([
    MyTable.url
]).select_from(
    MyTable
).where(
    tuple_(MyTable.symbol, MyTable.volume).in_(keys)
)

Upvotes: 7

Related Questions