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

[David Morley]

Harri Pasanen wrote:
> 
> I think the space overhead criteria alone is sufficiently strong to do
> without a dictionary for each row.
> 
> The proper approach in my mind is to wrap the tuple access inside a
> class, which can provide very natural access to each field.
> 
> Possible interfaces to data, assuming row below is an instance of
> above mention wrapper class.
> 
> row.field1     # access field1 using __getattr__, name of field is 'field1'
> row._tuple[0]  # access field1 directly via tuple
> row.GetAttribute("field1")  # access via class internal dict.
> 
> If I remember, Jim Fulton proposed something like this long time ago,
> and I've personally implemented this kind of an interface.  Works very
> nicely.  Jim's proposal was more detailed than this quick sketch.
> I suggest you browse past archives for this sig, if those are
> available.
>
> Harri Pasanen

When accessing an mSQL database, I use a similar interface, which I
also find extremely useful.  One difference is that I use the dict
metaphor with attribute access as a shortcut, so row['field1'] and
row.field1 accesses field1 of the row, row.keys() returns the field
names, and row.values() (rather than row._tuple) returns the original
tuple.  What this means is that although you lose the ability to use
attribute access for fields named values, keys, has_key, etc., you
gain the ability to treat a record just like a dictionary if you want
to.

Another layer of abstraction (on top of the API) that I find useful
when doing simple selects on individual tables is the following:

server.keys() =
	list of databases handled by server
server.mydb = server['mydb'] =
	Database representing database mydb

server.mydb.table1 = server.mydb['table1'] = 
	Table object representing table1
server.mydb.keys() = 
	list of table names in database

server.mydb.table1() =
	select * from table1
server.mydb.table1(id=47) =
	select * from table1 where id=47
server.mydb.table1(category='big', user='Fred', date=INCREASING) =
	select * from table1 where category="big" and user="Fred" order by date

and so on, which provide a very compact, yet readable (to me at least
:-), interface to tabular databases for things like CGI script.  I can
use the same interface to access databases stored in flat files as
well.

If you want, the idea can even be extended (although it is a bit more
obscure) to fields if you want:

server.mydb.table1.keys() =
	list of fields in table1
server.mydb.table1.id = server.mydb.table1['id'] =
	Field object representing field id of table table1
server.mydb.table1.user.keys() =
	list of values for field user in table1
server.mydb.table1.user['Fred'] = server.mydb.table1.user.Fred =
	select * from table1 where user="Fred"

As you can see, I am addicted to this Dictionary + Attribute-access
mixed-metaphor.

 David

=================
DB-SIG  - SIG on Tabular Databases in Python

send messages to: db-sig@python.org
administrivia to: db-sig-request@python.org
=================