Python and Databases

[Gerhard Häring]

Geraldo Lopes de souza wrote:
> "Gerhard Häring" <gh at ghaering.de> escreveu:
>>More later, if this thread will continue :)
> 
> Can you plug the DB-API drivers in the application and make it
> multi-database ?

Generally, this is not possible. While the DB-API defines certain 
interfaces, it also gives some choices to module implementors. One issue 
is that there are several possible paramstyles (format, pyformat, qmark, 
named). The other issue that you'll of course know is that apart from 
the most trivial SQL, writing cross-database compatible SQL is hard.

In particular, I'm involved in both pyPgSQL and PySQLite and try to keep 
them compatible. So you should be able to write code that works with 
both. And both support quite some SQL features. I've once done a little 
app that would run on both PostgreSQL and SQLite that way. It'd get 
harder if I wanted to also support MySQL through MySQLdb. But that were 
in theory still possible (not for my app though - it depends on 
triggers), because all three of them support the same paramstyles.

But as soon as you'd try to add, say, Oracle compatibility you're lost 
because the Oracle modules use a different paramstyle.

For bigger projects I use a different approach, anyway:

- create a database backend module/class for each supported database
- use a common superclass for all backends where it makes sense
- put as much code on the database side as possible (stored 
procedures/views)

The less SQL you have in the client application, the better.

HTH,

-- Gerhard