DB-API corner case (psycopg2)

[Paul Boddie]

On 1 Aug, 16:39, "Diez B. Roggisch" <de... at nospam.web.de> wrote:
> Diez B. Roggisch schrieb:
> > Thomas Guettler schrieb:
> >> cursor.execute('''SELECT '%' ''', ()) # Does fail
>
> >> Traceback (most recent call last):
> >>   File "/localhome/modw/tmp/t.py", line 5, in <module>
> >>     cursor.execute('''SELECT '%' ''', ()) # Does fail
> >> IndexError: tuple index out of range
>
> >> Is this a bug in psycopg2?

In my opinion, yes.

> >> How do other PEP 249 implementation behave?

pyPgSQL has a related problem:

  Traceback (most recent call last):
    File "<stdin>", line 1, in <module>
    File "/usr/lib/python2.5/site-packages/pyPgSQL/PgSQL.py", line
3087, in execute
      self.res = self.conn.conn.query(_qstr % parms)
  TypeError: not enough arguments for format string

> > Not a bug. The second execute is the parametrized variant, which simply
> > tries to fetch the parameters from the passed collection. As you give an
> > empty collection, but specify one parameter, the error is quite obvious.

As you point out below, % is not a complete format specification
according to the printf documentation. Therefore, it isn't a parameter
marker. I would accept the argument that it's quite difficult to
handle stray % characters when the string is going to be combined with
a tuple using Python's formatting operator, but I don't see why such
encoding issues should pollute queries written in SQL, which has its
own, completely separate encoding requirements. I suppose this is just
another reason for getting rid of the "format" paramstyle altogether.

> > The first execute takes the SQL-string as literal.
>
> On the second look, it appears that you don't acutally give a valid
> parameter specifier, just a fragment. However, in the same way
>
> "%" % "foo"
>
> yields ValueError - "incomplete format", this is bound to fail - you
> need to escape the % with %%.

What we're seeing here is the library exposing the mechanism by which
it sends values together with the query string to the database system,
not any particular flaw in the submitted query.

Paul

P.S. In order to promote improvements in parameter handling in
database modules, I made the sqlliterals package available; this might
be used to avoid issues such as the one raised in this thread:

http://www.python.org/pypi/sqlliterals