1. Michael Bayer
  2. sqlalchemy

Issues

Issue #3018 invalid

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

maxdamage
created an issue
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.

Comments (3)

  1. Michael Bayer repo owner

    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.

  2. Log in to comment