DB-API corner case (psycopg2)

[Thomas Guettler]

Hi,

I discovered this:

import psycopg2
connection=psycopg2.connect("dbname='...' user='...'")
cursor=connection.cursor()
cursor.execute('''SELECT '%' ''') # Does not fail
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?

How do other PEP 249 implementation behave?

Regards,
Thomas

--
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de

[Diez B. Roggisch]

Thomas Guettler schrieb:

Hi,

I discovered this:

import psycopg2
connection=psycopg2.connect("dbname='...' user='...'")
cursor=connection.cursor()
cursor.execute('''SELECT '%' ''') # Does not fail
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?

How do other PEP 249 implementation behave?

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.

The first execute takes the SQL-string as literal.

Diez

[Diez B. Roggisch]

Diez B. Roggisch schrieb:

Thomas Guettler schrieb:

>Hi,

I discovered this:

import psycopg2
connection=psycopg2.connect("dbname='...' user='...'")
cursor=connection.cursor()
cursor.execute('''SELECT '%' ''') # Does not fail
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?

How do other PEP 249 implementation behave?

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.

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 %%.

Diez

[M.-A. Lemburg]

On 2008-08-01 15:44, Thomas Guettler wrote:

Hi,

I discovered this:

import psycopg2
connection=psycopg2.connect("dbname='...' user='...'")
cursor=connection.cursor()
cursor.execute('''SELECT '%' ''') # Does not fail
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?

How do other PEP 249 implementation behave?

This depends a lot on the database backend. Some might
complain about the use of an incomplete binding parameter
marker '%' and issue a ProgrammingError even if you don't
pass in any binding parameters (to inform you of the possible
bug in your application).

However, "'%'" may also be perfectly valid SQL when used without
binding parameters, so it's not clear whether this case should
always raise an exception.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Aug 01 2008)

>>Python/Zope Consulting and Support ... http://www.egenix.com/
mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

__________________________________________________ ______________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611

[Thomas Guettler]

I forgot to mention where I stumbled about this.

Django has a wrapper:
http://code.djangoproject.com/browse...ckends/util.py

def execute(self, sql, params=()):
start = time()
try:
return self.cursor.execute(sql, params)
finally:

Most people don't have a percent sign in the variable sql.

I guess
cursor.execute(sql, None)
is not portable for all database backends.

I guess this should be the best solution:
if params:
return self.cursor.execute(sql, params)
else:
return self.cursor.execute(sql)

What do you think?

Thomas

Thomas Guettler schrieb:

Hi,

I discovered this:

import psycopg2
connection=psycopg2.connect("dbname='...' user='...'")
cursor=connection.cursor()
cursor.execute('''SELECT '%' ''') # Does not fail
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?

How do other PEP 249 implementation behave?

Regards,
Thomas

[M.-A. Lemburg]

On 2008-08-01 20:38, Thomas Guettler wrote:

I forgot to mention where I stumbled about this.

Django has a wrapper:
http://code.djangoproject.com/browse...ckends/util.py

def execute(self, sql, params=()):
start = time()
try:
return self.cursor.execute(sql, params)
finally:

Most people don't have a percent sign in the variable sql.

I guess
cursor.execute(sql, None)
is not portable for all database backends.

I guess this should be the best solution:
if params:
return self.cursor.execute(sql, params)
else:
return self.cursor.execute(sql)

What do you think?

Not good enough... you should use this:

def execute(self, sql, params=None):
start = time()
try:
if params is None:
return self.cursor.execute(sql)
else:
return self.cursor.execute(sql, params)
finally:
...

Thomas

Thomas Guettler schrieb:

>Hi,

I discovered this:

import psycopg2
connection=psycopg2.connect("dbname='...' user='...'")
cursor=connection.cursor()
cursor.execute('''SELECT '%' ''') # Does not fail
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?

How do other PEP 249 implementation behave?

Regards,
Thomas

--
http://mail.python.org/mailman/listinfo/python-list

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Aug 01 2008)

>>Python/Zope Consulting and Support ... http://www.egenix.com/
mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

__________________________________________________ ______________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611