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

Andy Todd andy47 at halfcooked.com
Wed Jun 18 18:23:09 EDT 2003

Nathan Clegg wrote:
>>If anyone can point to a more elegant solution, then great, but 
>>otherwise thanks for the rapid help, I can live with this.
> Well I'm not sure why you are forcing your lists into dictionaries.
> How about this:
> def sqlInsert(self,table,values,data):
>     if self.dbtype == 'postgresql':
>         cmd = 'insert into %s (%s) values (%s)' % (
>             table,
>             ', '.join(values),
>             ', '.join(('%s',) * len(values)),
>             )
>         self.cur.execute(cmd, data)

The column names and values are put into a dictionary because pgdb uses 
the 'pyformat' paramstyle. The general form of which is;

 >>> cursor.execute("SELECT column_a FROM table_a WHERE 
column_b=%(param_a)s", {'param_a':'some value'})

This ensures that values are appropriately quoted and parsed by the 
database module and not by you. This means that funny (or just 
unexpected) characters don't cause your query to fail in interesting and 
spectacular ways.

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

More information about the DB-SIG mailing list