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.

See Also:

Python DB-SIG Pages
  To find out more about the DB-API 2.0 or how to program using DB-API 2.0 methods, please visit http://www.python.org/topics/database/. The rest of this documentation will assume you are not interested in using the cursor as a DB-API 2.0 cursor and that you want to know the additional features available.