List Type and Contains and Operators in Web2py

Example

We can use list:string in the following way:

>>> db.define_table('product',
                 Field('name'),
                Field('colors', 'list:string'))
<Table product (id, name, colors)>
>>> db.product.colors.requires = IS_IN_SET(('red', 'blue', 'green'))
>>> db.product.insert(name='Toy Car', colors=['red', 'green'])
1
>>> products = db(db.product.colors.contains('red')).select()
>>> for an item in products:
       print item.name, item.colors

Output

Toy Car ['red', 'green']

Let us see another example of how to use list:reference and contains(value). The list:reference and contains(value) operators are beneficial for de-normalizing many-to-many relationships.

>>> db.define_table('tag',Field('name'),format='%(name)s')
<Table tag (id, name)>
>>> db.define_table('product',Field('name'),Field('tags', 'list:reference tag'))
<Table product (id, name, tags)>
>>> a = db.tag.insert(name='red')
>>> b = db.tag.insert(name='green')
>>> c = db.tag.insert(name='blue')
>>> db.product.insert(name='Toy Car', tags=[a, b, c])
1
>>> products = db(db.product.tags.contains(b)).select()
>>> for an item in products:
      print item.name, item.tags
Toy Car [1, 2, 3]
>>> for an item in products:
       print item.name, db.product.tags.represent(item.tags)

Output

Toy Car red, green, blue

Operators in Web2py🧐

Operators in web2py give an API to access analogous SQL operators. Let's create a table called "log" to keep track of security events, their event time, and severity, where 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)>

Various Operators🎯

Following are the various operators provided by Web2py:

• like: We can use the like operator in fields to match strings. For example:
   

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

• ilike: In most databases, like is case-sensitive and is based on the database's collation. We can use ilike when we want the matching not to be case-sensitive. For example:
   

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

• regexp: There is also a regexp method, which operates similarly to the like method but accepts regular expression syntax for the look-up expression. MySQL, Oracle, PostgreSQL, SQLite, and MongoDB are the only databases that support it (with different degrees of support).
• contains: The contains method is used to search for a given value in the database, For example:
   

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

The contains method is also used to search for records that contain all values, send a list of values and an optional boolean argument all to the contains method. For example:

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

or any of the values from the list

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

• upper, lower: The upper and lower methods transform the field value to upper or lower case, and they can be combined with the like operator:
   

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

Output
port scan

 

• year, month, day, hour, minutes, seconds: day, month, and year methods are available in the date and DateTime fields. hour, minute, and second options are open in the DateTime and time fields. Here's an example:
   

>>> for row in db(db.log.event_time.year() > 2018).select():
       print row.event

Output
port scan
xss injection
unauthorized login

 

• belongs: The belongs function, which returns true when the field value belongs to the given set (list or tuples), implements the SQL IN operator:
   

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

Output
port scan
xss injection

 

• sum: To add (sum) the values of a particular field from a collection of records, we can use the sum operator. The storage object can be used to get a sum's result. For example:
   

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

Output
6

 

• avg, min, max: We may also utilise avg, min, and max to get average, minimum, and maximum values for the chosen records. For example:
   

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

Output
3
 

• len: It determines the value of the field's length. Although it typically only works with text or string fields, other sorts of fields might also be compatible, depending on the back end (boolean, integer, etc.). For example:
   

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

Output
unauthorized login

 

• startswith, endswith: Additionally, web2py gives specific shortcuts:
   

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

The above shortcuts are equivalent to:

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

Substrings👨🏻‍💻

A substring can be referred to using an expression. For instance, we can group items whose names begin with the same three characters and choose only one from each group. For example:

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

Default Values with coalesce and coalesce_zero👇🏻

There are occasions when you need to retrieve a value from the database but also need default values if the value for a record is NULL. Coalesce is a SQL function that does this. There is an equivalent coalesce method in web2py.

For example:

>>> 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

 

Sometimes you need to compute a mathematical equation, however certain fields have the value None when it should be zero. Coalesce zero saves the day by setting None to zero in the query.

For example:

>>> 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'))

Frequently Asked Questions

What is the list type in Web2py?

Web2py supports particular field types, which can hold lists of strings, numbers, and references.

What is the use of operators in Web2py?

Operators in web2py give an API to access analogous SQL operators.

Define ilike operator in Web2py?

Most databases like case-sensitive and based on the database's collation. We can use ilike when we want the matching not to be case-sensitive.

What is web2py?

The Python programming language was used to create the free and open-source Web2py web application framework.

Is web2py an MVC model?

The development of web2py was influenced by the Ruby on Rails and Django frameworks. In that it stresses rapid growth, favours convention over configuration, and follows the model-view-controller (MVC) architecture paradigm, Web2py is comparable to these frameworks.

Conclusion

In this article, we have extensively discussed the List type, contains, and operators in Web2py. 

Do you not feel eager to read/explore additional information on the subject of Web2py after reading about the List type and contains and operators in Web2py? See the Web2py, Web2Py init,  Application creation, Web2Py Installation, and Troubleshooting to learn more.

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enrol in our courses and refer to the mock test and problems available. Take a look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow. 

Happy Learning Ninja!🥷