Introduction

Geniusql is a thread-safe Object-Relational Mapper for Python applications. It is designed to provide the "Model" third of an MVC application. When you build an application using Geniusql, you must supply the Controller(s) and View(s) yourself. Geniusql does not provide these, and does its best to not limit your choices regarding them.

If you're familiar with Martin Fowler's work [1], you can think of Geniusql as providing a Data Source layer, plus the tools to write your own Domain layer. For the Presentation layer, you're on your own. ;) It primarily uses a Row Data Gateway architecture.

Basic Structure

Developers build their Model by creating instances of Schema, Table, Column, and Index classes. For OLTP applications, Table objects expose the methods for managing individual rows: insert, select, save, and delete.

For OLAP applications, the Database exposes more generic select methods. Table instances can be associated to other Tables. This means that one of the Columns of TableA maps to one of the Columns of TableB. Related data may then be looked up more easily.

The results of a select call are usually dicts, where each Column in the result has a corresponding key/value pair in the dict. Complex queries can be written as pure Python lambdas, and Geniusql will decompile them into SQL.

Simple Example

Since a block of code is often worth a thousand words, here's a minimal example of a Geniusql application:

import geniusql

# Set up a global Schema and Database object.
conf = {'connections.Connect':
        r"PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\zookeeper.mdb;"}
db = geniusql.db("msaccess", **conf)
db.create()
schema = db.schema("main")
schema.create()

Zoo = schema.table('Zoo')
Zoo['ID'] = schema.column(int, autoincrement=True, key=True)
Zoo.add_index('ID')
Zoo['Name'] = schema.column()
Zoo['Size'] = schema.column(int)
Zoo['Opens'] = schema.column(datetime.time)
Zoo['LastEscape'] = schema.column(datetime.datetime)
schema['Zoo'] = Zoo

Animal = schema.table('Animal')
Animal['Legs'] = schema.column(int, default=4)
Animal['Name'] = schema.column()
Animal['ZooID'] = schema.column(int)
Animal.references['Zoo'] = ('ZooID', 'Zoo', 'ID')
schema['Animal'] = Animal

The above creates the model for the zookeeper application. There are three basic things happening:

1. The Zoo and Animal tables, which are instances of geniusql.Table. These will correspond to the Zoo and Animal tables within the database.
2. The association between the Animal class and the Zoo class.
3. The setup of a geniusql Schema object, including a Database which uses Microsoft Access (Jet).

Here's a simple interactive session which uses the above (assume that tables have been created and populated elsewhere):

>>> import zookeeper
>>> Animal.select_all()
[{'ID': 1, 'Name': 'Lion', 'Legs': 4, 'ZooID': 1},
 {'ID': 2, 'Name': 'Leopard', 'Legs': 4, 'ZooID': 1},
 {'ID': 3, 'Name': 'Centipede', 'Legs': 100, 'ZooID': 13},
 {'ID': 4, 'Name': 'Slug', 'Legs': 1, 'ZooID': 5},
 ]
>>> Zoo.select_all()
[]
>>> sdz = Zoo.insert(Name='San Diego Zoo', Size='38')
>>> sdz['ID']
1
>>> for creature in Animal.select_all():
        Zoo.save(ID=creature['ID'], ZooID=sdz['ID'])
>>> len(Animal.select_all(ZooID=sdz['ID']))
4

Design Goals

Geniusql is designed to function in environments with complex integration needs, and tends to separate concerns as much as possible. In particular, Geniusql tries to avoid making decisions in the framework which are better left to developers. Some of those decisions are:

• User interface. Geniusql works well in all sorts of applications, whether desktop, thin-client or web.
• Application package architecture. You can place your application within a single Python module, develop complete packages, or use Geniusql inside a larger framework.
• Which types to use for Columns. Builtin types are fully supported out of the box, including datetime and decimal. Tim Peters' excellent fixedpoint module is also available. New types are easily added.
• Which keys to use when associating Tables.
• What to name identifiers.

• Where (and how) to log error messages.
• Which database(s) to use.

