[DB-SIG] inserting strings into postgres db using pgdb (escaping quotes automagically)

Andy Todd andy47 at halfcooked.com
Wed Jun 18 17:14:20 EDT 2003


Bryan Lawrence wrote:
> Gidday Andy
> 
> Thanks, problem solved ... well, my code works!
> 
> However,  I couldn't understand pyformat until I found an example which led
> me to the following mess:
> 
> def sqlInsert(self,table,values,data):
>         """ Given two lists: a list of keys and a list of values, insert them 
> into a table (yes it's lists for historical reasons"""
>         if self.dbtype=='postgresql':
>             ddata={}
>             for i in range(len(values)):ddata[values[i]]=data[i]
>             cmd='insert into %s (%s) values (%s)'%(
>                 table,
>                 string.join(values,','),
>                 string.join( map( lambda x:'%s%s%s'%('%(',x,')s'),values),',')
>                 )
> 	    self.cur.execute(cmd,ddata)
> 
> If anyone can point to a more elegant solution, then great, but 
> otherwise thanks for the rapid help, I can live with this.
> 
> Cheers,
> Bryan
> 
[snip]
> 
> 

Yuck. But if it works ...

The only quibble I would have is that the 'string' module is deprecated. 
Given a list called, say, 'myList' you can replace;

 >>> string.join(myList, ',')

With;

 >>> ','.join(myList)

Which you do twice above.

Then a couple of minor stylistic quibbles, feel free to ignore these.

You can do you string substitution more elegantly (I think) by replacing;

'%s%s%s'%'%(',x,')s'

with;

'%('+'%s'%x+')s'

Whilst I was there, I'd also replace the map and lambda with a list 
comprehension, giving us something like;

','.join(['%('+'%s'%x%')s' for x in values])

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/




More information about the DB-SIG mailing list