[Python-Dev] database APIs

[Kevin Jacobs]

On Sun, 2 Feb 2003, Luke Kenneth Casson Leighton wrote:
> has anyone considered doing database helper functions based
> off of the DB 2.0 api?

Yes; though the conversation is best had on the DB-SIG mailing list.

> the question is inspired by the amount of work that is
> needed to create a useable suite of database queries in
> the shortest period of time - something that is important
> when both developing databases and also when doing lots
> of database applications one after the other.

I've found that rapid initial development is far less important than ease of
validation, maintenance, and portability.  My belief is based on writing and
maintaining Python applications that interface to, at times >8 heterogeneous
database backends, each with many hundreds of tables and distinct OTP and
OLAP queries.

> also for some of the background please see the advogato article
> http://advogato.org/article/600.html particularly the link to
> jmg's code which contains some very simple to use but obscure 
> coding methods to wrap SQL databases in objects.

We (my company) and many others have their own SQL generation and OR mapping
implementations that are considerably more advanced.  Some (like mine) are
in the process of transitioning to an open source development model, while
some are already freely available (see http://colorstudy.com/software/SQLObject 
for one released just last week).  So maybe it is time for all of us to get 
together and put the Open Source "bazaar" to the test and see what comes out.

> the reason i ask is because the DB 2.0 api is great in
> that it makes things easy-ish to add in different databases
> (e.g. MySQL, MS-SQL, PostgreSQL) if you can deal with
> the syntactical differences, but it doesn't help you
> do things like:
> 
> - make a query that gets you some results
> 
> - modify one of the results
> 
> - write the modified results back.
> 
> i would _expect_ that the cursor class would have a function
> called save() which performed the last of these operations.

I would not (for many reasons what I will be happy to elaborate on later). 
Primarily, DB-API is intended to supply low level access to database
connections, transactions, simple result sets, SQL pass-through, and some
data type mapping capabilities.  The functionality you desire is complex
enough that it deserves its own layer/tier that sits on top of DB-API
modules.

> i was therefore wondering about two things:
> 
> - what do people on the python-dev list think of this concept?

The idea is great, though there is a good deal of design that needs to be
done.  I say this from experience, having built such a system.

> - is it something appropriate to be included in the
>   python libs, given that the python db API at present
>   is more of a convention than something that is
>   enforced as part of the python library suite.

Absolutely not.  Even after such a scheme has been battle-tested for a few
years and has a large and active user base, it will likely be very large and
have a radically different development cycle than the core interpreter.
However, if you care that much about formal Python legitimacy, then you can
work with DB-SIG to write a new standard for the features you desire.

Anyhow, we should move the rest of this discussion to the DB-SIG mailing
list (db-sig@python.org).

Thanks,
-Kevin

-- 
--
Kevin Jacobs
The OPAL Group - Enterprise Systems Architect
Voice: (216) 986-0710 x 19         E-mail: jacobs@theopalgroup.com
Fax:   (216) 986-0714              WWW:    http://www.theopalgroup.com