Negative Integers Escaping
От | Maxim Avanov |
---|---|
Тема | Negative Integers Escaping |
Дата | |
Msg-id | BANLkTin-=DPaUo_BXbDnCdp1Rsg_VDqZmg@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Negative Integers Escaping
|
Список | 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?
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?
В списке psycopg по дате отправления: