[Twisted-Python] problems w/ pyPgSQL and the Enterprise HOWTO

[Sean Riley]

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.

from PgSQL.py:

def execute(self, query, *parms):
	.
	_qstr = query
	.
	self.res = self.conn.conn.query(_qstr % parms)

Sean.

-----Original Message-----
From: twisted-python-admin at twistedmatrix.com
[mailto:twisted-python-admin at twistedmatrix.com]On Behalf Of Andrew
Bennetts
Sent: Sunday, February 23, 2003 10:18 PM
To: twisted-python at twistedmatrix.com
Subject: Re: [Twisted-Python] problems w/ pyPgSQL and the Enterprise
HOWTO


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
the
> >     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.
Yuck.

> > > Is this a problem with pyPgSQL? something else? pyPgSQL is the
> > > _recommended_ module in the enterprise howto, so I didn't expect to
run
> > > 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 :)

-Andrew.


_______________________________________________
Twisted-Python mailing list
Twisted-Python at twistedmatrix.com
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python