[DB-SIG] Escaping Placeholders
Peter L. Buschman
plblists at iotk.com
Wed Jul 7 16:31:41 CEST 2004
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
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.
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.
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:
>This approach is used in some production projects with several
>different databases, but I cannot guaranty it works for every
>PLB> paramstyle. Going from ?,?,? to :1,:2:,:3,
>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
>Anyway, using list of raw chunks and parameters will work faster
>than parsing and constructing query.
>Denis S. Otkidach
More information about the DB-SIG