[PYTHON DB-SIG] Jim's DBAPI proposelet
Jim Fulton
jim.fulton@digicool.com
Wed, 16 Oct 1996 10:00:27 -0400
As has been mentioned several times, I have a number of ideas
for improving the Python Database API. I apologize for not
providing input earlier due to conflicting priorities. These
conflicting priorities persist, so my tardy input is likely to
continue.
My interest in the database API is based on experience with
developing 3 separate Python database extesnions. These were
for three *tabular* databases: Ingres, Arc/Info (a GIS system),
and Rand RDB (similar to and inspired by the /rdb product). Only
one of these databases is SQL based.
A guiding principle is that these interfaces should be as simple
and intuitive as possible, using existing protocols/models, and
where possible, promoting portability.
Here are some high-level notions:
- Result tables should be *sequences* is database records.
These sequences might only allow sequential access, that
is for loop access, so as to avoid having to store all data
in memory.
- Database records should be:
o Sequences of values
o Mappings from column name to value
o Records, supporting attribute access of column values
- Result table meta-data objects should provide access
to column definitions and other meta-data and should be
shared by both result tables and by database records.
- It should be possible to subclass result tables in some fashion
to add methods and to treat database records as instances.
(I have not implemented this yet, but have been thinking
quite seriously about it.)
- Database objects model and encapsulate database connections.
- Database objects provide methods to create prepared statements,
result tables, and to execute sql statements immediately.
- Prepared statements should look like functions.
- Here are some examples that show, informally, what I think
we should shoot for.
from somedb import Database
db=Database("...some connection string...")
for r in db.execute("select * from spam"):
...do stuff with r...
# Notice the use of standard python format strings.
# This would have to be extended to accomodate
# additional types, such as dates and blobs.
ins=db.prepare('insert into spam values(%s,%d,%f)')
ins('hello',23,123.456)
# Note the use of None to input null values.
ins('bye',None,None)
- The database execute method and prepared statements should
either return a result table, an integer number of rows
updated or inserted, or None.
There are a number of issues that need to be addressed:
- A standard exception model. For example, there need to be ways
of specific errors, such as:
- Invalid statements,
- Lost database connections,
- Invalid arguments to prepared statements.
- ...
- A standard mete-data mechanism. We need to define what meta-data
objects look like. Here's what I've done:
o Meta-data objects are accessed via __dict__ attributes of
result tables or rows.
o Meta-data objects are sequences of column definitions.
o Meta-data objects are mappings from column names to column
definitions.
o Meta-data objects have keys, items, and values methods that
return data ordered on column number.
o Column definitions are mapping objects that map column
attribute names to column attribute values.
There should be standard column attributes, such as:
name, type, and remark, as well as database specific
attributes. There should be some standard types, but
what are they and how should they be represented?
I'm sure that there are other details I'm forgetting, but this
at least provides a first cut at writing these down.
If people seem to think that the approach outlined is sound, then
I'd be happy to come up with a more formal proposal.
I've got to run.
Jim
--
Jim Fulton Digital Creations
jim@digicool.com 540.371.6909
## Python is my favorite language ##
## http://www.python.org/ ##
=================
DB-SIG - SIG on Tabular Databases in Python
send messages to: db-sig@python.org
administrivia to: db-sig-request@python.org
=================