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?