[DB-SIG] any 2.0-compliant packages for postgresql?

Andy Dustman andy@dustman.net
Thu, 28 Jun 2001 23:24:11 -0400 (EDT)


On 27 Jun 2001, Federico Di Gregorio wrote:

> Q: psycopg correctly put strings obtained by the Date and Time
> constructors inside quotes  ('') as requested by the api. it should do
> the same (and maybe escape) normal strings? if that's the case don't we
> need a String constructor? how is the module supposed to know when a
> string is real string and need quoting and when i am passing it just as
> convenience but it really is a number? example...
>
> d = {'id':'768', name:'a number'}
> curs.execute("CREATE TABLE test (id int4, name text)")
> curs.execute("INSERT INTO test VALUES (%(id)s, %(name))")
>
> this will obviously produce the  following (wrong) SQL:
>
>     INSERT INTO test VALUES (768, a number)
>
> what i really wanted was 'a number', quoted. but how can the module
> understand that?

Here's what I did for MySQLdb: All of the placeholders in the query should
be %s. The module is responsible for converting all Python types/instances
into a correct SQL literal string. Thus, for your example (corrected
somewhat):

d = {'id':768, name:'a number'}
curs.execute("CREATE TABLE test (id int4, name text)")
curs.execute("INSERT INTO test VALUES (%(id)s, %(name)s)", d)

produces the query:

     INSERT INTO test VALUES (768, 'a number')

The string converter uses MySQL's quoting function
(mysql_real_escape_string()) to escape any special characters, namely
single-quote ('), NUL (\0) and backslash (\).

Of course, MySQLdb allows you to use sequences as well as mappings:

curs.execute("INSERT INTO test VALUES (%s, %s)", (768,'a number'))

-- 
Andy Dustman         PGP: 0xC72F3F1D
    @       .net     http://dustman.net/andy
I'll give spammers one bite of the apple, but they'll
have to guess which bite has the razor blade in it.