Python Database Objects (DBO)
DBO is a database object relational mapper (ORM) written entirely in Python.
It's similar to
PyDO,
SQLObject, and
SQLDict, among others.
DBO allows you to reflect your database tables into python objects. DBO
composes all SQL queries, allowing the developer to focus on other things. It
was originally written as a component system for a web framework, but it has
since become its own framework that is useful in contexts beyond web
development.
It has a number of features:
- Python 2.2 newclass style support.
- Major SQL verbs: select, insert, update, delete, create, drop
- Complex SQL queries supported: Programmatically build your SQL queries
using python based class definitions.
- Database independence: Port your DBO objects to a new database engine with
minimal to no changes.
- Sequence and auto-increment support: A database column that serves as a
table's unique ID can be either a sequence or an auto-increment, regardless of
the underlying database engine.
- Transaction support (when supported by the database engine): Update,
Insert and Delete as many objects as you want within a transaction, and you can
roll back all the changes.
- Schema creation: you don't even need to write the schema for your tables,
DBO will do that for you given the structure of the Table classes you define.
This simplifies schema adjustments by allowing the developer to make the change
once.
- Support for single or multiple primary keys: A unique feature not found in
other python based ORMs.
- Flexible type system: Using the primitive column types, you can build more
complex column types that are used to convert the data as it flows in and out
of the database.
- Support for relational information: DBO understands when a specific column
is a foreign key, and, in response to a reference(), DBO will wrap the foreign
table reference in its designated Table object, automatically.
- Support for relational information BEFORE you insert using a link: If you
need to relate objects before you insert them, just link() the columns. DBO
will ensure the generated database IDs will propagate to foreign references as
soon as you insert your changes.
- On the fly Views: DBO can join two or more Tables into a single object,
using appropriate foreign keys to automatically build a join statement for
speedups during selects.
- Serialization support across the board: Every DBO class defined can save
and restore its state to disk. This not only includes Tables and Views, but
ResultSets, links, and query strings!
- WebKit plugin, intergrate DBO objects with your servlets.
DBO is written with an emphasis on clarity and consistency. DBO follows many
python idioms allowing sage developers a short learning curve.
Currently, DBO has no documentation, but here is a tiny example of how it's
used.
Generated SQL commands are hilighted using this box.
import dbo
from dbo.sqlops import *
dbi = dbo.dsn_connect( 'mysql://dbo:dbo@localhost/db', '__ALIAS__' )
class User( dbo.Table ):
tablename = 'user' # table name in the db
alias = '__ALIAS__' # alias used to group objs
class Columns( dbo.Columns ): # class containing columns
class user_id( dbo.IntCol ): # columns definition
primary = True # column attributes
auto_increment = True
class username( dbo.VarcharCol ):
pass
class password( dbo.VarcharCol ):
not_null = False
class access( dbo.EnumCol ): # special enumerator coltype
enumerate = ('guest','user','admin')
User.create_tables() # write the table definition
CREATE TABLE `user` (username VARCHAR(255) NOT NULL, access INT NOT NULL, password VARCHAR(255) NULL, user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) type=myisam
u = User.new() # create a new instance of User
u.username = 'Bob' # assign it its values
u.password = 'password'
u.access = User.access.guest # User is the class!
u.insert()
insert into `user` (username,access,password,user_id) values ('Bob','0','password',NULL)
select username, access, password, user_id from `user` where user_id = '1'
# compound SQL queries use the column types to help describe
# the criteria to match on.
u = User.select_where( AND( User.user_id != 0, User.access == User.guest ))
select user_id from `user` where user_id != '0' AND access = '0'
select username, access, password, user_id from `user` where user_id = '1'