[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

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, ',')


 >>> ','.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;




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])

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

More information about the DB-SIG mailing list