#96 "where x in %s" failes with single-element tuple

open
nobody
None
5
2007-03-03
2007-03-03
Larry Hastings
No

This:
db.execute("delete from table where index in %s", ((1, 2, 3),) )
works fine.

But this:
db.execute("delete from table where index in %s", ((1,),) )
fails with a syntax error.

It's because PyPgSQL renders the arguments into the command, so the command fed into PostgreSQL becomes
delete from table where index in (1,);
and PostgreSQL's parser doesn't like a one-element tuple like that.

I suspect the fix is: notice when rendering a one-element tuple, and somehow remove or prevent the comma.

Discussion

  • Logged In: YES
    user_id=698599
    Originator: NO

    You might want to check that you're using an up-to-date version of pyPgSQL - I seem to remember this being fixed in 2.4. I use IN queries extensively in my apps, and I haven't had a complaint about this problem for some time.

     
  • Larry Hastings
    Larry Hastings
    2007-05-01

    Logged In: YES
    user_id=364875
    Originator: YES

    I'm using 2.5.1, and I definitely saw the problem. Where in the code does it handle this case?

     
  • Logged In: YES
    user_id=698599
    Originator: NO

    In PgSQL.py, the function _quote() is responsible for this. For example:

    >>> from pyPgSQL import PgSQL
    >>> PgSQL._quote((1,))
    '(1)'