• loading...

An API that dynamically generates the SQL in real time and maps queries, tables, and records into database

web2py comes with a Database Abstraction Layer (DAL), an API that maps Python objects into database objects such as queries, tables, and records. The DAL dynamically generates the SQL in real time using the specified dialect for the database back end, so that you do not have to write SQL code or learn different SQL dialects (the term SQL is used generically), and the application will be portable among different types of databases. A partial list of supported databases is show in the table below. Please check on the web2py web site and mailing list for more recent adapters


pool_size=0, folder=None, db_codec='UTF-8',
check_reserved=None, migrate=True, fake_migrate=False, migrate_enabled=True,
fake_migrate_all=False, decode_credentials=False, driver_args=None,
adapter_args=None, attempts=5, auto_import=False, bigint_id=False,
debug=False, lazy_tables=False, db_uid=None, do_connect=True,
after_connection=None, tables=None, ignore_field_case=True,
entity_quoting=False, table_hash=None)

Attributes / Arguments

The connection string is the only web2py code that depends on a specific back-end database.

SQLite    sqlite://storage.db
MySQL    mysql://username:password@localhost/test
PostgreSQL    postgres://username:password@localhost/test
MSSQL (legacy)    mssql://username:password@localhost/test
MSSQL (>=2005)    mssql3://username:password@localhost/test
MSSQL (>=2012)    mssql4://username:password@localhost/test
FireBird    firebird://username:password@localhost/test
Oracle    oracle://username/password@test
DB2    db2://username:password@test
Ingres    ingres://username:password@localhost/test
Sybase    sybase://username:password@localhost/test
Informix    informix://username:password@test
Teradata    teradata://DSN=dsn;UID=user;PWD=pass;DATABASE=test
Cubrid    cubrid://username:password@localhost/test
SAPDB    sapdb://username:password@localhost/test
IMAP    imap://user:password@server:port
MongoDB    mongodb://username:password@localhost/test
Google/SQL    google:sql://project:instance/database
Google/NoSQL    google:datastore
Google/NoSQL/NDB    google:datastore+ndb


 db = DAL('sqlite://storage.db', pool_size=0)

A common argument of the DAL constructor is the pool_size; it defaults to zero. As it is rather slow to establish a new database connection for each request, web2py implements a mechanism for connection pooling. Once a connection is established and the page has been served and the transaction completed, the connection is not closed but goes into a pool. When the next http request arrives, web2py tries to recycle a connection from the pool and use that for the new transaction. If there are no available connections in the pool, a new connection is established. When web2py starts, the pool is always empty. The pool grows up to the minimum between the value of pool_size and the max number of concurrent requests. This means that if pool_size=10 but our server never receives more than 5 concurrent requests, then the actual pool size will only grow to 5. If pool_size=0 then connection pooling is not used. Connections in the pools are shared sequentially among threads, in the sense that they may be used by two different but not simultaneous threads. There is only one pool for each web2py process. The pool_size parameter is ignored by SQLite and Google App Engine. Connection pooling is ignored for SQLite, since it would not yield any benefit.

db = DAL('...', pool_size=0)

where .table files will be created. Automatically set within web2py. Use an explicit path when using DAL outside web2py

If you work with existing databases that use different character encoding you have to change it with the optional parameter db_codec like


check_reserved tells the constructor to check table names and column names against reserved SQL keywords in target back-end databases. check_reserved defaults to None. This is a list of strings that contain the database back-end adapter names. The adapter name is the same as used in the DAL connection string. 

The DAL will scan the keywords in the same order as of the list. There are two extra options "all" and "common". If you specify all, it will check against all known SQL keywords. If you specify common, it will only check against common SQL keywords such as SELECT, INSERT, UPDATE, etc. For supported back-ends you may also specify if you would like to check against the non-reserved SQL keywords as well. In this case you would append _nonreserved to the name

db = DAL('sqlite://storage.db',
         check_reserved=[ 'postgres_nonreserved', 'mssql'])

sets default migrate behavior for all tables

db = DAL('sqlite://storage.db', migrate=False)

fake_migrate = False sets default fake_migrate behavior for all tables

migrate_enabled = True If set to False disables ALL migrations

fake_migrate_all = False If set to True fake migrates ALL tables

The decode_credentials argument is used to build the 'credential_decoder' lambda. If decode_credentials is False then the lambda doesn't do anything, but if decode_credentials is True then the lambda will pass  the credential through urllib.unquote.

Anytime a username or password is parsed from the URI, it will be passed through the credential_decoder lambda before being used with  the underlying database connection.

If web2py fails to connect to the database it waits 1 seconds and by default tries again up to 5 times before declaring a failure.

In case of connection pooling it is possible that a pooled connection that stays open but unused for some time is closed by the database end. Thanks to the retry feature web2py tries to re-establish these dropped connections.

DAL('...', attempts=5)

To access the data and its attributes we still have to define all the tables we are going to access with db.define_tables(...). 

If we just need access to the data but not to the web2py table attributes, we get away without re-defining the tables but simply asking web2py to read the necessary info from the metadata in the .table files:

from gluon import DAL, Field
db = DAL('sqlite://storage.sqlite',folder='path/to/app/databases',

The big-id and, big-reference are only supported by some of the database engines and are experimental. 

They are not normally used as field types unless for legacy tables, however, the DAL constructor has a bigint_id argument that when set to True makes the id fields and reference fields big-id and big-reference respectively.

lazy_tables parameter is one of the most significant response-time performance boosts in web2py.

web2py models are executed before controllers, so all tables are defined at every request. Not all tables are needed to handle each request, so it is possible that some of the time spent defining tables is wasted. web2py offers a big performance boost via lazy_tables. This feature means that table creation is deferred until the table is actually referenced. Enabling lazy tables is made when initialising a database via the DAL constructor. 


db_uid db_uid=None

Some times you may need to generate SQL as if you had a connection but without actually connecting to the database. In this case you will be able to call _select, _insert, _update, and _delete to generate SQL but not call select, insert, update, and delete. In most of the cases you can use do_connect=False even without having the required database drivers. This can be done with

db = DAL('...', do_connect=False)

after_connection after_connection=None

tables tables=None

You can also use explicit quoting of SQL entities at DAL level. It works transparently so you can use the same names in python and in the DB schema.

You can also use explicit quoting of SQL entities at DAL level. It works transparently so you can use the same names in python and in the DB schema. ignore_field_case = Trueentity_quoting = True

db = DAL('postgres://...', ...,ignore_field_case=False, entity_quoting=True)

db.define_table('table1', Field('column'), Field('COLUMN'))

print db(db.table1.COLUMN != db.table1.column).select()

table_hash table_hash=None

web2py Module List



Sources / Reference

" Web2py - Chapter 1" by Massimo is licensed under Creative Common License BY-NC-ND 3.0

" Google Groups"

" Web2py Book" is licensed under Creative Common License BY-NC-ND 3.0

" Test" is licensed under Attribution CC BY



Powered by

Hosted on
(affiliated link)