Select in Web2Py

Iterator-based Select in Web2Py🔁

This method uses lower memory while performing queries. Testing shows that these queries are 10% faster than others.

Iterators in Python are an example of "lazy evaluation." Traditional Python loops create the whole set of data in memory before looping; these methods "feed" data one step at a time.

The traditional use of select in web2py is:

for row in dBase(dBase.table).select():
   ...

 

But for a large number of rows, using an iterator-based alternative has dramatically lower memory use:

for row in dBase(dBase.table).iterselect():
   ...

Rendering rows using represent📄

You may want to rewrite rows returned by select to take advantage of the formatting information contained in provided represents setting of the fields for Select in Web2Py.

rows = dBase(query).select()  
repr_row = rows.render(0)

 

If you only want to transform the selected fields using their "represent" attribute, you can list them in the "fields" argument:

repr_row = row.render(0, fields=[dBase.mytable.myfield])

 

📝Note: It returns a transformed copy of the original Row so that there's no update_record (which you also wouldn't want anyway) or delete_record in query.

Shortcuts🛡️

✏️The DAL supports various code-simplifying shortcuts for Select in Web2Py. In particular,

myrecord = dBase.mytable[id]

 

returns the record data with the given id if it exists in the table. If the id does not exist, in that case, it returns None.

The above statement is equivalent to

myrecord = dBase(dBase.mytable.id == id).select().first()

✏️You can delete records by id:

del dBase.mytable[id]

This is equivalent to

dBase(dBase.mytable.id == id).delete()

✏️You can insert records:

dBase.mytable[None] = dict(myfield='somevalue')

It is equivalent to

dBase.mytable.insert(myfield='somevalue')

✏️You can update records:

dBase.mytable[id] = dict(myfield='somevalue')

Which is equivalent to

dBase(dBase.mytable.id == id).update(myfield='somevalue')

Fetching a Row

Like dBase.mytable[id], the above syntax is more flexible and safer. First of all, it checks whether the id is an int (or str(id) is an int) and then returns None if not (it never raises an exception). It also allows you to specify multiple conditions the record must meet. If they are not met, then it also returns None.

record = dBase.mytable(id)
record = dBase.mytable(dBase.mytable.id == id)
record = dBase.mytable(id, myfield='somevalue')

Recursive selects

Consider the previous table named person and a new table "thing" referencing a "person":

dBase.define_table('thing',Field('name'),Field('owner_id', 'reference person'))

 

And a simple select from this table:

things = dBase(dBase.thing).select()

 

which is equivalent to

things = dBase(dBase.thing._id != None).select()

 

Where _id is a reference to the table's primary key, normally dBase.thing._id is the same as dBase.thing.id, and we will assume that in most of the queries.

For each Row of things table, it is possible to fetch not just fields from the selected table (thing) but also from the linked tables (recursively):

for thing in things:
   print thing.name, thing.owner_id.name

 

Here thing.owner_id.name requires one database select for each thing in things, which is therefore inefficient. We suggest using the joins whenever it is possible instead of recursive selects. Nevertheless, this is convenient and practical when accessing individual records.

Serializing Rows in views👀

Given the following action containing a query

def index():
   return dict(rows = dBase(query).select())

 

The result of a select can be displayed in a view using the following syntax:

{{extend 'layout.html'}}
<h1>Records</h1>
{{=rows}}

 

Which is equivalent to:

{{extend 'layout.html'}}
<h1>Records</h1>
{{=SQLTABLE(rows)}}

 

SQLTABLE changes the rows into an HTML table with one row per record and a header with column names. Classes "even" and "odd" are alternately noted in the rows. Under the hood, Rows are serialized after being transformed into an SQLTABLE object (not to be confused with Table). The validators connected to the field also format the values taken from the database before escaping. This is an important concept in Select in Web2Py.

💡However, manually calling SQLTABLE is possible and might occasionally be helpful.

The SQLTABLE constructor takes the following optional arguments:

linkto lambda function or an action to be used to link the reference fields (default to None).

If you assign it a string with the name of the action, it will generate a link to that function, passing it, as arguments, the name of the table and the id of each record (in this order) for Select in Web2Py. 

Example:

linkto = 'pointed_function' # generates something like <a href="pointed_function/table_name/id_value">

 

If you want a different link to be generated, you can specify a lambda, which will receive as parameters the value of id, the type of the object (e.g. table), and the name of the object. For example, if you have to receive the args in reverse order for Select in Web2Py:

linkto = lambda id, type, name: URL(f='pointed_function', args=[id, name])

 

upload the URL or provide download action to allow downloading of uploaded files (default to None)

Headers, a dictionary mapping field names to their labels to be used as a header (default to {}). It can also be an instruction. Currently, we support the headers='fieldname:capitalize'.

truncate the number of characters for truncating such long values in the table (default is 16)

columns the list of the fieldnames to be shown as a column (in tablename.fieldname format). Those not listed are not to be displayed (defaults to all).

**attributes of generic helper attributes to be passed to most external TABLE object.

orderby, groupby, limitby, distinct, having, orderby_on_limitby, join, left, cache

These all are the optional attributes for select in Web2Py.

🚀orderby

You can fetch the records which were sorted by name in Select in Web2Py.

>>> for row in dBase().select(dBase.person.ALL, orderby=dBase.person.name):
...     print row.name
...
Amit
Piyush
Karan

🚀groupby, having

Using groupby together with orderby, you can group the records with the same value for a specified field (this is back-end specific and it is not on the Google NoSQL) in Select in Web2Py.

