pysimpledb - Python Simple Database Access

DOWNLOAD

Here's a link to download the most recent version of this library. Download Now

Also, you can get the latest files via easy_install.

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.).

#
# This file is part of pysimpledb.
#
# pysimpledb 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 3 of the License, or
# (at your option) any later version.
#
# pysimpledb 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 pysimpledb.  If not, see <http://www.gnu.org/licenses/>.
#
#      Author Daniel Mikusa <dan@mikusa.com>
#   Copyright Daniel Mikusa 2008
#
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

or with easy_install

        
            easy_install -U pysimpledb

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 or 2.6. 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

pysimpledb 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 3 of the License, or (at your option) any later version.

pysimpledb 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 pysimpledb. If not, see .