PYTHON DAO - DB CAFE

http://www.mikusa.com/pysimpledb/
tar zxf pysimple-2.1.tar.gz cd pysimpledb-2.1 python setup.py install
from pysimpledb.sql import AbstractDao
from pysimpledb.mappers import *
 
class Tea:
    """Simple data object, no parameters are needed"""
    def __init__(self, id = None, name = None, cost = 0.0):
        self.id = id
        self.name = name
        self.cost = cost
        self.mod = datetime.now()
 
    def __str__(self):
        return ("Tea : [id -> %s, name -> %s, cost -> %s, mod -> %s]" %
                                    (self.id, self.name, self.cost, self.mod))
 
    def __repr__(self):
        return str(self)
 
class TeaDao(AbstractDao):
    """
    Data access object for Tea.
 
    This class will contain the functions for accessing our database.
    """
    def __init__(self, db):
        """
        Define queries and database connection then passes them to super class.
 
        First define all of your queries in a dictionary.  The key is the
        name of the dynamic function that will be available on the Dao object.
        The value is another dictionary which defines the query.
 
        For details on the different options see the library documentation.
 
        The following example attempts to define as many of the different
        options as possible.
 
        Finally when all of your queries have been defined, simply pass the
        dictionary of queries and the database connection to the super class.
        """
        queries = {
            'get': {
                'sql': 'SELECT * FROM tea WHERE id = :id',
                'rowMapper': ObjectRowMapper(Tea),
                'execType': 'queryForObject'
            },
            'getAll': {
                'sql': 'SELECT * FROM tea',
                'rowMapper': ObjectRowMapper(Tea)
            },
            'getAllAsMap': {
                'sql': 'SELECT * FROM tea',
                'rowMapper': ObjectRowMapper(Tea),
                'execType': 'queryForMap',
                'defaultKeyParam': 'id'
            },
            'getTeaByName': {
                'sql': 'SELECT * FROM tea WHERE name = :name',
                'rowMapper': ObjectRowMapper(Tea)
            },
            'count': {
                'sql': 'SELECT COUNT(*) FROM tea',
                'rowMapper': SingleTypeRowMapper(int)
            },
            'sum': {
                'sql': 'SELECT SUM(cost) FROM tea',
                'rowMapper': SingleTypeRowMapper(float)
            },
            'avg': {
                'sql': 'SELECT AVG(cost) FROM tea',
                'rowMapper': SingleTypeRowMapper(float)
            },
            'insert': {
                'sql': 'INSERT INTO tea (name, cost, mod) VALUES (:name, :cost, :mod)',
                'paramClass': Tea,
                'insertId': 'id'
            },
            'batch': {
                'sql': 'INSERT INTO tea (name, cost, mod) VALUES (:name, :cost, :mod)',
                'paramClass': Tea,
                'execType': 'batch'
            },
            'deleteAll': {
                'sql': 'DELETE FROM tea'
            },
            'update': {
                'sql': 'UPDATE tea SET name = :name, cost = :cost, mod = :mod WHERE id = :id',
                'paramClass': Tea
            },
            'drop': {
                'sql': 'DROP TABLE tea'
            },
            'create': {
                'sql': (
                        'CREATE TABLE IF NOT EXISTS tea ('
                            'id integer primary key autoincrement, '
                            'name text, '
                            'cost real, '
                            'mod date default current_date '
                        ');')
            }
        }
        AbstractDao.__init__(self, db, queries)
 
if __name__ == '__main__':
    import sqlite3
    from datetime import datetime
    try:
        # Connect to my database
        db = sqlite3.connect(database='test.db')
 
        # Build basic objects
        t1 = Tea(name='Earl Grey', cost=1.25)
        t2 = Tea(name='Lady Grey', cost=1.25)
        t3 = Tea(name='English Breakfast Tea', cost=1.35)
        t4 = Tea(name='Green Tea', cost=0.90)
        t5 = Tea(name='Black Tea', cost=0.50)
        t6 = Tea(name='White Tea', cost=2.25)
        t7 = Tea(name='Lemon Lift', cost=1.00)
        t8 = Tea(name='Mint Tea', cost=1.99)
        t9 = Tea(name='Dutch Express', cost=2.00)
        t10 = Tea(name='French Twist', cost=1.75)
 
        print (t1, t2, t3, t4, t5, t6, t7, t8, t9, t10)
 
 
        # Create my Dao Object
        dao = TeaDao(db)
 
        # Have the Dao create the table
        dao.create()
        print 'Created'
        print
 
        # Insert my objects
        dao.insert(t1)
        print t1.id
        dao.insert(t2)
        print t2.id
        dao.insert(t3)
        print t3.id
        dao.insert(t4)
        print t4.id
        dao.insert(t5)
        print t5.id
        dao.insert(t6)
        print t6.id
 
        print 'Batch inserted %d elements' % dao.batch((t7, t8, t9, t10))
        print 'Ids not set automatically! (%s, %s, %s, %s)' % (t7.id, t8.id, t9.id, t10.id)
 
        # Print list
        print
        for obj in dao.getAll():
            print '(%s, %s, %s, %s)' % (obj.id, obj.name, obj.cost, obj.mod)
        print
 
        # Raise Prices
        print
        for obj in dao.getAll():
            obj.cost += (obj.cost * 0.1)
            obj.mod = datetime.now()
            dao.update(obj)
 
        # Reprint list
        print
        for obj in dao.getAll():
            print '(%s, %s, %s, %s)' % (obj.id, obj.name, obj.cost, obj.mod)
        print
 
        # Print the list as a map keyed by the default id
        print
        for key,objs in dao.getAllAsMap().iteritems():
            for obj in objs:
                print 'id %s -> (%s, %s, %s, %s)' % (key, obj.id, obj.name, obj.cost, obj.mod)
        print
 
        # Print the list as a map key by the name
        print
        for key,objs in dao.getAllAsMap(keyParam="name").iteritems():
            for obj in objs:
                print 'name %s -> (%s, %s, %s, %s)' % (key, obj.id, obj.name, obj.cost, obj.mod)
        print
 
        # Execute some scalar operations
        print
        print 'Found %d number of teas' % dao.count()
        print 'Total %0.2f price of teas' % dao.sum()
        print 'Average %0.2f price of teas' % dao.avg()
        print
 
        # Delete Objects
        dao.deleteAll()
        print 'Deleted'
 
        # Drop table
        dao.drop()
        print 'Dropped'
 
    except sqlite3.Error,e:
        print
        print 'Uncaught Exception: ',str(e)
        print
    finally:
        db.close()