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?
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 |