[clug] Python & databases

[Daniel Pittman]

Brendan Jurd <direvus at gmail.com> writes:
> 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?

I bet the answer is "...you need to write back-end specific SQL"[1], even if
the API is uniform.

Sadly, I don't know of any Python equivalent of the Perl SQL::Abstract module,
which achieves this by providing a Perl-like syntax to writing queries, and a
set of back-end modules to generate the individual SQL statement from it.

> 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].
>
> [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 ...

Uh-huh.  Which, I think, is the objection that the OP has to dbapi2.  Sad
realities and all that.

        Daniel

Footnotes: 
[1]  Theoretically you can write engine-independent SQL to the standard, but
     I have *never* had that be sufficient in practice.  Certainly not if
     performance is desired.

-- 
✣ Daniel Pittman            ✉ daniel at rimspace.net            ☎ +61 401 155 707
               ♽ made with 100 percent post-consumer electrons