[DB-SIG] Why the varying paramstyles in DB-API?

[Paul DuBois]

>On Mon, Nov 20, 2000 at 11:04:11PM -0600, Paul DuBois wrote:
>>  Why are there so many different paramstyles for DB-API?
>>  Given that one benefit of the API is to provide a uniform
>>  access method across database engines, doesn't the non-uniformity
>>  of parameter styles diminish the extent to which that benefit
>>  can be realized?
>
>The idea is to provide a *similar* access method across engines. If you use
>DBAPI against one database, then you know how to use it against others. But
>that doesn't mean it is used exactly the same.
>
>There are way too many differences between databases to create any kind of
>system that can encompass all of them. ODBC attempts to do so, but even that
>has a bazillion runtime query mechanisms to find out *how* to run against
>the database. And even then, you're probably *still* not portable. For
>example: cursors aren't portable, certain SQL functions, some formatting
>stuff, or even SQL itself.
>
>The DBAPI helps you transition, but it doesn't create a totally safe "plug
>in whatever you want" system.

And thus, DB-API developers aren't led, as JDBC developers are sometimes
led (falsely) to make claims such as that "you can substitute an entirely
different databaseinto your application without so much as a thought about
compatibility"? :-)

>
>Specifically, in this case, the formatting codes are database-specific. The
>DBAPI does not attempt to enforce a particular form, which would simply
>serve to make every DBAPI developer have to implement a lot of glue if that
>form did not correspond to their database's form. Some databases use '?',
>others use %1, others something else. Those changes are reflected up to the
>DBAPI client.

Thanks.  When I wrote my message, I was thinking of Perl DBI.  Some of the
drivers do understand things like :1, :2, etc., but the ? placeholder
character seems to be fairly standard (i.e., portable) across drivers.
For several of the drivers, ? is provided as an emulation of some other
native mechanism, or to compensate for the engine having no placeholder
mechanism at all.

So for DB-API, is it true to say that providing a portable placeholder
machanism (emulating it in the drivers if necessary) simply wasn't a
design goal?


My question springs out of examination of the MySQLdb driver, which uses
the format paramstyle.  As far as I can tell, that parameter style gives you
printf-style formatting, nothing more, nothing less.  It doesn't add quotes
around substituted values, doesn't escape special characters, doesn't turn
None into NULL in the resulting query string, etc.  Using format specifiers
that way is something Python does anyway, so I was wondering why that was
even called a parameter style at all.  It's kind of like calling printf
in Perl a parameter style.

Thanks for your message.