[DB-SIG] Re: any 2.0-compliant packages for postgresql? (Andy Dustman)

Billy G. Allie Billy G. Allie" <Bill.Allie@mug.org
Fri, 29 Jun 2001 12:34:39 -0400


> Andy Dustman wrote:
> > 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'))

I agree with Andy's statement.  That is how I implemented execute in my
PostgreSQL DB-API 2.0 module, PyPgSQL.  You always use '%s' (or '%(name)s')
as placeholder in the query string and PyPgSQL will apply the correct quoting
based on the type of the parameter.

BTW: PyPgSQL is available on SourceForge (http://www.sf.net/projects/pypgsql)
     I will be announcing a new release of PyPgSQL this weekend.
___________________________________________________________________________
____       | Billy G. Allie    | Domain....: Bill.Allie@mug.org
|  /|      | 7436 Hartwell     | MSN.......: B_G_Allie@email.msn.com
|-/-|----- | Dearborn, MI 48126| 
|/  |LLIE  | (313) 582-1540    |