[PYTHON DB-SIG] DB-Modules returning lists of dicts

[Jim Fulton]

I'm afraid that I don't have enough time to participate in this list
like I would like to.  I haven't been able to follow this thread very 
closely, but I feel that I need to spend a little badwidth here to make
some points I've made before, but that have probably been forgotten.

1. At one point, I thought there was agreement that data should be
   returned in Database tuple objects, not Python tuples.  Database 
   tuple objects provide sequence, mapping, and getattr interfaces.
   For example, assume that you have a table with columns a, b, and c. 
   If I have a database tuple, t, then I can:

     t[1]      # Get value of b for t
     t.b       # ditto
     t['b']    # ditto
     t[1]=42   # Set value of b for t to 42 
               #(for database interfacess that allow this)
     t.b=42    # ditto
     t['b']=42 # ditto

   Database tuples store data internally as a C array of object pointers
   and a pointer to a shared meta-data object that has the data
necessary
   to do name->column mappings.  So the implementation is essentially as 
   efficient as Python tuples and yet provides name-based interfaces.

   I have implemented this mechanism for several database interfaces
   I did at USGS, but these were never released. :-(

   I feel very strongly that this is the "right" way to model result
data.

2. I don't particularly like the current database API.  I favor an 
   API that I consider far more natural.  Unfortunately, I don't have
time
   to develop a detailed proposal.  Here is a high-level sketch:

   Replace "cursors" with database table objects.

     import spamdb

     db=spamdb.Database(whatever) # Create a database connection
     
     table=db.execute("select * from spam")  # This creates a database 
                                             # table object
                            
   Note that, on systems that use cursors, table objects wrap cursors
and
   make the cursor look like a sequence of database records.  We don't
load
   all of the data into memory at once.  Rather, as we sequentially
access the
   data, the data are fetched, one row at a time.  The most common way
   to access the data will be via a for loop:

     for row in table:
       # Do stuff with the row, like, maybe printing out the values 
       # for column a
       print row.a

   Note that we could combine the table creation with the for:

     for row in db.execute("select * from spam"): ...

   We can also do non-select statements, as in:

     db.execute("create table eggs ...")

   If you want to get all of the data at once, you can use a slice:

     datacopy=table[:]

   Or

     data=db.execute("select * from spam")[:]

   And we can compile parameterized statements for use later as 
   functions:

     f=db.prepare("select * from spam where ni=%s")

   This creates a compiled statement that has a single string parameter.
   A database-independent string format should be used, IMO.  Now the
   compiled statement is used like any other Python callable object:

     table=f("foo")

   or

     for t in f("bar")   

   Databse connections may be closed explicitly:

     db.close()

   or implicitly when the database is GCed, as with file objects.

My $0.02.  Sorry it isn't more.  Silence is not assent.

-- 
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
=================