DAL constructor

Basic use:

>>> db = DAL('sqlite://storage.sqlite')

The database is now connected and the connection is stored in the global variable db.

At any time you can retrieve the connection string.

>>> db._uri
sqlite://storage.sqlite

and the database name

>>> db._dbname
sqlite

The connection string is called a _uri because it is an instance of a Uniform Resource Identifier.

The DAL allows multiple connections with the same database or with different databases, even databases of different types. For now, we will assume the presence of a single database since this is the most common situation.

DAL signature

DAL(uri='sqlite://dummy.db',
    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)

Connection strings (the uri parameter)

A connection with the database is established by creating an instance of the DAL object:

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

db is not a keyword; it is a local variable that stores the connection object DAL. You are free to give it a different name. The constructor of DAL requires a single argument, the connection string. The connection string is the only web2py code that depends on a specific back-end database. Here are examples of connection strings for specific types of supported back-end databases (in all cases, we assume the database is running from localhost on its default port and is named “test”):

ndb

Notice that in SQLite the database consists of a single file. If it does not exist, it is created. This file is locked every time it is accessed. In the case of MySQL, PostgreSQL, MSSQL, FireBird, Oracle, DB2, Ingres and Informix the database “test” must be created outside web2py. Once the connection is established, web2py will create, alter, and drop tables appropriately.

In the MySQL connection string, the ?set_encoding=utf8mb4 at the end sets the encoding to UTF-8 and avoids an Invalid utf8 character string: error on Unicode characters that consist of four bytes, as by default, MySQL can only handle Unicode characters that consist of one to three bytes. [mathiasbyensbe]

In the Google/NoSQL case the +ndb option turns on NDB. NDB uses a Memcache buffer to read data that is accessed often. This is completely automatic and done at the datastore level, not at the web2py level.

It is also possible to set the connection string to None. In this case DAL will not connect to any back-end database, but the API can still be accessed for testing.

Some times you may need to generate SQL as if you had a connection but without actually connecting to the database. This can be done with

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

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.

Notice that by default web2py uses utf8 character encoding for databases. If you work with existing databases that behave differently, you have to change it with the optional parameter db_codec like

db = DAL('...', db_codec='latin1')

Otherwise you’ll get UnicodeDecodeError tickets.

Connection pooling

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.

Connection failures (attempts parameter)

If web2py fails to connect to the database it waits 1 second 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. The number of attempts is set via the attempts parameter.

Lazy Tables

setting lazy_tables = True provides a major performance boost. See below: lazy tables

Model-less applications

Using web2py’s model directory for your application models is very convenient and productive. With lazy tables and conditional models, performance is usually acceptable even for large applications. Many experienced developers use this is production environments.

However, it is possible to define DAL tables on demand inside controller functions or modules. This may make sense when the number or complexity of table definitions overloads the use of lazy tables and conditional models.

This is referred to as “model-less” development by the web2py community. It means less use of the automatic execution of Python files in the model directory. It does not imply abandoning the concept of models, views and controllers.

Web2py’s auto-execution of Python code inside the model directory does this for you:

1. models are run automatically every time a request is processed
2. models access web2py’s global scope.

Models also make for useful interactive shell sessions when web2py is started with the -M commandline option.

Also, remember maintainability: other web2py developers expect to find model definitions in the model directory.

To use the “model-less” approach, you take responsibility for doing these two housekeeping tasks. You call the table definitions when you need them, and provide necessary access to global scope via the current object (as described in Chapter 4).

For example, a typical model-less application may leave the definitions of the database connection objects in the model file, but define the tables on demand per controller function.

The typical case is to move the table definitions to a module file (a Python file saved in the modules directory).

If the function to define a set of tables is called define_employee_tables() in a module called “table_setup.py”, your controller that wants to refer to the tables related to employee records in order to make an SQLFORM needs to call the define_employee_tables() function before accessing any tables. The define_employee_tables() function needs to access the database connection object in order to define tables. This is why you need to correctly use the current object in the module file containing define_employee_tables() (as mentioned above).

Replicated databases

The first argument of DAL(...) can be a list of URIs. In this case web2py tries to connect to each of them. The main purpose for this is to deal with multiple database servers and distribute the workload among them). Here is a typical use case:

db = DAL(['mysql://...1', 'mysql://...2', 'mysql://...3'])

In this case the DAL tries to connect to the first and, on failure, it will try the second and the third. This can also be used to distribute load in a database master-slave configuration. We will talk more about this in Chapter 13 in the context of scalability.

Reserved keywords

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. So if you want to check against PostgreSQL and MSSQL then your connection string would look as follows:

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

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. For example:

check_reserved=['postgres', 'postgres_nonreserved']

The following database backends support reserved words checking.

Database quoting and case settings

Quoting of SQL entities are enabled by default in DAL, that is:

entity_quoting = True

This way identifiers are automatically quoted in SQL generated by DAL. At SQL level keywords and unquoted identifiers are case insensitive, thus quoting an SQL identifier makes it case sensitive.

Notice that unquoted identifiers should always be folded to lower case by the back-end engine according to SQL standard but not all engines are compliant with this (for example PostgreSQL default folding is upper case).

By default DAL ignores field case too, to change this use:

ignore_field_case = False

To be sure of using the same names in python and in the DB schema, you must arrange for both settings above. Here is an example:

db = DAL(ignore_field_case=False)
db.define_table('table1', Field('column'), Field('COLUMN'))
query = db.table1.COLUMN != db.table1.column

Making a secure connection

Sometimes it is necessary (and advised) to connect to your database using secure connection, especially if your database is not on the same server as your application. In this case you need to pass additional parameters to the database driver. You should refer to database driver documentation for details.

For PostgreSQL with psycopg2 it should look like this:

DAL('postgres://user_name:user_password@server_addr/db_name',
    driver_args={'sslmode': 'require', 'sslrootcert': 'root.crt',
                 'sslcert': 'postgresql.crt', 'sslkey': 'postgresql.key'})

where parameters sslrootcert, sslcert and sslkey should contain the full path to the files. You should refer to PostgreSQL documentation on how to configure PostgreSQL server to accept secure connections.

Other DAL constructor parameters

Database folder location

folder sets the place where migration files will be created (see Migrations section in this chapter for details). It is also used for SQLite databases. Automatically set within web2py. Set a path when using DAL outside web2py.

Default migration settings

The DAL constructor migration settings are booleans affecting defaults and global behaviour.

migrate = True sets default migrate behavior for all tables

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

Experiment with the web2py shell

You can experiment with the DAL API using the web2py shell, that is available using the -S command line option (read more in Chapter 4).

You need to choose an application to run the shell on, mind that database changes may be persistent. So be carefull and do NOT exitate to create a new application for doing testing instead of tampering with an existing one.

Start by creating a connection. For the sake of example, you can use SQLite. Nothing in this discussion changes when you change the back-end engine.

Note that most of the code snippets that contain the python prompt >>> are directly executable via a plain shell, which you can obtain using -PS command line options.