SQLAlchemy WHERE IN single value (raw SQL)

mysql, python, sqlalchemy

Solution

No, SQL parameters only ever deal with scalar values. You'll have to generate the SQL here; if you need raw SQL, use:

statement = "SELECT * FROM table WHERE `key`='rating' AND uid IN ({})".format(
    ', '.join([':i{}'.format(i) for i in range(len(some_list))]))

my_sess.execute(
        statement, 
        params={'i{}'.format(i): v for i, v in enumerate(some_list)})
    ).fetchall()

e.g. generate enough parameters to hold all values in `some_list` with string formatting, then generate matching parameters to fill them.

Better still would be to use a `literal_column()` object to do all the generating for you:

from sqlalchemy.sql import literal_column

uid_in = literal_column('uid').in_(some_list)
statement = "SELECT * FROM able WHERE `key`='rating' AND {}".format(uid_in)

my_sess.execute(
        statement, 
        params={'uid_{}'.format(i): v for i, v in enumerate(some_list)})
    ).fetchall()

but then you perhaps could just generate the whole statement using the `sqlalchemy.sql.expression module, as this would make supporting multiple database dialects much easier.

Moreover, the `uid_in` object already holds references to the right values for the bind parameters; instead of turning it into a string as we do with the `str.format()` action above, SQLAlchemy would have the actual object plus the associated parameters and you would no longer have to generate the `params` dictionary either.

The following should work:

from sqlalchemy.sql import table, literal_column, select

tbl = table('table')
key_clause = literal_column('key') == 'rating'
uid_clause = literal_column('uid').in_(some_list)
my_sess.execute(select('*', key_clause & uid_clause, [tbl]))

where the `sqlalchemy.sql.select()` takes a column spec (here hard-coded to `*`), a where clause (generated from the two clauses with `&` to generate a SQL `AND` clause) and a list of selectables; here your one `sqlalchemy.sql.table()` value.

Quick demo:

>>> from sqlalchemy.sql import table, literal_column, select
>>> some_list = ['foo', 'bar']
>>> tbl = table('table')
>>> key_clause = literal_column('key') == 'rating'
>>> uid_clause = literal_column('uid').in_(some_list)
>>> print select('*', key_clause & uid_clause, [tbl])
SELECT * 
FROM "table" 
WHERE key = :key_1 AND uid IN (:uid_1, :uid_2)

but the actual object tree generated from all this contains the actual values for the bind parameters too, so `my_sess.execute()` can access these directly.

Problem

I'm having trouble with SQLAlchemy when doing a raw SQL which checks against multiple values. ``` my_sess.execute( "SELECT * FROM table WHERE `key`='rating' AND uid IN :uids", params=dict(uids=some_list) ).fetchall() ``` There are 2 scenarios for this query, one that works and one that doesn't. If `some_list = [1]`, it throws me an SQL error that I have a syntax error near `)`. But if `some_list = [1, 2]`, the query executes successfully. Any reason why this would happen?

Original source