[DB-SIG] inserting strings into postgres db using pgdb (escaping
b.n.lawrence at rl.ac.uk
Wed Jun 18 16:04:21 EDT 2003
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:
""" Given two lists: a list of keys and a list of values, insert them
into a table (yes it's lists for historical reasons"""
for i in range(len(values)):ddata[values[i]]=data[i]
cmd='insert into %s (%s) values (%s)'%(
string.join( map( lambda x:'%s%s%s'%('%(',x,')s'),values),',')
If anyone can point to a more elegant solution, then great, but
otherwise thanks for the rapid help, I can live with this.
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;
> 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
> 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;
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
More information about the DB-SIG