Other methods

update_or_insert

Some times you need to perform an insert only if there is no record with the same values as those being inserted. This can be done with

  1. db.define_table('person',
  2.                 Field('name'),
  3.                 Field('birthplace'))
  5. db.person.update_or_insert(name='John', birthplace='Chicago')

The record will be inserted only if there is no other user called John born in Chicago.

You can specify which values to use as a key to determine if the record exists. For example:

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

and if there is John his birthplace will be updated else a new record will be created.

The selection criteria in the example above is a single field. It can also be a query, such as

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

validate_and_insert, validate_and_update

The function

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

works very much like

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

except that it calls the validators for the fields before performing the insert and bails out if the validation does not pass. If validation does not pass the errors can be found in ret.errors. ret.errors holds a key-value mapping where each key is the field name whose validation failed, and the value of the key is the result from the validation error (much like form.errors). If it passes, the id of the new record is in ret.id. Mind that normally validation is done by the form processing logic so this function is rarely needed.

Similarly

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

works very much the same as

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

except that it calls the validators for the fields before performing the update. Notice that it only works if query involves a single table. The number of updated records can be found in ret.updated and errors will be in ret.errors.

smart_query (experimental)

There are times when you need to parse a query using natural language such as

  1. name contains m and age greater than 18

The DAL provides a method to parse this type of queries:

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

The first argument must be a list of tables or fields that should be allowed in the search. It raises a RuntimeError if the search string is invalid. This functionality can be used to build RESTful interfaces (see chapter 10) and it is used internally by the SQLFORM.grid and SQLFORM.smartgrid.

In the smart_query search string, a field can be identified by fieldname only and or by tablename.fieldname. Strings may be delimited by double quotes if they contain spaces.