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

[Greg Stein]

On Thu, Nov 23, 2000 at 10:48:22AM -0600, Paul DuBois wrote:
>...
> >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"? :-)

Exactly. Solving that problem is practically impossible, so why try? Why
fool people into thinking you can?

[ it can be solved with high-level abstractions where you never even see SQL
  or a cursor or whatever, but *that* abstraction has to build on
  something... thus, the DBAPI :-) ]

> >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?

Correct. Most DB-API implementors simply expose what the database itself
uses, rather than trying to do some mappings.

Consider the case where you want to do something like:

  select table1.*, table2.* from table1, table2 where col1=:1 and col2=:1

This is easy for Oracle since that uses the :1 syntax. I can simply say:

    cursor.execute(stmt, (value,))

But to map the above syntax into ? form, the SQL now looks like:

  select table1.*, table2.* from table1, table2 where col1=:1 and col2=:1

And the code must pass the input parameter *twice*:

    cursor.execute(stmt, (value, value))

Doing mappings like this under the covers is certainly possible, but the
DB-API doesn't ask for modules to do so. It is certainly nicer to avoid the
"?" form since it doesn't support positional or named arguments, but that's
up to the implementor.

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

You are probably looking at an old version. As somebody already stated, the
latest MySQLdb does proper binding, where you don't need to do any quoting.

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

The printf parameter style *is* valid, presuming the DB-API module does the
proper quoting for you. If it doesn't... I agree: it isn't much of a
parameter style :-)

Cheers,
-g

-- 
Greg Stein, http://www.lyra.org/