New answer to an old question because it seems like some of the underlying functionality has changed since this question/accepted answer were first posted (as alluded to by @vicvicvic in @Gary's answer, but I feel it should be an answer for better visibility).
psycopg2
now supports type adaptation, which allows, among other things, the ability to pass a list into a single parameterized value in the query. This also works in SQLAlchemy, at the very least for raw-SQL-esque queries to a postgresql database (I don't have access to other database types, so I don't know if sqlalchemy
will respect this convention for other databases, but my inclinationcitation needed is that it will work).
some_ids = [1, 2, 3, 4]
query = "SELECT * FROM my_table t WHERE t.id = ANY(:ids);"
conn.execute(sqlalchemy.text(query), ids=some_ids)
## runs just fine
I found that without the wrapper call to sqlalchemy.text
, it gave a ProgrammingError: syntax error at or near ":"
.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…