[clug] Python & databases

[Hal Ashburner]

On 22/02/2010 5:44 PM, Brendan Jurd wrote:
> On 22 February 2010 17:30, Hal Ashburner<hal.ashburner at gmail.com>  wrote:
>    
>> I'm playing with some python code that needs to connect to a database.
>> Ideally I'd like to be able to use differing database back ends with the
>> same application. I don't want to use sql alchemy and all it's ORM stuff.
>> Has anyone done this kind of thing in python? The dbapi2 stuff seems fairly
>> backend specific.
>>      
> The idea of dbapi is to provide an API that is consistent regardless
> of which driver you're using, so I'm not sure what you're driving at
> when you say "seems fairly backend specific".  In what sense?
>    
In the sense that there is so much discretion left to the implementation 
that the interface will never be consistent between different backends.
Casts are not handled the same way between back ends,  sqlite3 uses 
different place holders to psycopg2 (eg  ? vs %s)... and so on.
Either I'm reading it wrong or there doesn't seem to be a good way of 
doing very simple things and making it work with both backends. ie, 
nothing you list below, the resulting sql should be the same.

> dbapi (deliberately) only specifies API conventions for a very basic
> subset of operations, but unless you're looking to do something fancy,
> that shouldn't be a hindrance [1].
>
> Cheers,
> BJ
>
> [1] If you're doing anything non-trivial with databases you will need
> to write different SQL for the different backends anyway because each
> DBMS will have a different feature set, different interpretation of
> the SQL standard, different ways of optimising queries ...
>