SQLAlchemy raw SQL parameter substitution with an IN clause

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.

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.