New Database Abstraction Layer

Web2py trunk has a new implementation of the Database Abstraction Layer. Do not worry! We believe it is fully backward compatible but it is a complete rewrite of the old one. As of today it passes all our tests. Anyway, we will continue testing.

Why?

The new DAL consists of a single file

http://web2py.googlecode.com/hg/gluon/dal.py

with about 5000 lines of code.

The new DAL no longer depends on web2py and it can be used stand-alone into any Python program (compatibly with the GPL2 license). As before it may need third party drivers like pyodbc, psycopg2, etc.

The rewrite is much more modular and it will be easy to extend and maintain. We already have support for sqlite, jdbc:sqlite for jython, mysql, postgres, jdbc:postgres for jython, oracle, informix, db2, mssql, firebird, firebed embedded, ingres, and the gae datastore.

We are also working on CouchDB and MongoDB support and this should be available within mid January 2011.

Everything discussed in the book - chapter 6 still works fine, including:

• automatic transactions
• connection pooling
• multiple concurrent database connections
• multiple replicated clients for fail safety (optional round robin)
• distributed transaction (postgresql, mysql, firebird)
• joins, left joins, nested selects, and aggregates
• recursive selects

How?

If you want to try use it from a non web2py program and help us test it, grab the dal.py file and place it somewhere where Python can find it. Then try the following examples:

Import the new dal

>>> from dal import DAL, Field

create DAL connection (and create DB if not exists)

>>> db=DAL('sqlite://storage.sqlite',folder='/path/')

folder should be the location where metadata files are created. Other connection URIs are:

'sqlite://database.db'
'sqlite:memory' 
'jdbc:sqlite://database.db'
'mysql://user:password@localhost/database'
'postgres://user:password@localhost/database'
'jdbc:postgres://user:password@localhost/database' 
'mssql://user:password@A64X2/database'
'mssql2://user:password@A64X2/database'
'oracle://user:password@database'
'firebird://user:password@server:3050/database'
'db2://DSN=dsn;UID=user;PWD=pass'
'firebird://user:password@hostname/database'
'firebird_embedded://user:password@c://path'
'informix://user:password@server:3050/database'
'informixu://user:password@server:3050/database'
'gae'

define a table 'person' (create/aster as necessary)

>>> person = db.define_table('person',Field('name','string'))

Other Field types are:

id string text boolean integer double decimal password upload blob time date datetime

insert a record

>>> id = person.insert(name='James')

retrieve it by id

>>> james = person(id)

retrieve it by name

>>> james = person(name='James')

retrieve it by arbitrary query

>>> query = (person.name=='James')&(person.name.startswith('J'))
>>> james = db(query).select(person.ALL)[0]

update one record

>>> james.update_record(name='Jim')

update multiple records by query

>>> db(person.name.like('J%')).update(name='James')
1

delete records by query

>>> db(person.name.lower()=='jim').delete()
0

retrieve multiple records (rows)

>>> people = db(person).select(orderby=person.name,groupby=person.name,limitby=(0,100))

further filter them

>>> james = people.find(lambda row: row.name=='James').first()
>>> print james.id, james.name
1 James

delete one record

>>> james.delete_record()
1

commit your changes

>>> db.commit()

delete (drop) entire database table

>>> person.drop()