Raw SQL

Timing queries

All queries are automatically timed by web2py. The variable db._timings is a list of tuples. Each tuple contains the raw SQL query as passed to the database driver and the time it took to execute in seconds. This variable can be displayed in views using the toolbar:

{{=response.toolbar()}}

executesql

The DAL allows you to explicitly issue SQL statements.

>>> db.executesql('SELECT * FROM person;')
[(1, u'Massimo'), (2, u'Massimo')]

In this case, the return values are not parsed or transformed by the DAL, and the format depends on the specific database driver. This usage with selects is normally not needed, but it is more common with indexes.

executesql takes five optional arguments: placeholders, as_dict, fields, colnames, and as_ordered_dict.

placeholders is an optional sequence of values to be substituted in or, if supported by the DB driver, a dictionary with keys matching named placeholders in your SQL.

If as_dict is set to True, the results cursor returned by the DB driver will be converted to a sequence of dictionaries keyed with the db field names. Results returned with as_dict = True are the same as those returned when applying .as_list() to a normal select:

[{'field1': val1_row1, 'field2': val2_row1}, {'field1': val1_row2, 'field2': val2_row2}]

as_ordered_dict is pretty much like as_dict but the former ensures that the order of resulting fields (OrderedDict keys) reflect the order on which they are returned from DB driver:

[OrderedDict([('field1', val1_row1), ('field2', val2_row1)]),
 OrderedDict([('field1', val1_row2), ('field2', val2_row2)])]

The fields argument is a list of DAL Field objects that match the fields returned from the DB. The Field objects should be part of one or more Table objects defined on the DAL object. The fields list can include one or more DAL Table objects in addition to or instead of including Field objects, or it can be just a single table (not in a list). In that case, the Field objects will be extracted from the table(s).

Instead of specifying the fields argument, the colnames argument can be specified as a list of field names in tablename.fieldname format. Again, these should represent tables and fields defined on the DAL object.

It is also possible to specify both fields and the associated colnames. In that case, fields can also include DAL Expression objects in addition to Field objects. For Field objects in “fields”, the associated colnames must still be in tablename.fieldname format. For Expression objects in fields, the associated colnames can be any arbitrary labels.

Notice, the DAL Table objects referred to by fields or colnames can be dummy tables and do not have to represent any real tables in the database. Also, note that the fields and colnames must be in the same order as the fields in the results cursor returned from the DB.

_lastsql

Whether SQL was executed manually using executesql or was SQL generated by the DAL, you can always find the SQL code in db._lastsql. This is useful for debugging purposes:

>>> rows = db().select(db.person.ALL)
>>> db._lastsql
SELECT person.id, person.name FROM person;

web2py never generates queries using the “*“ operator. web2py is always explicit when selecting fields.