DB-API corner case (psycopg2)

[Thomas Guettler]

Hi,

I discovered this:

import psycopg2
connection=psyc opg2.connect("d bname='...' user='...'")
cursor=connecti on.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]

Re: DB-API corner case (psycopg2)

Thomas Guettler schrieb:

Hi,
>
I discovered this:
>
import psycopg2
connection=psyc opg2.connect("d bname='...' user='...'")
cursor=connecti on.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]

Re: DB-API corner case (psycopg2)

Diez B. Roggisch schrieb:

Thomas Guettler schrieb:

>Hi,
>>
>I discovered this:
>>
>import psycopg2
>connection=psy copg2.connect(" dbname='...' user='...'")
>cursor=connect ion.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]

Re: DB-API corner case (psycopg2)

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

Hi,
>
I discovered this:
>
import psycopg2
connection=psyc opg2.connect("d bname='...' user='...'")
cursor=connecti on.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 ProgrammingErro r 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.D atabase.Adapter ... http://zope.egenix.com/
>>mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

_______________ _______________ _______________ _______________ ____________

:::: Try mxODBC.Zope.DA for Windows,Linux,S olaris,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]

Re: DB-API corner case (psycopg2)

I forgot to mention where I stumbled about this.

Django has a wrapper:

http://code.djangoproject.com/browser/django/trunk/django/db/backends/util.py

def execute(self, sql, params=()):
start = time()
try:
return self.cursor.exe cute(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.exe cute(sql, params)
else:
return self.cursor.exe cute(sql)

What do you think?

Thomas

Thomas Guettler schrieb:

Hi,
>
I discovered this:
>
import psycopg2
connection=psyc opg2.connect("d bname='...' user='...'")
cursor=connecti on.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]

Re: DB-API corner case (psycopg2)

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/browser/django/trunk/django/db/backends/util.py

>
def execute(self, sql, params=()):
start = time()
try:
return self.cursor.exe cute(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.exe cute(sql, params)
else:
return self.cursor.exe cute(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.exe cute(sql)
else:
return self.cursor.exe cute(sql, params)
finally:
...

Thomas
>
Thomas Guettler schrieb:

>Hi,
>>
>I discovered this:
>>
>import psycopg2
>connection=psy copg2.connect(" dbname='...' user='...'")
>cursor=connect ion.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.D atabase.Adapter ... http://zope.egenix.com/
>>mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

_______________ _______________ _______________ _______________ ____________

:::: Try mxODBC.Zope.DA for Windows,Linux,S olaris,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