The connection string is the only web2py code that depends on a specific back-end database.
MSSQL (legacy) mssql://username:password@localhost/test
MSSQL (>=2005) mssql3://username:password@localhost/test
MSSQL (>=2012) mssql4://username:password@localhost/test
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
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.
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.
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()
Define and set table field
Construct web2py DAL queries
The most common way to use redirect is to redirect to other pages
Define database table