>>> for row in dBase().select(dBase.person.ALL,
...                        orderby=dBase.person.name,
...                        groupby=dBase.person.name):
...     print row.name
...
Amit
Piyush
Karan

🚀distinct

With the distinct argument distinct=True, you can specify that you only want to select the distinct records. This has the same effect as the grouping using all the specified fields, except that it does not require sorting. When using distinct, it is essential not to select ALL fields, and in particular not to select the given "id" field; else, all records will always be distinct for Select in Web2Py.

Here is an example:

>>> for row in dBase().select(dBase.person.name, distinct=True):
...     print row.name
...
Amit
Piyush
Karan

 

Notice that the distinct can also be an expression, for example:

>>> for row in dBase().select(dBase.person.name, distinct=dBase.person.name):
...     print row.name
...
Amit
Piyush
Karan

🚀limitby

With limitby=(min, max) in Select in Web2Py, you can select a subset of the records from the offset=min to but not including offset=max. In the below example, we select the first two records starting at zero.

>>> for row in dBase().select(dBase.person.ALL, limitby=(0, 2)):
...     print row.name
...
Amit
Piyush
Karan

🚀orderby_on_limitby

Note that the DAL defaults to implicitly adding an orderby when using the limitby. This ensures that the same query returns the same results each time, necessary for the pagination. But it can cause performance problems for users. Use orderby_on_limitby = False to alter this whole issue(this defaults to True).

🚀cache, cacheable

An example used that gives much faster selects is:

rows = dBase(query).select(cache=(cache.ram, 3600), cacheable=True)

🚀Logical operators

Queries can be combined using the already present binary AND operator "&" for Select in Web2Py:

>>> rows = dBase((dBase.person.name=='Alex') & (dBase.person.id > 3)).select()
>>> for row in rows: print row.id, row.name
>>> len(rows)
0

🚀count

You can count records in a set:

>>> dBase(dBase.person.Nationality != 'Indian').count()
5

🚀Isempty

>>> dBase(dBase.person).isempty()
False

🚀delete, update

>>> dBase(dBase.person.id > 4).delete()
0

The delete method returns the total number of records that are deleted.

And you can update all records in a set by passing named arguments corresponding to the fields that need to be updated:

>>> dBase(dBase.person.id > 2).update(name='Amrita')
1

The update method returns the total number of records that were updated.

Expressions

The value assigned to an update statement can be an expression. For example, consider the below model.

dBase.define_table('person',Field('name'),Field('visits', 'integer', default=0))
dBase(dBase.person.name == 'Amit').update(visits = dBase.person.visits + 1)

case

An expression can contain a case clause, for example:

>>> condition = dBase.person.name.startswith('B')
>>> yes_or_no = condition.case('Yes', 'No')
>>> for row in dBase().select(dBase.person.name, yes_or_no):
...     print row.person.name, row[yes_or_no]  # could be an row(yes_or_no) too
...
Amit No
Piyush Yes
Karan No

update_record💻

web2py also allows the updating a single record that is already in memory using update_record

>>> row = dBase(dBase.person.id == 2).select().first()
>>> row.update_record(name='Salim')
<Row {'id': 2L, 'name': 'Salim'}>

 

update_record should not be confused with

>>> row.update(name='Salim')

 

Because for a single row, the method update updates only the row object but not the database record of the app, as in the case of update_record.

It is also possible to change the attributes of a row (one at a time) and then call update_record() without arguments to save the changes:

Inserting and updating from a dictionary

A common issue consists of needing to insert or update records in a table where the name of the table, the field to be updated, and the value for the field are all stored in variables. For example

tablename, fieldname, and value.

The insert can be done using the following syntax:

db[tablename].insert(**{fieldname:value})

 

The update of the record with the given id can be done with:

dBase(dBase[tablename]._id == id).update(**{fieldname:value})

Combining rows

Rows of objects can be combined at the Python level. Here we assume:

>>> print rows1
person.name
Maria
John

>>> print rows2
person.name
John
Rohan

 

You can do a union of the records in two sets of rows:

>>> rows3 = rows1 + rows2
>>> print rows3
person.name
Maria
John
John
Rohan

 

You can do a union of the records removing duplicates:

>>> rows3 = rows1 | rows2
>>> print rows3
person.name
Maria
John
Rohan

 

And this finally concludes the Attributes and method of select in Web2Py. Moving towards the FAQs.

Frequently Asked Questions

What is DAL in Python?

Database Abstraction Layer (DAL), a feature of web2py, is an API that converts Python objects into database objects, including queries, tables, and records.

What is SQLite used for?

Desktop applications like version control systems, financial analysis tools, media categorization and editing suites, CAD packages, record-keeping programs, and others frequently use SQLite as the on-disk file format.

What is pyDAL?

An entirely Python database abstraction layer is called pyDAL. The pyDAL module dynamically creates the SQL for the database back end in the chosen language.

What is the difference between SQL and SQLite?

The standard SQL language describes establishing relational structure, entering or updating data in relationships, initiating and terminating transactions, etc. SQLite is File-based. It differs from other SQL databases in that it lacks a distinct server process, unlike most other SQL databases.

MySQL vs SQLite: Which is superior?

MySQL is a superior option if you need scalability regarding the number of database queries needed. MySQL defeats SQLite if you need genuine parallelism, stricter security standards, or the ability to govern user permissions.

Conclusion

In this article, we have extensively discussed, select in Web2Py.

To learn more about Web2Py, see Troubleshooting Web2Py, What is Web2Py, Introduction to Web2Py, Creating a New Application, Prepare the tool. 

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

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!