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

[Bryan Lawrence]

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

On Wednesday 18 June 2003 14:06, Andy Todd wrote:
> Bryan Lawrence wrote:
> > Hi Folks
> >
> > I'm just playing at a common db core module for an application. It works
> > ok with gadfly and mysql, and I'm trying to extend it to postgresql.
> >
> > I'm using the pgdb module ...
> >
> > At the moment (mysql) I do inserts with something like
> >
> > data=('a',"b's")
> > cmd="insert into table blah (x,y) values (%s%s)"
> > self.cursor.execute(cmd,data)
> > (replacing the %s with ? for gadfly).
> > This breaks with postgres ...
> >
> > I tried something like
> > cmd='insert into table blah (x,y) values (string.join(
> > map(lambda x: repr(x),data),','))
> > but that breaks with
> > ERROR: Attribute ... not found
> >
> > Clearly I can muck around parsing strings and escaping things, but
> > that seems remarkably clumsy, and I can't believe I'm the only person
> > with this problem. I've had a google and rummaged in the manuals, but
> > obviously didn't turn over the right leaf ... how does one do this with
> > postgres?
> >
> > I'm not on the mailing list, so if you can help, could you please reply
> > directly? It's not an easy thing to search for in the archive, so
> > apologies if this is a much rehearsed problem.
> >
> > Thanks
> > Bryan
>
> I'm not a PostgreSQL expert so you may want to wait for one of them to
> supply the answer to your questions. Looking at the PostgreSQL web site
> there is no explicit documentation of pgdb other than to say that it is
> a Python interface that conforms to the DB-API version 2.0. In which
> case I suggest a quick look at;
>
> http://www.python.org/topics/database/DatabaseAPI-2.0.html
>
> Specifically the module interface called 'paramstyle'. Gadfly uses the
> 'qmark' style and MySQLdb uses a specialised version of the 'format'
> style. It is entirely possible that pgdb uses an alternative. To find
> out, from a python prompt type the following;
>
>  >>> import pgdb
>  >>> pgdb.paramstyle
>
> and a string should be displayed which tells you the paramstyle of your
> module.
>
> If I'm completely wide of the mark and pgdb is not DB-API 2.0 compatible
> (for instance it doesn't support the paramstyle module interface) there
> are a number of alternative PostgreSQL interface modules that may be
> better for you. They are listed on this web page;
>
> http://www.python.org/topics/database/modules.html
>
> Regards,
> Andy

-- 
Bryan Lawrence, Head NCAS/British Atmospheric Data Centre
web: www.badc.nerc.ac.uk  phone: +44 1235 445012
CLRC: Rutherford Appleton Laboratory, Chilton, Didcot, OX110QX, UK