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

[Andrew Bennetts]

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.