Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

Problem with %, (, ) in names of parameters in cursor.execute

#167

Submitted by: Basil Peace grv87@yandex.ru

Documentations says that 'in order to include a literal % in the query you can use the %% string'. However, this doesn't work for names of parameters when dictionary-style query string is used.

I propose to add escaping for names of parameters. '%' and ')' (and probably '(') should be escaped. For example, using '%' escape symbol, the following commands would be correct:

cursor.execute('INSERT INTO test1 ("50%%") VALUES (%(50%%)s)', {'50%': 123}) cursor.execute('INSERT INTO test3 ("max(price)") VALUES (%(max(price%))s)', {'max(price)': 123})

Currently %%-escaping for parameters doesn't work:

cursor.execute('INSERT INTO test1 ("50%%") VALUES (%(50%%)s)', {'50%': 123}) psycopg2.ProgrammingError: incomplete placeholder: '%(' without ')'

And with braces there is something wonderful:

cursor.execute('INSERT INTO test3("max(price)") VALUES (%(max(price))s)', {'max(price)': 123}) KeyError: 'max(price'
cursor.execute('INSERT INTO test3("max(price)") VALUES (%(max(price))s)', {'max(price': 123}) KeyError: 'max(price)'
cursor.execute('INSERT INTO test3("max(price)") VALUES (%(max(price))s)', {'max(price)': 123, 'max(price': 123}) KeyError: 'max(price)'

Reported by Psycopg website · July 11th, 2013 @ 05:39 AM

State: resolved
Milestone: none
Assigned to: nobody

Activity

  1. Daniele Varrazzo
    Daniele Varrazzo
    • State changed from new to open

    I don't know about %, but ( and ) don't work in Python either. % works in Python but not in psycopg: I'll have to check why.

    I think the only fix you may end up with is to just have % accepted inside the parenthesis, with no need to escape, while ( and `) won't be accepted.

    In [4]: print "foo %(50%)s baz" % {'50%': 'bar'}
foo bar baz

In [5]: cur.mogrify("foo %(50%)s baz", {'50%': 'bar'})
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
/home/piro/dev/psycopg2/build/lib.2.7/<ipython-input-5-71c53d1f4f43> in <module>()
----> 1 cur.mogrify("foo %(50%)s baz", {'50%': 'bar'})

ProgrammingError: incomplete placeholder: '%(' without ')'

    I don't want to diverge from what Python's % accepts and complicate something as easy as the name of the parameters with escaping rules.

    July 12th, 2013 @ 08:31 PM

  2. Daniele Varrazzo
    Daniele Varrazzo
    • State changed from open to resolved

    Issue solved by declaring in the docs that %, (, ) are not supported.

    October 16th, 2013 @ 02:37 PM