Python, sqlalchemy, Select with WHERE IN clause

Question

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

Forhadul Islam · Accepted Answer · 2017-04-15 00:25:27Z

7

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

answered Apr 15, 2017 at 0:25

Forhadul Islam

1,24711 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jim jarnac Over a year ago

@A H M Forhadul Islam This is not useful as it deters other users from posting the right answer. I clearly stated in my question (in bold) that I am not interested in the orm. You might leave your answer though, as it might be helpful to others.

Brailo · Accepted Answer · 2017-04-24 09:42:31Z

1

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

edited Apr 24, 2017 at 9:42

answered Apr 24, 2017 at 9:27

Brailo

3502 silver badges5 bronze badges

Comments

dangelsaurus · Accepted Answer · 2017-07-06 02:53:16Z

0

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

answered Jul 6, 2017 at 2:53

dangelsaurus

7,6228 gold badges58 silver badges77 bronze badges

Collectives™ on Stack Overflow

Python, sqlalchemy, Select with WHERE IN clause

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related