dtuble extended

dtuple.py

#
# dtuple.py: Database Tuple handling
#
# Written by Greg Stein. Public Domain.
# No Copyright, no Rights Reserved, and no Warranties.
#
# This module is maintained by Greg and is available at:
#    http://www.lyra.org/greg/python/dtuple.py
#
# Some discussion/usage of this module can be found at:
#    http://www.python.org/pipermail/db-sig/1996-January/000000.html
#    http://www.python.org/pipermail/db-sig/1997-January/000152.html
#    (and various other posts around these time frames)
#
# Since this isn't in any Python distribution yet, we'll use the CVS ID for
# tracking:
#   $Id: dtuple.py,v 1.1 2000/04/18 20:17:20 gstein Exp $
#

class TupleDescriptor:
  """Describes a return tuple from a DB-API fetch*() method.

  Instances of this class are used to describe database tuples (which are
  typically instances of DatabaseTuple or one of its derivative classes).
  These instances specify the column names, formats, lengths, and other
  relevant information about the items in a particular tuple. An instance
  is typically shared between many database tuples (such as those returned
  by a single query).

  Note: the term database tuple is rather specific; in actuality the tuple
  may have come from non-database sources and/or generated by a process
  wholly unrelated to databases.

  Note again: I'm open for new names for this and the DatabaseTuple class
  and concept :-)
  """

  def __init__(self, desc):
    """TupleDescriptor constructor.

    An instance is created by passing a "descriptor" to fully specify the
    information about the related database tuple. This descriptor takes the
    form of a tuple or list where each element is a tuple. The first element
    of this tuple is the name of the column. The following elements of the
    tuple are used to describe the column (such as length, format,
    significant
    digits, etc).
    """
    self.desc = tuple(desc)
    ### validate the names?
    self.names = map(lambda x: x[0], desc)
    self.namemap = { }
    for i in range(len(self.names)):
      self.namemap[self.names[i]] = i
      
  def __len__(self):
    """Returns the number of elements in the data object.

    A tuple descriptor responds to __len__ to simplify some processing by
    allowing the use of the len() builtin function.
    """
    return len(self.names)
    
  def __repr__(self):
    return '%s(%s)' % (self.__class__.__name__, repr(self.desc))
  def __str__(self):
    return str(self.desc)



