Other methods in web2py

Introduction

Committing operations on a table in SQL has always been a meddling affair for the programmers, independent of their framework. web2py has a rich section devoted solely to the Database Abstraction Layer, which deals with all the queries related to the interaction with the database. 

While some basic functionalities are present in web2py for fetching data from the rows after imposing some restrictions, there are other additional methods also. In the following article, we will dive deeper into these different methods and look at their usage along with examples.

Other methods

The other methods present in web2py for interacting with tables are-

1. update_or_insert
    
2. validate_and_insert, validate_and_update
    
3. smart_query

update_or_insert

When there is no record with the same data being inserted, we may occasionally need to execute an insert. This is achievable using-

db.define_table('person',
                Field('name'),
                Field('birthplace'))

db.person.update_or_insert(name='John', birthplace='Chicago')

You can also try this code with Online Python Compiler

Run Code

If there isn't another person named John born in Chicago, the table won't enter the record.

We can define which values should be used as a key to check whether a record is present. For instance:

db.person.update_or_insert(db.person.name == 'John',
                           name='John',
                           birthplace='Chicago')

You can also try this code with Online Python Compiler

Run Code

If John is present, the record will update the birth location; otherwise, a new record will be made.

The example above uses a single field as the selection criteria. It might also be a query, such as-

db.person.update_or_insert((db.person.name == 'John') & (db.person.birthplace == 'Chicago'),
                           name='John',
                           birthplace='Chicago',
                           pet='Rover')

You can also try this code with Online Python Compiler

Run Code

validate_and_insert, validate_and_update

The function

ret = db.mytable.validate_and_insert(field='value')

You can also try this code with Online Python Compiler

Run Code

works almost like

id = db.mytable.insert(field='value')

You can also try this code with Online Python Compiler

Run Code

except that before conducting the insert, it calls the field validators and exits if the validation is unsuccessful. The errors can be found in ret.errors if validation fails. Each key in ret.errors is the name of a field whose validation failed, and each key's value is the outcome of the validation error (much like form.errors). If it succeeds, the new record's id can be found in ret.id. Remember that validation is typically handled by the logic that processes the form; thus, this method is rarely required.

Similarly,

ret = db(query).validate_and_update(field='value')

You can also try this code with Online Python Compiler

Run Code

works almost the same as

num = db(query).update(field='value')

You can also try this code with Online Python Compiler

Run Code

except that before executing the change, it calls the field validators. Keep in mind that a single table query is required for it to function. Ret.updated contains the number of updated records, and Ret.errors contain the number of errors.

smart_query (experimental)

Occasionally, it would be best if we parsed a query using natural language, such as-

name contains m and age greater than 18

The DAL gives a method to parse these types of queries:

search = 'name contains m and age greater than 18'
rows = db.smart_query([db.person], search).select()

You can also try this code with Online Python Compiler

Run Code

The first input must be a list of tables or fields that should be permitted in the search. If the search string is incorrect, a RuntimeError is raised. The SQLFORM.grid and SQLFORM.smartgrid both use this feature internally to create RESTful interfaces. The SQLFORM.grid and SQLFORM.smartgrid both use this feature internally to create RESTful interfaces.

A field can be found using the fieldname alone or tablename.fieldname in the smart query search string. Double quotes may be used to separate the words if a string has spaces in it.