Database Abstraction Layer DAL

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

uri='sqlite://dummy.db'

 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

db_codec='UTF-8',

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. 

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.

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(...). 

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

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

DAL(...,lazy_tables=True)

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)

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()