How to properly construct query with in_ operator

Question

I have an SQL update query with IN statement which is executed via SQLAlchemy. Unfortunately current implementation uses string interpolation, which I'd like to replace with more secure option.

session.execute('''
        UPDATE servers SET
            cpu_cores=st.cpu_cores,
            cpu_mhz=st.cpu_mhz,
            ram_mb=st.ram_mb
        FROM servers
        WHERE
            server.provider_id IN (%s)
''' % ','.join([ ... ]))

Is it possible to replace % operator with in_ method here? Or I should re-implement this query using session.query?

Answer 1

If all you want to do is to make it more secure, you can still interpolate in the param placeholders:

session.execute("""UPDATE ... WHERE server.provider_id IN (%s)""" % ",".join("?" * len(provider_ids)), provider_ids)

The ? is the placeholder, and varies depending on the paramstyle of the DB driver you are using.

Otherwise, the easiest way is to translate this into a session.query or update().where() construct.