Raw SQL in Web2py

Timing queries

Web2py automatically times each query. There are many tuples in the variable db.timings. The raw Sql query provided to the database driver is contained in each tuple, along with the execution time in seconds. The toolbar can be used to display this variable in views. For example:

{{=response.toolbar()}}

executesql

The DAL (Data Abstraction Layer) explicitly allows you to issue SQL statements. For example:

>>> db.executesql('SELECT * FROM person;')
[(1, u'Massimo'), (2, u'Massimo')]

The DAL does not parse or change the return values in this situation, and the database driver determines the format. This is not generally necessary with selections, but it is more common with indexes.

Executesql accepts five optional arguments: placeholders, as_dict, fields, colnames, and as_ordered_dict.

Placeholders

placeholders is a sequence of values that can be substituted in or, if supported by the DB driver, a dictionary with keys that match named placeholders in your SQL.

as_dict

If we set as_dic to True, the DB driver's results cursor will be converted to a sequence of dictionaries keyed with the db field names. The results returned by as_dict being set to True are the same as those. as_list() to a standard select.

as_ordered_dict

as_ordered_dict is similar to as_dict, but the as_dict assures that the order of the generated fields (OrderedDict keys) corresponds to the order in which the DB driver returns them.

fields

The fields argument is a list of DAL Field objects that correspond to the fields returned by the database. The Field objects should be defined as part of one or more Table objects on the DAL object. The fields list can contain one or more DAL Table objects in addition to or instead of Field objects, or it can have only one table (not in a list). The Field objects will be removed from the table in such instance (s).

colnames

Instead of giving the fields argument, the colnames argument can be given as a list of tablename.fieldname formatted field names. Again, these should represent the DAL object's tables and fields.

Both fields and their related colnames can also be specified. Field objects, in addition to DAL Expression objects, can be included in this situation. The related colnames for Field objects in "fields" must still be in tablename.fieldname format. The linked colnames for Expression objects in fields can be any arbitrary labels.

_lastsql

Whether SQL was run directly using executesql or generated by the DAL, the SQL code may always be seen in db. lastsql. This is beneficial for debugging. As an example,

>>> rows = db().select(db.person.ALL)
>>> db._lastsql
SELECT person.id, person.name FROM person;

drop

Web2py gives the following option for dropping tables and losing all data:

db.person.drop()

Indexes

The DAL(Data Abstraction Layer) API currently lacks a method for creating indexes on tables, although this may be accomplished with the executesql command. This is because indexes might complicate migrations, and it is preferable to deal with them explicitly. Indexes may be required for fields that are often queried.

Here's an example of how to construct an index in SQLite using SQL:

db = DAL('sqlite://storage.sqlite')
db.define_table('person', Field('name'))
db.executesql('CREATE INDEX IF NOT EXISTS myidx ON person (name);')

Other database dialects have similar syntaxes. However, they may not allow the optional "IF NOT EXISTS" directive.

Check out this problem - Shortest Common Supersequence.

Frequently Asked Questions

What is Raw SQL in Web2py?

Raw SQL offers methods for automatically timing queries, issuing SQL statements directly, removing SQL database tables, building SQL indexes, etc.

Define _lastsql.

Whether SQL was run directly using executesql or generated by the DAL, the SQL code may always be seen in db. lastsql. This is beneficial for debugging.

What are placeholders in Web2py?

Placeholders are a sequence of values that can be substituted in or, if supported by the DB driver, a dictionary with keys that match named placeholders in your SQL.

What is Database Abstraction Layer?

A database abstraction layer is an API (Application Programming Interface) that allows a computer application to communicate with databases such as Oracle, SQL Server, MySQL, IBM Db2, and PostgreSQL.

Why web2py?

Server-side web development is easy to learn and lightweight, and fast.

Conclusion

In this article, we have extensively discussed the Raw SQL in Web2py. 

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

Nevertheless, you may consider our paid courses to give your career an edge over others!

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

Happy Learning!