1.4 web.database -- SQL database layer

1.4 web.database -- SQL database layer

The web.database module is a simple SQL abstraction layer which sits on top of a DB-API 2.0 cursor to implement data type conversions, provide database independance and offer a more Python-like interface to the data returned from queries. This is achieved by implementing common field types, a portable SQL dialect and a standard API for all supported databases.

Here are the main features of the module:

100% compatible with the underlying DB-API 2.0 cursor. A web.database cursor provides access to the underlying DB-API 2.0 cursor.

Provides methods including select(), insert(), update(), delete(), create(), alter() and drop() which build and customise the SQL depending on the database being used providing database independance.

Provides strong typing for the data being used. No need to deal with SQL strings, the module automatically encodes and decodes data for the approriate column.

This module has a number of different layers of increasing complexity and decreasing portability. It is important you understand which layer you wish to use for a particular task. If for example you are only going to work with one database you do not need to be concerned about portability and so might use the cursor object in direct mode. If you don't know any SQL you might choose to use the web.database.object module to treat the database as a Python dictionary and allow portable access.

Warning: The web.database module provides total database portability by converting SQL and data types to an appropriate form for a limited subset of functions and data types of the underlying database engine. There are two drawbacks to this approach. Firstly the web.database layer needs to know the structure of the database. It does this by mainting a special table which it hides. The second drawback is that if you access the database outside the web.database module it is possible that the changes you make will not be compatible with the web.database module.

Having said all that, if you only access your databases through the web.database module in protable mode (the default) these drawbacks will not be an issue.

If you are looking for a database abstraction module to get away from using DB-API methods, and to pick up features such as results that are returned by field name but are not so worried about the complete portability provided by portable mode, perhaps because you only intend to use one database, you could instead use these modules in direct mode.