[DB-SIG] Escaping Placeholders

[Peter L. Buschman]

Denis:

The dbsinglestyle.py is nice and is one of the scripts I am studying in 
designing my own approach to this problem. :-)

I don't think you need a SQL parser for each RDBMS.  Writing cross-platform 
compatible SQL is another
problem entirely (and one for which I am working on a helper module that 
knows about the differences between
SQL dialects.)  Rather, what I am referring to here is a set of conversion 
routines that will enable the creation
of a dbapi-compliant wrapper around existing dbapi drivers such that there 
are no high-level dependencies on
the underlying dbapi driver.  This is key.

Wichert Akkerman has a nice approach in his dhm package 
(http://www.wiggy.net/code/python-dhm) but
not all of the conversions are implemented.  I'm taking a similar approach, 
although there will likely be only
a couple super conversion routines that can handle the sequence-based vs. 
name-based paramstyles.

I want to import one module (mine) and tell it which underlying dbapi 
driver to use.  That driver should act as
an intelligent pass-through, wrapping the underlying exceptions and 
connection method, as well as transparently
translating between differing paramstyles if necessary.  Since the driver 
and database platform are likely to be
determined at runtime or in the install, having a single umbrella interface 
makes it much easier to deal with.

Is this necessarily the best way to do things?  Maybe not, but I have the 
following goals which make it a sensible
approach in my eyes.

1. Eliminate dependency on a specific dbapi 2.0 driver like mxODBC or 
MySQLdb.  Make it easy to prototype on
one and deploy on another, possibly to avoid licensing restrictions.

2. Provide backwards-compatibility for code written using another dbapi 2.0 
driver (possibly with a different paramstyle).

3. Provide an interface that makes my wrapper usable with other dbapi 
wrappers like dtuple without modifications.

Speed is secondary to compatibility with the dbapi spec, but maybe that's 
just a quirk of mine.  I'm writing a lot of this
just for fun because it is challenging and forces me to use features of 
Python I haven't needed before.  In reality, I could
get away with writing a lot of rdbms and driver-dependent code, but I like 
the idea of engineering a reasonable amount
of portability in just in case it might be useful in the future.

--PLB




At 02:28 PM 7/7/2004, Denis S. Otkidach wrote:
>On Tue, 6 Jul 2004, Peter L. Buschman wrote:
>
>PLB> Thanks.  Are you saying there is no explicit escaping of
>PLB> placeholders?  If
>PLB> so, that actually makes my problem
>PLB> somewhat easier as I am working on a set of translation
>PLB> routines to convert
>PLB> from any paramstyle to any other
>
>Is it possible?  In fact you need a SQL parser for each RDBMS, so
>coverters will be database dependent.  The other way is to have
>one "good" style to define queries, that can be easily converted
>to any paramstyle, e.g. look at this one:
>http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/278612
>http://cvs.sourceforge.net/viewcvs.py/ppa/misc/DBSingleStyle.py?rev=HEAD&content-type=text/vnd.viewcvs-markup
>
>This approach is used in some production projects with several
>different databases, but I cannot guaranty it works for every
>RDBMS.
>
>PLB> paramstyle.  Going from ?,?,? to :1,:2:,:3,
>PLB> :param1,:param2,:param3,
>PLB> %s,%s,%s, or :%(param1)s,%(param2)s,%(param3)s
>PLB> is actually quite easy if you don't need to deal with
>PLB> escaped parameters
>PLB> that screw up your search and replace
>PLB> routines.
>
>Anyway, using list of raw chunks and parameters will work faster
>than parsing and constructing query.
>
>--
>Denis S. Otkidach
>http://www.python.ru/      [ru]