PySimpleDb
Python Database Access Simplified
FUTURE
I do not plan to release or work on this library going forward. I'm not really using it much these days and it needs some work to modernize it.
Feel free to reach out if you have questions.
DOWNLOAD
Here's a link to download the most recent version of this library. Download Now
WHAT YOU GET
In case you are wondering "What does this package do?", wonder no more. It is a simple database layer that sits on top of the standard Python DB-API 2.0.
pysimpledb has the following objectives
- improve developer performance
- keep the power of SQL
- be fast
- be flexible
- be simple
pysimpledb allows you to very easily create simple and fast database access layer for you application. With pysimpledb you can create your database with only basic Python classes and SQL. Here is an example of what you can do (file is also included in the examples section of the code download.).
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()
FAQ
Q. How can install this software?
You can install this package like any standard Python package. Type the following.
tar zxf pysimple-2.1.tar.gz
cd pysimpledb-2.1
python setup.py install
Q. What systems / processors does this code work on?
Since the code is pure Python, it should run fine on any system capable of running Python 2.5 - 2.7. I have confirmed it running correctly on Win32, Linux 2.6 kernel 32bit & 64bit, and Mac OSX.
MORE INFORMATION
If you need more information I suggest you take a look at the examples included in the download. They demonstrate some of the features of pysimpledb. You can also look at the README which attempts to explain the system. Lastly you can take a look at the code. There are loads of comments and, of course, there's the code.
Questions and comments are always welcome, shoot me an email.
CHANGES
Version 2.1
This is a minor update. Some of the ingrained functionality has now been ported to external mappers or providers. All of the same functionality is still there though. In addition, the ability to batch commands was added.
Please NOTE that there are some minor differences in configuration and usage with this version. There are fewer options that are required to be specified in configuration.
Version 2.0
This is a major update. It is not compatible with 1.0 in anyway and functions completely different. There were major problems with version 1.0. This release fixes so many things it is difficult to list them all. It is a good idea to use version 2.0!
Version 1.0
Created the library as an expirement on working with some of the dynamic aspect of Python. It does not have a lot of features, but is a solid start.
LICENSE
Copyright (C) 2009 Daniel Mikusa
This library is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version.
This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.
You should have received a copy of the GNU Lesser General Public License along with this library; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA