Bad parameter conversion on execute when it is list with one element

[Marcin Marzec]

some_list = [1, ] # list with one element

query = "SELECT * FROM some_table WHERE id IN :param_list "

params = { "param_list": some_list }

result = db_session.execute(expression.text(query), params)

query will look like:

SELECT * FROM some_table WHERE id IN ('1',)

When we want to pass list as a parameter to query, and the list has one element there is added coma at the end that breaks the query. When list has more elements there is no additional coma at the end so the query is fine.

Tested on 0.7.4. Didn't check newer versions.

[Mike Bayer]

the format of a SQL IN clause is:

where col IN (v1, v2, v3, ...)

your string is missing parenthesis and also is making the mistake that it assumes a list will be expanded somehow to modify the text of the SQL. No such capability exists. For auto-expansion of a list into individual components within IN, use the core expression language, see below:

from sqlalchemy import create_engine, select, text
from sqlalchemy.sql import column, table

some_list = [1]

e = create_engine("sqlite://", echo=True)
e.execute("create table some_table (id integer)")
e.execute("insert into some_table (id) values (1)")

# correct
q1 = "SELECT * FROM some_table WHERE id IN (:param_list)"
print e.execute(q1, {"param_list": some_list[0]}).fetchall()

# correct
q1 = "SELECT * FROM some_table WHERE id IN (:param_list)"
print e.execute(text(q1), {"param_list": some_list[0]}).fetchall()

# correct
t1 = table("some_table", column('id'))
q2 = select([t1]).where(t1.c.id.in_(some_list))
print e.execute(q2, {"param_list": some_list}).fetchall()

# incorrect
q1 = "SELECT * FROM some_table WHERE id IN :param_list"
print e.execute(text(q1), {"param_list": some_list}).fetchall()

we do seem to be short on documentation for in_() however, that should be fixed.

[Marcin Marzec]

Thanks for clarification.

[Mike Bayer]

Removing milestone: 0.7.xx (automated comment)