PDO and database abstraction

[Steve Holden]

Gerhard Häring wrote:
> Luiz Geron wrote:
> 
>>Hi all,
>>I'm testing the PDO wrapper to database modules [1] and I'm wondering
>>how few things like this there are around. 
> 
> 
> Actually there are several Object-Relation Mappers (ORM) for Python, and 
> also a few other attempts to provide a more convenient layer on top of 
> DB-API modules.
> 
> This wiki page has links: 
> http://wiki.python.org/moin/HigherLevelDatabaseProgramming
> 
> 
>>My problem, actually, is the paramstyle of modules.
> 
> 
> That problem exactly what these solutions try to solve, this and 
> multiple SQL dialects and handling types like date etc. for multiple 
> backends.
> 
> 
>>I want to use kinterbasdb in the same code I use
>>cx_oracle, for example, but paramstyle changes from one to other, than
>>I searched for things like this and found nothing really usefull. The
>>problem with PDO is that it was so dificult to find, since a few people
>>seems to use it,  and I haven't yet figured how to change the paramstyle
>>on it, so I want to ask: Do you use a thing like this that you would
>>recommend to me?
> 
> 
> I always wrote my own thin layer on top of DB-API modules and used it to 
> implement a *specific* database interface for my applications. This 
> would then have one or more database backends. Actually never more than 
> two so far.
> 
[ORM stuff ...]

Another way is to actually parameterise your queries for the paramstyle 
according to which packend module you are using. For example, in a 
current development I have queries that are generated like this:

         sql = ("SELECT %s FROM %s WHERE %s=%s" %
                (",".join(f[1] for f in self.FIELDS),
                          table, keyfield, db.pmark))

The "db" module imports one or other of a number of back-end modules, 
and sets "pmark" to the appropriate parameter marker ("%s" or "?" are 
the ones I have used: positionals would be a little trickier, now I 
think of it).

Of course you still have to be careful of SQL syntax variations and 
other backend differences (the usual one being "find the primary key 
value of the last-inserted row on this connection/cursor").

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC                     www.holdenweb.com
PyCon TX 2006                  www.python.org/pycon/