SQLAlchemy raw SQL parameter substitution with an IN clause

Question

I have an SQL statement, SELECT foo FROM bar WHERE id IN %s. I have a list of integers, e.g. [1, 2, 3] and I'd like this to get turned into a SQL statement that looks like SELECT foo FROM bar WHERE id IN (1, 2, 3).

I use SQLAlchemy Core for its connection pooling and for making some inserts with multiple VALUES clauses easier to write and maintain. I prefer to write most of my queries in raw SQL.

To do this in Pyscopg2 I do cursor.execute('SELECT .. WHERE IN %s', (tuple(my_list),)). I can't manage to make this work in SQLAlchemy, however.

engine.execute('SELECT ... WHERE IN %s', tuple(my_list)) raises an exception: TypeError: not all arguments converted during string formatting. This same exception is raised if I pass just the list, not wrapped in a tuple.

If I use named parameters like engine.execute('SELECT ... WHERE id IN :ids', ids=my_list) I get a ProgrammingError exception because SQLAlchemy creates incorrect SQL: SELECT * FROM foo WHERE id IN :ids (it doesn't substitute the :ids value for my variable). This same exception is raised if I pass a tuple.

How can I use a WHERE IN() clause using raw SQL in SQLAlchemy?

Answer 1

This is an unusual format supported only by some DBAPIs, in that it renders a tuple of items as individual SQL expressions, including that it renders the comma and such in between parameters, so a statement like execute("select * from table where value in %s", (somelist, )) expands out at the database level into select * from table where value in (1, 2, 3).

SQLAlchemy is not expecting this format - it already does some inspection of the incoming parameters as it is concerned with routing the parameters into either the DBAPI execute() or executemany() methods, and also accepts a few different styles, and the outcome of this conversion is that the tuple here gets flattened out. You can sneak your tuple past this parsing by adding one more tuple:

from sqlalchemy import create_engine

engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

with engine.connect() as conn:
    trans = conn.begin()


    conn.execute("create table test (data integer)")
    conn.execute(
            "insert into test (data) values (%s)",
            [(1, ), (2, ), (3, ), (4, ), (5, )]
        )

    result = conn.execute(
                "select * from test where data in %s",
                (
                    ((1, 2, 3),),
                )
            )

    print result.fetchall()

The above style only works for some DBAPIs. A quick test confirms it works for psycopg2 and MySQLdb, but not on sqlite3. It has more to do with the underlying system which the DBAPI uses to send bound parameters to the database; psycopg2 and MySQLdb both do Python string interpolation and their own escaping, but systems like cx_oracle will pass the parameters individually to OCI, so this kind of thing wouldn't work in that case.

SQLAlchemy of course offers the in_() operator when using SQL expression constructs but this doesn't apply to straight strings.

Answer 2

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.