Table constructor in web2py

Defining Tables Using DAL

In the table constructor in Web2py, tables are defined as define_table.

Signature for define_table

This is the define_table method's signature:

define_table(tablename, *fields, **kwargs)

It accepts an optional number of Field objects and a mandatory table name (even none). Instead of passing a Field object, you can also supply a Table (or subclass) object, which copies and adds all fields to the defining table except the "id." Additionally, available keyword arguments include the following: common filter, rname, redefine, fields, migrate, primarykey, fake migrate, on define, singular, table class, plural, format, polymodel, sequence name, and trigger name, which are covered in more detail below.

For instance:

>>> db.define_table('mobile', Field('model'))
<Table mobile (id, model)>

 

A field (column) called "model" is contained in the "mobile" Table object, which is defined, stored, and returned. You do not need to chase the value produced by define_table because you can also get this object using db.mobile.

id: Notes about the primary key

There is already an "id" field provided by web2py; thus, there is no need to specify one. By default, every table has a field called "id." "id" is the primary key since it is an auto-increment integer field (often starting at 1) used for cross-referencing and to make each record unique. (Note: The ID counter begins at one and is exclusive to the backend. This does not apply, for instance, to Google App Engine NoSQL.)

You can optionally define a field with the type='id' so that web2py can use it as an auto-increment id field. This is not advised unless you're accessing old database tables with a new name for the primary key. Using the primarykey argument, you can utilize several primary keys with restrictions.

singular and plural

The single and plural names of the table constructor in Web2py may be necessary information for smart grid objects. Although the defaults are smart, you can be more precise with these choices.

redefine

Table definitions can only be made once, but you can instruct Web2py to redefine an existing table:

db.define_table('mobile', Field('model'))
db.define_table('mobile', Field('model'), redefine=True)

 

If the table definition changes, the redefinition can cause migration.

format: Representation of a record

It is optional but advised to define a format representation for records containing the format parameter in the table constructor in Web2py.

db.define_table('mobile', Field('model'), format='%(model)s')

 

There are two uses for the format attribute:

• To display referred records in select or option drop-down menus.
• For all fields referencing this table, set the db.othertable.otherfield.represent property. This indicates that SQLTABLE will utilize the format desired representation rather than showing references by id.

rname: Real name

rname gives the table a name in the database backend. As a result, the name of the web2py table is an alias, and the real name utilized to build the backend query is rname. To give just one example, rname can be used to access tables from different databases on the server by providing MSSQL fully qualified table names:

rname = 'db1.dbo.table1'

primarykey: Support for legacy tables

Support for multi-part primary keys in legacy tables is made possible by primarykey.

migrate, fake_migrate

The table's migration options are set by migrate. The migrate value represents the filename where Web2py saves the internal migration data for this table. While the corresponding tables are still present, these files are precious and should never be deleted. 

table_class

import Table from pydal.objects
class MyTable(Table):
    ...

db.define_table(..., table_class=MyTable)

sequence_name

The name of a unique table sequence (if supported by the database). You can construct a SEQUENCE (beginning at one and increasing by 1) or use it with legacy tables with unique sequences.

📋 Take note that Web2py will, by default, create sequences as necessary.

trigger_name 

It is associated with sequence_name. This is vital for some backends that do not enable auto-increment numeric fields.

polymodel

The PolyModel class serves as the superclass for data model definitions that can also be subclasses of other data model specifications. Instances of a PolyModel class or any subclasses may appear as results of a query generated from that class.

polymodel is available through the google.appengine.ext.db.polymodel module.

on_define 

When a lazy table is created using the table constructor in Web2py, a callback called on_define is triggered. If the table is not lazy, anyway, it is still called. As a result, the table can be changed dynamically without losing the benefits of delayed instantiation.

Example:

db = DAL(lazy_tables=True)
db.define_table('mobile',
    Field('model'),
    Field('pricerange', 'brandr'),
    on_define=lambda table: [
        table.model.set_attributes(requires=IS_NOT_EMPTY(), default=''),
 table.pricerange.set_attributes(requires=IS_INT_IN_RANGE(10000,1,20000), default=15000) ])

 

Although on_define is demonstrated in this example, it is not required. The table would still be lazy even if the basic required values were added to the Field definitions. However, requires like IS_IN_DB that take a Set object as their first input will generate a query like

db.sometable.somefield == some value

It would result in an early definition of sometable. By using on define, this situation was restored.

Adding attributes to fields and tables

You can easily add custom attributes to fields in the table constructor in Web2py by following these simple steps:

db.table.field.extra = {}

"extra" is a custom attribute currently associated with the field object; it is not a keyword. You can do that with tables too, but to prevent naming conflicts with fields, they must be preceded by an underscore:

db.table._extra = {}

Frequently Asked Questions

What does "web application framework" mean?

Web resources, web services, and web APIs are all part of web applications developed using a software framework called a web application framework (WAF).

What is a database abstraction layer?

A database abstraction layer is an API (Application Programming Interface) streamlining communication between a computer application and databases like Oracle, SQL Server, MySQL, IBM Db2, and PostgreSQL.

Define API.

A method for two or more computer programs to communicate is the API (Application Programming Interface). It is a kind of software interface that provides support to other software applications.

What makes web2py helpful?

With Web2py, web developers may program dynamic web content in Python. Difficult web development chores, such as creating web forms from scratch, are made easier using Web2py.

How reliable is the web2py framework?

The Web2py framework is easy to use, takes up little space, and has excellent flexibility. Its architecture, design, and compatibility are unrestricted, making it highly dependable.

Conclusion

This article has vividly explained the table constructor in Web2py using the database abstraction layer (DAL), one of the most prominent features of Web2py. Check out blogs  Web2py, Web2py Installation, Web2py application,  Troubleshooting, Table Constructor, Lazy Tables, and capacity planning use cases to upskill your knowledge in the Web2py domain.

Also, visit our Guided Path in  Coding Ninjas Studio to learn about Web2py. If you are preparing for an interview, visit our Interview Experience Section and interview bundle for placement preparations. Upskill yourself in Python, Kivy, Backend Web Technologies, SQL, MongoDB, Data Structures and Algorithms, JavaScript,  System Design, and much more!

You may consider our paid courses to give your career an edge over others!

Do upvote our blogs to help other ninjas grow!

Happy Learning, ninjas!