Negative Integers Escaping

From: Maxim Avanov <maxim(dot)avanov(at)gmail(dot)com>
To: psycopg(at)postgresql(dot)org
Subject: Negative Integers Escaping
Date: 2011-05-27 17:42:51
Message-ID: BANLkTin-=DPaUo_BXbDnCdp1Rsg_VDqZmg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: psycopg

Hello everyone!

There is an unclear behaviour in negative integers escaping when they are
being passed to specific SQL queries.
Here are some examples:

CREATE TABLE testdb (testval integer not null default 0);

>>> import psycopg2 as p
>>> p.__version__
'2.4 (dt dec pq3 ext)'
>>> c = p.connect(...)
>>> cr = c.cursor()
>>> cr.execute("insert into testdb(testval) values(9)")
>>> c.commit()
>>> cr.execute("select testval from testdb")
>>> cr.fetchall()
[(9,)]

>>> # Ok, we know about required parentheses here because we explicitly type
the negative value
>>> cr.execute("update testdb set testval=testval-(-2)")
>>> c.commit()
>>> cr.execute("select testval from testdb")
>>> cr.fetchall()
[(11,)]

>>> # Here we'll get a correct expression but the wrong result caused by the
comment sequence '--'
>>> cr.execute("update testdb set testval=testval-%s", (-2,))
>>> c.commit()
>>> cr.execute("select testval from testdb")
>>> cr.fetchall()
[(11,)]

>>> # So we got to explicitly ident or to frame the placeholder with
parentheses
>>> cr.execute("update testdb set testval=testval - %s", (-2,))
>>> c.commit()
>>> cr.execute("select testval from testdb")
>>> cr.fetchall()
[(13,)]

>>> # The same behaviour with named placeholders
>>> cr.execute("update testdb set testval=testval-%(val)s", {'val':-2})
>>> c.commit()
>>> cr.execute("select testval from testdb")
>>> cr.fetchall()
[(13,)]

I found no strict rules about this case in DBAPI2 specification. So how
negative integers escaping should behave?

Responses

Browse psycopg by date

  From Date Subject
Next Message Oswaldo 2011-05-27 18:29:46 Re: Negative Integers Escaping
Previous Message Federico Di Gregorio 2011-05-20 05:11:42 Re: Named Cursor Basics