Re: Negative Integers Escaping

El 27/05/2011 19:42, Maxim Avanov escribió:
> 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?
>

When you do:
cr.execute("update testdb set testval=testval-%s", (-2,))

Postgresql receive:
update testdb set testval=testval--2

The double dash is treated as begin sql comment and only execute:

update testdb set testval=testval

Is a good rule to always put spaces between operators

Regards

--
Oswaldo Hernández