CODEWITHSUNDEEP
pythonmysqlsqlalchemy
mattdonders
mattdonders

Reputation: 1356

SQLAlchemy Where Value in List of Columns

Before moving my code to SQLAlchemy, I have a MySQL query that looks for a value in multiple columns (ex - WHERE 'value' in (col1, col2)) which works fine when submitting the query manually to the database.

The use case above is relatively simple, but in the future I may want to run a similar query on more than two columns so just want to get ahead of this problem now.

Currently I have converted my query to the below (which works), but I am trying to find a similar syntax to the one above.

metadata = sqlalchemy.MetaData(mydb, reflect=True)
select_stmt = (tbl_pbp.select(
              ((tbl_pbp.c.home_team == team_abbreviation) | (tbl_pbp.c.away_team == team_abbreviation))
              & (tbl_pbp.c.is_corsi == True)))

When I try the below, I get a syntax error so not sure if it is an issue with the in_ keyword I am trying to use.

metadata = sqlalchemy.MetaData(mydb, reflect=True)
select_stmt = (tbl_pbp.select(
              (team_abbreviation in_(tbl_pbp.c.home_team, tbl_pbp.c.away_team)
              & (tbl_pbp.c.is_corsi == True)))

Any help is greatly appreciated - thanks!

Upvotes: 0

Views: 448

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

In order to operate on a Python value as an SQL literal in SQL Expression Language it must be wrapped with literal(). The resulting construct can then be used to produce the desired IN clause:

literal(team_abbreviation).in_([tbl_pbp.c.home_team, tbl_pbp.c.away_team])

This is equivalent to producing a bunch of equality checks combined with OR, as is noted in the comments. The sole argument to in_() should be a suitable sequence, such as a list, in this case.

Note that in_ here is a method of the SQLAlchemy construct, not the Python operator in, which cannot be overloaded to produce custom constructs due to how it treats the return value of __contains__(): it implicitly converts to a bool:

In [1]: class X:
   ...:     def __contains__(self, item):
   ...:         return 'nope'
   ...:     

In [2]: 1 in X()
Out[2]: True

Upvotes: 2

Related Questions