Web2py - Database Abstraction Layer

The Database Abstraction Layer (DAL) is considered as the major strength of web2py. The DAL exposes a simple Applications Programming Interface (API) to the underlying SQL syntax.
In this chapter, we will get to know the non-trivial applications of DAL, such as building queries to search by tags efficiently and building a hierarchical category tree.
Some important features of DAL are −

• web2py includes a Database Abstraction Layer (DAL), an API which maps Python objects into database objects. The database objects can be queries, tables and records.
• The DAL dynamically generates the SQL in real time using the specified dialect for the database back end, so that it is not mandatory for a developer to write complete SQL query.
• The major advantage of using DAL is that the applications will be portable with different kinds of databases.

Getting Started with DAL

Most applications in web2py require a database connection. Therefore, building the database model is the first step in the design of an application.
Consider the newly created application named “helloWorld”. The database is implemented under the Models of the application. All the models for the respective application are comprised under file named − models/db_custom.py.
The following steps are used for implementing DAL −

Step 1 - DAL Constructor

Establish a database connection. This is created using DAL object which is also called the DAL constructor.

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

The notable feature of DAL is that it allows multiple connections with the same database or with different databases, even with different types of database. It is observed that this line is already in the file models/db.py. Therefore, you may not need it, unless you deleted it or need to connect to a different database. By default, web2py connects to a SQLite database stored in file storage.sqlite.
This file is located in the application's databases folder. If the file is absent, it is created by web2py when the application is first executed.
SQLite is fast, and stores all the data in one single file. This means that your data can be easily transferred from one application to another. In fact, the SQLite database(s) are packaged by web2py together with the applications. It provides full SQL support, including translations, joins, and aggregates.
There are two disadvantages of SQLite.

• One is that it does not enforce column types, and there is no ALTER TABLE except for adding and dropping columns.
• The other disadvantage is that the entire database is locked by any transaction that requires write access.

Step 2 - Table Constructor

Once the connection with database is established, we can use the define_table method to define new tables.
For example −

db.define_table('invoice',Field('name'))

The above method is also used among Table constructor. The syntax for the table constructor is the same. The first argument is the table name, and it is followed by a list of Field(s). The field constructor takes the following arguments −

Sr.No	Arguments & Usage
1	The field name Name of the field in table.
2	The field type takes values having any of the datatypes such as string (default), text, boolean, integer and so on.
3	Length Defines the maximum length.
4	default = None This is the default value when a new record is inserted.
5	update = None This works the same as default, but the value is used only on update, not on insert.
6	Notnull This specifies whether the field value can be NULL or not.
7	readable = True This specifies whether the field is readable in forms or not.
8	writable = True This specifies whether the field is writable in forms or not.
9	label = "Field Name" This is the label to be used for this field in forms.

The define_table method also takes three named arguments −

Syntax

db.define_table('....',migrate=True, fake_migrate=False, format = '%(id)s')

• migrate = True − This instructs web2py to create the table if it does not exist, or alter it if it does not match the model definition.
• fake_migrate = False − If the model matches the database table content, then set fake_migrate = True which helps web2py to rebuild a data.
• format = '%(id)s' − This is a format string that determines how records on the given table should be represented.

Generating Raw SQL

Using DAL, we can establish a connection to database and create new tables and their fields using the table constructor and field constructor.
Sometimes, it is necessary to generate SQL statements to conform to the necessary output. web2py includes various functions, which help in generating raw SQL, which are given as follows −

_insert

It helps in fetching insert statements for the given table. For example,

print db.person._insert(name ='ABC')

It will retrieve the insert statement for table named “person”.
SQL statement output −

INSERT INTO person(name) VALUES ('ABC');

_count

It helps in fetching SQL statement, which gives the count of records. For example, consider a table named ‘person’ and we need to find the count of persons with name ‘ABC’.

print db(db.person.name ==' ABC ')._count()

SQL statement output −

SELECT count(*) FROM person WHERE person.name = ' ABC ';

_select

It helps in fetching select SQL statements. For example, consider a table named ‘person’ and we need to find the list of persons with name ‘ABC’.

print db(db.person.name == ' ABC ')._select()

SQL statement output −

SELECT person.name FROM person WHERE person.name = ' ABC ';

_delete

It helps in fetching the delete SQL statements. For example, consider for table named ‘person’ and we need to delete the statements with name ‘ABC’

print db(db.person.name == ' ABC ')._delete()

SQL statement output −

DELETE FROM person WHERE person.name = ' ABC ';4

_update

It helps in fetching updated SQL statements. For example, consider for table named ‘person’ and we need to update a column name with some other value.

print db(db.person.name == ' ABC ')._update()

SQL statement output −

UPDATE person SET WHERE person.name = ’Alex’;

Issues with DAL (Gotchas)

SQLite

SQLite lacks the support of dropping or altering the columns. Deleting a field from the table keeps it active in the database, due to which web2py will not be aware of any changes made.
In this case, it is necessary to set the fake_migrate = True which will help to redefine the metadata such that any changes such as alter or delete will be kept under the knowledge of web2py.
SQLite does not support Boolean types. For this, web2py internally maps the Booleans to 1 character string, with 'T' and 'F' representing true and False respectively.

MySQL

MySQL does not support ALTER TABLE feature. Thus, migration of database involves multiple commits. This situation can be avoided by setting the parameter fake_migrate = True while defining the database, which will persist all the metadata.

Oracle

Oracle does not support the feature of pagination of records. It also lacks the support for the keywords OFFSET or limit. For this, web2py achieves pagination with the help of a complex three-way nested select of DAL. DAL needs to handle pagination on its own, if Oracle database has been used.