Other operators

web2py has other operators that provide an API to access equivalent SQL operators. Let’s define another table “log” to store security events, their event_time and severity, where the severity is an integer number.

>>> db.define_table('log', Field('event'),
...                        Field('event_time', 'datetime'),
...                        Field('severity', 'integer'))
<Table log (id, event, event_time, severity)>

As before, insert a few events, a “port scan”, an “xss injection” and an “unauthorized login”. For the sake of the example, you can log events with the same event_time but with different severities (1, 2, and 3 respectively).

>>> import datetime
>>> now = datetime.datetime.now()
>>> db.log.insert(event='port scan', event_time=now, severity=1)
1
>>> db.log.insert(event='xss injection', event_time=now, severity=2)
2
>>> db.log.insert(event='unauthorized login', event_time=now, severity=3)
3

like, ilike, regexp, startswith, endswith, contains, upper, lower

Fields have a like operator that you can use to match strings:

>>> for row in db(db.log.event.like('port%')).select():
...     print row.event
... 
port scan

Here “port%” indicates a string starting with “port”. The percent sign character, “%”, is a wild-card character that means “any sequence of characters”.

The like operator maps to the LIKE word in ANSI-SQL. LIKE is case-sensitive in most databases, and depends on the collation of the database itself. The like method is hence case-sensitive but it can be made case-insensitive with

db.mytable.myfield.like('value', case_sensitive=False)

which is the same as using ilike

db.mytable.myfield.ilike('value')

web2py also provides some shortcuts:

db.mytable.myfield.startswith('value')
db.mytable.myfield.endswith('value')
db.mytable.myfield.contains('value')

which are roughly equivalent respectively to

db.mytable.myfield.like('value%')
db.mytable.myfield.like('%value')
db.mytable.myfield.like('%value%')

Remember that contains has a special meaning for list:<type> fields, as discussed in previous list: and contains section.

The contains method can also be passed a list of values and an optional boolean argument all to search for records that contain all values:

db.mytable.myfield.contains(['value1', 'value2'], all=True)

or any value from the list

db.mytable.myfield.contains(['value1', 'value2'], all=False)

There is a also a regexp method that works like the like method but allows regular expression syntax for the look-up expression. It is only supported by MySQL, Oracle, PostgreSQL, SQLite, and MongoDB (with different degree of support).

The upper and lower methods allow you to convert the value of the field to upper or lower case, and you can also combine them with the like operator:

>>> for row in db(db.log.event.upper().like('PORT%')).select():
...     print row.event
... 
port scan

year, month, day, hour, minutes, seconds

The date and datetime fields have day, month and year methods. The datetime and time fields have hour, minutes and seconds methods. Here is an example:

>>> for row in db(db.log.event_time.year() > 2018).select():
...     print row.event
... 
port scan
xss injection
unauthorized login

belongs

The SQL IN operator is realized via the belongs method which returns true when the field value belongs to the specified set (list or tuples):

>>> for row in db(db.log.severity.belongs((1, 2))).select():
...     print row.event
... 
port scan
xss injection

The DAL also allows a nested select as the argument of the belongs operator. The only caveat is that the nested select has to be a _select, not a select, and only one field has to be selected explicitly, the one that defines the set.

>>> bad_days = db(db.log.severity == 3)._select(db.log.event_time)
>>> for row in db(db.log.event_time.belongs(bad_days)).select():
...     print row.severity, row.event
... 
1 port scan
2 xss injection
3 unauthorized login

In those cases where a nested select is required and the look-up field is a reference we can also use a query as argument. For example:

db.define_table('person', Field('name'))
db.define_table('thing',
                Field('name'),
                Field('owner_id', 'reference person'))
db(db.thing.owner_id.belongs(db.person.name == 'Jonathan')).select()

In this case it is obvious that the nested select only needs the field referenced by the db.thing.owner_id field so we do not need the more verbose _select notation.

A nested select can also be used as insert/update value but in this case the syntax is different:

lazy = db(db.person.name == 'Jonathan').nested_select(db.person.id)
db(db.thing.id == 1).update(owner_id = lazy)

In this case lazy is a nested expression that computes the id of person “Jonathan”. The two lines result in one single SQL query.

sum, avg, min, max and len

Previously, you have used the count operator to count records. Similarly, you can use the sum operator to add (sum) the values of a specific field from a group of records. As in the case of count, the result of a sum is retrieved via the storage object:

>>> sum = db.log.severity.sum()
>>> print db().select(sum).first()[sum]
6

You can also use avg, min, and max to the average, minimum, and maximum value respectively for the selected records. For example:

>>> max = db.log.severity.max()
>>> print db().select(max).first()[max]
3

len computes the length of field’s value. It is generally used on string or text fields but depending on the back-end it may still work for other types too (boolean, integer, etc).

>>> for row in db(db.log.event.len() > 13).select():
...     print row.event
... 
unauthorized login

Expressions can be combined to form more complex expressions. For example here we are computing the sum of the length of the event strings in the logs plus one:

>>> exp = (db.log.event.len() + 1).sum()
>>> db().select(exp).first()[exp]
43

Substrings

One can build an expression to refer to a substring. For example, we can group things whose name starts with the same three characters and select only one from each group:

db(db.thing).select(distinct = db.thing.name[:3])

Default values with coalesce and coalesce_zero

There are times when you need to pull a value from database but also need a default values if the value for a record is set to NULL. In SQL there is a function, COALESCE, for this. web2py has an equivalent coalesce method:

>>> db.define_table('sysuser', Field('username'), Field('fullname'))
<Table sysuser (id, username, fullname)>
>>> db.sysuser.insert(username='max', fullname='Max Power')
1
>>> db.sysuser.insert(username='tim', fullname=None)
2
>>> coa = db.sysuser.fullname.coalesce(db.sysuser.username)
>>> for row in db().select(coa):
...     print row[coa]
... 
Max Power
tim

Other times you need to compute a mathematical expression but some fields have a value set to None while it should be zero. coalesce_zero comes to the rescue by defaulting None to zero in the query:

>>> db.define_table('sysuser', Field('username'), Field('points'))
<Table sysuser (id, username, points)>
>>> db.sysuser.insert(username='max', points=10)
1
>>> db.sysuser.insert(username='tim', points=None)
2
>>> exp = db.sysuser.points.coalesce_zero().sum()
>>> db().select(exp).first()[exp]
10
>>> type(exp)
<class 'pydal.objects.Expression'>
>>> print exp
SUM(COALESCE("sysuser"."points",'0'))