Python and Databases

[Ian Bicking]

On Mon, 2003-05-19 at 20:19, Greg Ewing (using news.cis.dfn.de) wrote:
> Gerhard Häring wrote:
> > While the DB-API defines certain 
> > interfaces, it also gives some choices to module implementors. One issue 
> > is that there are several possible paramstyles (format, pyformat, qmark, 
> > named).
> 
> This is my biggest gripe with the DB-API. It seems totally
> insane to me -- what's the use of having a so-called "standard"
> API in which something so fundamental is not standardised?
> It defeats much of the purpose.

It's a frequent topic on the DB-SIG mailing list.  The problem boils
down to the underlying DB client libraries using a particular format
style, and it's hard to map between styles -- you have to parse the SQL
to tell if the marker is inside a string or bare.  

That would be fine, really, if they used something like pyformat, where
all non-marker %'s must be quoted (assuming pyformat works like normal
substitution).  But I think other people are too attached to using
things like ? (unquoted in strings)... not that I've followed all those
discussions very closely.  There have been some other implementable
suggestions (like "SELECT * FROM something WHERE col = ",
val_to_substitution, "AND yada yada"), but they please people even less.

Personally I avoid it by doing all the substitutions myself, and not
using the database for it.  There's no standard way to map Python
objects to their SQL representations anyway, so that makes substitution
doubly unportable -- when I do my own quoting and string substitution,
it's not a problem.  I don't know what the performance effect of this
is, but I can't imagine it's that bad, since I don't do that many
repeated queries where prepared statements would help.

  Ian