[DB-SIG] inserting strings into postgres db using pgdb (escaping
quotes automagically)
Andy Todd
andy47 at halfcooked.com
Wed Jun 18 15:06:32 EDT 2003
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/
More information about the DB-SIG
mailing list