>> [...] Any row should "look like a dictionary", but python's
>> neat OO interface means we can implement it however we like. The only
>> reason I can think of for using something more complicated than a
>> dict for a row would be for "updatable cursors" which might need to hold more
>> information.
>>
> The other responses that I received indicated a preference for a tuple with
> the column name information obtainable elsewhere (or not at all). I agree
> with Aaron that a dict is a better choice because it retains the column
> heading information on a row-by-row basis, thus allowing each row to be
> "detached" from the query without loss of information.
You can still do this without making the row a dictionary. For
example, you can use a header object that is shared among multiple
rows. I think a cleaner model is that of records. That is,
rather than:
row['some_column_name']
allow:
row.some_column_name
I'd say that database users think more in terms of tuples with
attributes, than they do of dictionaries. Also, column order is
useful for some applications, so it should be possible to use tuples
like lists (of attributes). With a properly designed tuple object,
you could provide list, dictionary, and record semantics with much
less overhead than returning a dictionary for each row.
> I toyed with the idea of
> having the execution of a query return a Cursor object that could be
> treated like a list. I backed off of this because I didn't see any
> value in adding the overhead. Nor do I see the point in storing
> the full results of the query. If the user wants the full results stored,
> (s)he can do it outside of the framework of the module itself. To illustrate,
> my solution is a module that looks a little like this:
> class Oracle:
> def execute(self, query):
> # Pass the query to the DBMS and open a cursor for fetching
> def nextRow(self):
> # get the next row from the cursor and return it
> # if there is no next row, return None
> I use it as such:
>>>> o = Oracle()
>>>> o.execute('select * from my_tables')
>>>> results = []
>>>> row = o.nextRow()
>>>> while row: results.append(row); row = o.nextRow()
Yipes, don't you want to be able to process more than one table at a
time?
> I could have used list semantics for the nextRow, but I use an internal
> cache for the rows and when I need a row that is beyond the end of the
> cache, I flush the cache and refill it with the next n rows, so I have
> no way of going backwards.
It is still useful to provide partial list semantics (without true
random access) with your implementations that allow things like:
for tuple in DB.sql('select * from my_tables'):
...code on tuple...
which I find much more readable than:
table=DB.sql('select * from my_tables')
tuple=table.next()
while tuple:
...code on tuple...
tuple=table.next()
This can be done even if you only store one record at a time, as long
as you only allow sequential access. Basically, the index to the []
operator must be the current row, or the current row plus 1.
--
-- Jim Fulton jfulton@mailqvarsa.er.usgs.gov (703) 648-5622
U.S. Geological Survey, Reston VA 22092
This message is being posted to obtain or provide technical information
relating to my duties at the U.S. Geological Survey.