class DatabaseTuple:
  """Wraps the return data from a DB-API fetch*() method.

  Instances of this class are used to represent tuples of information,
  typically returned by a database query. A TupleDescriptor is used as
  a means of describing the information for a variety of access methods.
  The tuple's information can be accessed via simple indexing, slices,
  as a mapping where the keys are the column names (as defined by the
  descriptor), or via attribute-based access (where the attribute names
  are equivalent to the column names).

  This object acts as a tuple, a list, a mapping, and an instance. To
  retrieve "pure" tuples, lists, or mappings, the asTuple(), asList(),
  and asMapping() methods may be used, each returning a value equal to
  what this object pretends to be.

  There exists a potential ambiguity between attempting to act as a list
  or mapping and the attribute-based access to the data. In particular,
  if the column names are 'index', 'count', 'keys', 'items', 'values', or
  'has_key', then the attribute-based access will have precedence over
  their related methods for lists and mappings. To actually use these
  methods, simply apply them to the result of the asList() or asMapping()
  methods.

  Note that column names with leading underscores may interfere with
  the implementation of this class, and as a result may not be accessible
  via the attribute-access scheme. Also, column names of asTuple, asList,
  and asMapping will be inaccessible via the attribute-access scheme
  since those will always represent the methods. To access these columns,
  the mapping interface can be used with the column name as the mapping
  key.

  Note that a database tuple acts as a tuple with respect to sub-scripted
  assignment. TypeError exceptions will be raised for several situations,
  and AttributeError may be raised for some methods that are intended
  to mutate the data (list's 'sort' method) as these methods have not
  been implemented.
  """

  def __init__(self, desc, data):
    """DatabaseTuple constructor.

    A DatabaseTuple is initialized with a TupleDescriptor and a tuple or
    list specifying the data elements.
    """
    if len(desc) != len(data):
      raise ValueError  # descriptor does not seem to describe tuple
    if type(desc) == type(()) or type(desc) == type([]):
      desc = TupleDescriptor(desc)
    self.__dict__['_desc_'] = desc
    self.__dict__['_data_'] = tuple(data)
    
  def __str__(self):
    return str(self._data_)
  def __repr__(self):
    return '%s(%s,%s)' % (self.__class__.__name__,
                          repr(self._desc_),
                          repr(self._data_))
  
  def __cmp__(self, other):
    if type(self._data_) == type(other):
      return cmp(self._data_, other)
    if type(self._data_) == type( {} ):
      return cmp(self.asMapping(), other)
    if type(self._data_) == type( () ):
      return cmp(self.asTuple(), other)
    if type(self) == type(other):  ### fix this: need to verify equal classes
      return cmp(self._data_, other._data_)
    return cmp(self._data_, other)
    
  def __getattr__(self, name):
    'Simulate attribute-access via column names'
    return self._getvalue_(name)
  
  def __setattr__(self, name, value):
    'Simulate attribute-access via column names'
    ### need to redirect into a db update
    raise TypeError, "can't assign to this subscripted object"

  def __getitem__(self, key):
    'Simulate indexed (tuple/list) and mapping-style access'
    if type(key) == type(1):
      return self._data_[key]
    return self._getvalue_(key)
  
  def __setitem__(self, key, value):
    'Simulate indexed (tuple/list) and mapping-style access'
    if type(key) == type(1):
      ### need to redirect into a db update of elem #key
      raise TypeError, "can't assign to this subscripted object"
    ### need to redirect into a db update of elem named key
    raise TypeError, "can't assign to this subscripted object"
  
  def __len__(self):
    return len(self._data_)
  
  def __getslice__(self, i, j):
    'Simulate list/tuple slicing access'
    return self._data_[i:j]
  
  def __setslice__(self, i, j, list):
    'Simulate list/tuple slicing access'
    ### need to redirect into a db update of elems
    raise TypeError, "can't assign to this subscripted object"
  
  def _keys_(self):
    "Simulate mapping's methods"
    return self._desc_.names
    
  def _has_key_(self, key):
    "Simulate mapping's methods"
    return key in self._desc_.names
  
  def _items_(self):
    "Simulate mapping's methods"
    return self.asMapping().items()
  
  def _count_(self, item):
    "Simulate list's methods"
    return self.asList().count(item)
  
  def _index_(self, item):
    "Simulate list's methods"
    return self.asList().index(item)
  
  def _getvalue_(self,name):
    'Internal method for named-based value retrieval'
    if name not in self._desc_.names:
      if name == 'keys':
        return self._keys_
      if name == 'items':
        return self._items_
      if name == 'values':
        return self.asList
      if name == 'has_key':
        return self._has_key_
      if name == 'count':
        return self._count_
      if name == 'index':
        return self._index_
      raise AttributeError
    return self._data_[self._desc_.namemap[name]]
    
  def asMapping(self):
    'Return the "tuple" as a real mapping'
    value = { }
    for name, idx in self._desc_.namemap.items():
      value[name] = self._data_[idx]
    return value
    
  def asTuple(self):
    'Return the "tuple" as a real tuple'
    return self._data_

  def asList(self):
    'Return the "list" as a real mapping'
    return map(None, self._data_)

rtdb.py

#
# rtdb.py: Extension for dtuple.py
#
# Written by Dennis T Kaplan . Public Domain.
# No Copyright, no Rights Reserved, and no Warranties.

import sqlite3
import dtuple

def getOne(dbname, sql):
    connection = sqlite3.connect(dbname)
    cursor = connection.cursor()
    cursor.execute(sql)
    descriptor = dtuple.TupleDescriptor(cursor.description)
    cursor_row = cursor.fetchone()
    row = dtuple.DatabaseTuple(descriptor, cursor_row)
    cursor.close()
    return row


class getAll:

    def __init__(self, dbname, sql, one = True):
        self.one = one
        connection = sqlite3.connect(dbname)
        self.cursor = connection.cursor()
        self.cursor.execute(sql)

        self._descriptor = dtuple.TupleDescriptor([[f][0] for f in self.cursor.description])
        self._rows = self.cursor.fetchall()

    def __getitem__(self, index):
        rows = dtuple.DatabaseTuple(self._descriptor, self._rows[index])
        self.cursor.close()
        return rows

    def __len__(self):
        return len(self._rows)

test.py

# Use it like this:

import os
import sys
root = os.path.realpath(os.path.expanduser('~/py'))
libs = os.path.join(root,'lib')
data = os.path.join(root,'data')
sys.path.append(libs)
from rtdb import *

print '\nrow'
# getting One
todo = getOne(os.path.join(data,'todo.db'), "SELECT * FROM todo LIMIT 1")
print '\n\trow:\n'
print 'id: ' + str(todo.id)
print 'todo: ' + todo.task
print 'status: ' + str(todo.status)
print 'length: '
print len(todo)

print '\n\trows'
# getting all
rows = getAll(os.path.join(data,'todo.db'), "SELECT * FROM todo", 2)
print '\n\trow:\n'
print 'id: ' + str(rows[0].id)
print 'todo: ' + rows[0].task
print 'status: ' + str(rows[0].status)
print 'length rows: '
print len(rows)
print 'length fields: '
print len(rows[0])