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

[Andy Todd]

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
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/