다른 명령
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()