Welcome, guest | Sign In | My Account | Store | Cart
"""A wrapper around DBAPI-compliant databases to support iteration
and list comprehension syntax for requests, instead of SQL

To get an iterator, initialize a connection to the database, then

tbl = Table(connection,table_name)

returns an iterator that yields records (instances of the generic
class Record) whose attributes match the fields in the database

You can also choose to return a dictionary or a list with the
method set_return_type()

Example of use with sqlite :

    from pysqlite2 import dbapi2 as sqlite

    conn = sqlite.connect('planes')
    plane_tbl = Table(conn,'plane')
    country_tbl = Table(conn,'countries')

    # simple requests
    print [ r.name for r in plane_tbl if r.country == 'France' ]
    print [ r.country for r in country_tbl if r.continent == 'Europe']

    # request on two tables
    print [r.name for r in plane_tbl for c in country_tbl 
            if r.country == c.country and c.continent == 'Europe']


class Record(object):
    """A generic class for database records"""

class Table:

    def __init__(self,conn,table):
        self.table = table
        self.cursor = conn.cursor()
        self._iterating = False
        # to initialize cursor.description, make a select request
        self.sql = "SELECT * FROM %s" %self.table
        self.names = [ d[0] for d in self.cursor.description ]
        self.return_type = object
    def set_return_type(self,rt):
        if not rt in [object,list,dict]:
            raise TypeError,"Invalid return type %s" %rt
            self.return_type = rt
    def __iter__(self):
        return self
    def next(self):
        if not self._iterating:
            # begin iteration
            self._iterating = True
        row = self.cursor.fetchone()
        if row is not None:
            if self.return_type == object:
                # transform list into instance of Record
                rec = Record()
                rec.__dict__ = dict(zip(self.names,row))
                return rec
            elif self.return_type == dict:
                return dict(zip(self.names,row))
            elif self.return_type == list:
                return row
        self._iterating = False
        raise StopIteration