Obtaining and Installing

You can obtain Geniusql from its Subversion repository at http://projects.amor.org/geniusql/svn/trunk. Geniusql is designed to be installed in site-packages/geniusql or some other root python path.

Geniusql was built using Python 2.4.2. You should probably use at least 2.3; Geniusql depends upon the datetime module. Although Geniusql supports additional modules like fixedpoint and decimal, it does not require them.

Geniusql uses bytecode hacks, and therefore requires CPython [2].

Compared To Other Database Wrappers

SQLObject

No matter what project I start on, odds are I'll discover that Ian Bicking has already done the same thing, usually better.
See http://blog.ianbicking.org/another-less-sleepy-alternative-to-hibernate.html
Which was a reply to Ruby's ActiveRecord: http://www.loudthinking.com/arc/000297.html
Which was a reply to Java's Hibernate: http://informit.com/guides/content.asp?g=java&seqNum=127&f1=rss

Using Geniusql, the application developer supplies the following code to define the Tables and their relationships:

from geniusql import *
import fixedpoint   # or decimal, for Python 2.4+
import datetime

conf = {'connections.Connect':
        r"host=localhost dbname=bookstore user=postgres password=****"}
db = geniusql.db("postgres", **conf)
db.create()
schema = db.schema("main")
schema.create()

Book = schema.table('Book')
Book['ID'] = schema.column(int, autoincrement=True, key=True)
Book['title'] = schema.column(str)
Book['price'] = schema.column(fixedpoint.Fixedpoint)
Book['publishDate'] = schema.column(datetime.datetime)
Book['publisher'] = schema.column(int)

def addAuthor(book, author):
    Authorship.insert(authorID=author['ID'], bookID=book['ID'])

Publisher = schema.table('Publisher')
Publisher['ID'] = schema.column(int, autoincrement=True, key=True)
Publisher['name'] = schema.column(str)

Author = schema.table('Author')
Author['ID'] = schema.column(int, autoincrement=True, key=True)
Author['name'] = schema.column(str)

Authorship = schema.table('Authorship')
Authorship['ID'] = schema.column(int, autoincrement=True, key=True)
Authorship['authorID'] = schema.column(int)
Authorship['bookID'] = schema.column(int)

Book.references['Publisher'] = ('publisher', 'Publisher', 'ID')
Authorship.references['Book'] = ('bookID', 'Book', 'ID')
Authorship.references['Author'] = ('authorID', 'Author', 'ID')

To create the tables, attach them to the schema object:

for t in (Book, Publisher, Author, Authorship):
    schema[t.name] = t

The app developer's runtime code reads as follows:

ppython = Book.insert(title='Programming Python', price=20,
                      publishDate=datetime.datetime(2001, 3, 1))
print ppython['title'] # output: 'Programming Python'

mlutz = Author.insert(name = 'Mark Lutz')
addAuthor(ppython, mlutz)

print len(Authorship.select_all(bookID=ppython['ID'])) # output: 1

def author_names(book):
    names = [name for (name, )
             in db.select_all(Authorship >> Author,
                              [None, ('name', )],
                              lambda aship, a: aship.bookID == ppython['ID']
                              )]
    return u', '.join(names)

print author_names(ppython) # output: 'Mark Lutz'

oreilly = Publisher.insert(name="O'Reilly")

Book.save(ID=ppython['ID'], publisher=oreilly['ID'])
print Publisher.select(ID=ppython['publisher'])['name'] # output: "O'Reilly"

print len(Book.select_all(authorID=oreilly['ID'])) # output: 1

print 'Hi,', author_names(Book.select(authorID=oreilly['ID'])) # output: "Hi, Mark Lutz"

[1] Fowler, Patterns of Enterprise Application Architecture.
[2] Geniusql relies upon bytecode hacking to achieve its clean lambda syntax for data queries. Therefore, it is CPython-specific. In addition, the bytecode of Python may change from one version of Python to another; if you find your version of Python does not work with Geniusql's codewalk and logic modules, please let me know.