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