[Twisted-Python] problems w/ pyPgSQL and the Enterprise HOWTO
sean at twistedmatrix.com
Mon Feb 24 00:06:18 EST 2003
yep, this really sucks. You cannot do parameterized SQL through pyPgSQL at
all. Inside the driver, pyPgSQL just does a string subsitution for the query
and submits the whole thing to the database.
When you pass parameters to a SQL operation with pyPgSQL is has absolutely
no effect on performance, so you might as well just build the entire SQL
statement youself and pass it in as a single string.
def execute(self, query, *parms):
_qstr = query
self.res = self.conn.conn.query(_qstr % parms)
From: twisted-python-admin at twistedmatrix.com
[mailto:twisted-python-admin at twistedmatrix.com]On Behalf Of Andrew
Sent: Sunday, February 23, 2003 10:18 PM
To: twisted-python at twistedmatrix.com
Subject: Re: [Twisted-Python] problems w/ pyPgSQL and the Enterprise
On Sun, Feb 23, 2003 at 09:20:23PM -0600, Justin Ryan wrote:
> > The howto also says:
> > Also worth noting is that this example assumes that dbmodule uses
> > qmarks paramstyle (see the DB-API specification).
> > This is the cause of the problem; pyPgSQL uses a different paramstyle.
> > Maybe this should be made clearer, by inserting a
> > # Assumes dbmodule.paramstyle == 'qmarks'
> > into the example code, where people will read it.
> Perhaps, However not being familiar with the qmarks paramstyle, I
> noticed this bit but most likely skimmed over it because I didn't make
> the connection.. Learn something new every day..
> Is the qmarks paramstyle a required part of the DB API 2.0 spec?
No, it's even worse than that: DB API 2.0 doesn't specify which paramstyle
to use, it merely states that possible values are "qmark", "numeric",
"named", "format", and "pyformat". So a DB API 2.0-compliant module is free
to implement any of these it feels like, so long as it sets the correct
paramstyle for it. Thus they've defined a spec which allows 5 incompatible
flavours. As far as I can see, the only portable way to write queries is to
format the queries yourself, which means handling correct quoting yourself.
> > > Is this a problem with pyPgSQL? something else? pyPgSQL is the
> > > _recommended_ module in the enterprise howto, so I didn't expect to
> > > into trouble with it..
> > Yeah, that is a good point. The howto should probably use examples that
> > work with pyPgSQL, to save confusion.
> or at least distinguish. I assumed that the howto was using proper DB
> API syntax, and that pyPgSQL is not 100% compliant, but it would be nice
> to see the line drawn. I don't know that I've worked with a fully DB
> API compliant driver at this point ;p
> Thanks for the clarification...
The howto is using proper DB API syntax. The problem is that being DB API
2.0 compliant doesn't help here; qmarks format strings are compliant -- but
incompatible with pyformat format strings, which are also compliant.
Twisted has nothing to do with this mess, and lets you and your db module
sort it out between yourselves :)
Twisted-Python mailing list
Twisted-Python at twistedmatrix.com
More information about the Twisted-Python