DBI spec and parameter passing

[M.-A. Lemburg]

Skip Montanaro wrote:
> 
>     Marc> Note that one should always use binding parameters if possible:
>     Marc> the driver or interface usually knows better how to encode the
>     Marc> data according to the DBs specs and it's also much easier to write
>     Marc> and more portable.
> 
> In my local case (MySQLdb), I use "%s" and your indication is that ODBC uses
> "?", which is fine.  Other database modules apparently use named
> interpolation (e.g., "%(city)s").  Is there a database-independent way of
> building the basic strings other than switching on the value of
> db.paramstyle:
> 
>     db = somethingDBIcompliant(...)
>     c = db.cursor()
>     if db.paramstyle == "qmark":
>         c.execute("sql statement with ? embedded", params)
>     elif db.paramstyle == "numeric":
>         c.execute("sql statement with :1 embedded", params)
>     elif db.paramstyle == "format":
>         c.execute("sql statement with %s embedded", params)
>     ...
> 
> or is that much portability worth the effort?  My code stops before this
> point in its attempts to be independent of a particular database module.

I would suggest to add a DB abstraction layer on top of the
DB module interface -- even with ODBC which is supposed to
provide much of this abstraction you often run into situations
where DBs use different SQL syntax, different column type
names etc.

If you really want your code to DB independent,
you should consider coding your particular needs into a
Python abstration layer and then write backends for each DB
you target. 

Not sure whether it's worth it though... some
applications do just fine when coded for a single DB target,
e.g. large accounting systems can well assume to have an
Oracle or IBM DB available, small apps can do well with MySQL
(which is not free in this case, BTW).

-- 
Marc-Andre Lemburg
______________________________________________________________________
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/