0

I am trying to bind a list to a parameter in a raw SQL query in sqlalchemy. This post suggests a great way to do so with psycopg2 as below. 

some_ids = [1, 2, 3, 4]
query = "SELECT * FROM my_table WHERE id = ANY(:ids);"
engine.execute(sqlalchemy.sql.text(query), ids=some_ids)

However, this does not seems to work for my environment for SQL Server with pyodbc. Only one "?" gets added instead of 4.

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) 
('Invalid parameter type. param-index=0 param-type=tuple', 'HY105') 
[SQL: 'SELECT * FROM my_table WHERE id = ANY(?);'] [parameters: ((1, 2, 3, 4),)]

Is there any way to make this work? I would like to avoid manually creating placeholders if possible.

sqlalchemy version=1.0.13, pyodbc version=4.0.16

E.K.
  • 4,179
  • 8
  • 30
  • 50

1 Answers1

0

This seems to be working for me:

from sqlalchemy import create_engine
from sqlalchemy.sql import table, column, select, literal_column
engine = create_engine('mssql+pyodbc://SQLmyDb')
cnxn = engine.connect()
some_ids = [2, 3]
sql = select([literal_column('*')]).where(column('ID').in_(some_ids)).select_from(table('People'))
print(sql)
print('')
params = {":ID_" + str(x+1): some_ids[x] for x in range(len(some_ids))}
rows = cnxn.execute(sql, params).fetchall()
print(rows)

which prints the generated SQL statement, followed by the results of the query

SELECT * 
FROM "People" 
WHERE "ID" IN (:ID_1, :ID_2)

[(2, 'Anne', 'Elk'), (3, 'Gord', 'Thompson')]
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Yes this seems to work for me as well. But the raw SQL query above does not work. Any idea why? – E.K. Aug 08 '17 at 21:33
  • You could use [`some_ids = [bindparam('id_1'), bindparam('id_2')]`](http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.bindparam) to have explicit control over the parameter names, if wanting to use `executemany()`. In your example case you'd not even need to pass the manually formed `params` to `execute()`, since they're already bound in your statement. – Ilja Everilä Aug 09 '17 at 06:36
  • 1
    hmm I see. I guess that is one way. But if possible, I would like to write a raw SQL query and pass a list as a param. – E.K. Aug 09 '17 at 15:34