[DB-SIG] In praise of pyformat

[Carsten Haese]

On Sun, 2007-08-12 at 18:12 -0400, Mike Meyer wrote:
> You're right, in that the existing mechanisms *can* deal with the
> issues. However, two of the points that comes up over and over again
> here is "use parameters, don't build the query strings yourself" and
> "we would rather the module authors do the work than the users". I'm
> trying to figure out how *either* of those is miscible with "Just use
> pythons string substitutions for table/column names", much less *both*
> of them.

You're right, not having a cross-database mechanism for building queries
out of variable parts does not quite jibe with those two principles.
Keep in mind, however, that of the two problems of filling variable
values into a query and filling variable table/column names into a query
are two very different problems, and in most applications, the first one
is more common than the second one by a factor of about a million to one
or so.

Let's say hypothetically we add something to the DB-API for plugging
table names and column names into a query. Older versions of Informix
restrict identifiers to 18 characters. What is the DB-API supposed to do
if you try to plug in a table name that's longer? Truncate the name?
Raise an exception? Something else?

Even if we find a solution that fits every use case, simply having a
mechanism for plugging table and column names into a query is not enough
for a general framework for writing cross-database applications. What
about syntax differences in "limit" queries? What about different names
for built-in functions, etc. Any solution to this problem will either be
incomplete or such a behemoth that it will be next to impossible to
implement a compliant API module.

The sheer size of the problem of database abstraction and the fact that
there is no one solution that fits all is the reason why there are many
different solutions such as SQLObject, SQLAlchemy, Dabo, Django, etc
already in the wild. It's also the reason why I doubt that DB-API is
going to grow such a query construction toolkit layer any time soon.

-- 
Carsten Haese
http://informixdb.sourceforge.net