[DB-SIG] Database Independent SQL

[Chris Cogdon]

On Sunday, Sep 8, 2002, at 18:44 US/Pacific, Huy Do wrote:

> Hi,
>
> I'm about to develop a major Python application and was wondering what 
> the
> most database independent way of going about accessing a database in my
> application. i.e so I can switch between databases almost instantly (a
> little bit of work is also okay :-)).

Depending on 'how portable' you wanted to be, and how complicated the 
application is.

For example, if you were restricting yourself to just MySQL and 
PostgreSQL, you could almost get away with simply doing a 'drop in 
replacement' between the two (as long as you didnt use foreign keys, or 
auto increment, or any of the other PG/My dependant things).

However, if you think you want a bit more portability, or even want to 
use some of the advanced features of just one or two database, the BEST 
way is to contain all your database-access code into a single module 
(or a grouping). This way, if you need to change, all your database 
stuff is isolated.

For example:

class MY_APP_DATABASE:

    def __init__ ( self ):
        self.db = PyPgSQL.connect ( lotsofparams)

    def add_thingy ( self, thingy ):
        cur = self.db.cursor ()
        cur.execute ( "insert into thingy ( id, name, properties ) 
values ( %(id)s, %(name)s, %(props) )", vars(thingy)

    ... and so on.


You can then perhaps create a switch in the __init__ function that 
connects to a different database, and other functions that use 
database-dependant stuff would change appropriately. the DB-API gives 
you portable /access/, but not portable /use/.

The rest of your code uses your application-specific DB layer, so won't 
care if you change the backend implementation.


--
    ("`-/")_.-'"``-._        Ch'marr, a.k.a.
     . . `; -._    )-;-,_`)  Chris Cogdon <chmarr@furry.org.au>
    (v_,)'  _  )`-.\  ``-'
   _.- _..-_/ / ((.'       FC1.3: 
FFH3cmA+>++C++D++H++M++P++R++T+++WZ++Sm++
((,.-'   ((,/   fL               RLCT acl+++d++e+f+++h++i++++jp-sm++