Web2py - Database Handling

Database Handling

The database abstraction layer (DAL) of web2py dynamically and transparently generates SQL queries and runs on multiple compatible database backend without the need for database-specific SQL commands (though SQL commands can be issued explicitly).

SQLite is included in Python and is the default web2py database. A connection string change allows connection to Firebird, IBM DB2, Informix, Ingres, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, and Google App Engine (GAE) with some caveats. Specialities:

• Multiple database connections.
• Automatic table creates and alters.
• Automatic transactions.
• Distributed transactions:
  • Since web2py v1.17 with PostgreSQL v8.2 and later, because it provides API for two-phase commits.
  • Since web2py v1.70.1 with Firebird and MySQL (experimental).
• GAE is not a relational store, but web2py emulates certain operations.

The DAL is fast, at least comparable with SQLAlchemy and Storm.

Web2py implements a DAL, not an ORM. An ORM maps database tables into classes representing logical abstractions from the database layer (e.g., a User class or a PurchaseOrder class), and maps records into instances of those classes. The DAL instead maps database tables and records into instances of classes representing sets and records instead of higher-level abstractions. It has very similar syntax to an ORM but it is faster, and can map almost any SQL expressions into DAL expressions. The DAL can be used independently of the rest of web2py.

Here are some examples of DAL syntax:

db = DAL('postgresql://user:pass@localhost/db', pool_size=10) db.define_table('person',Field('name'),Field('image','upload')) db.person.insert(name='Martin', image=open('filename.png')) rows = db((db.person.name=='Martin')|db.person.name.contains('T')).select(orderby=db.person.name.lower)

The latest version of the DAL has support for 2D GIS functions with Spatialite and PostGIS. The current API are experimental because of a possible move to 3D APIs.