I wanted to be able to use db.executesql with sqlite but was missing the ability to add placeholders to my queries and refer to columns in the result by name. Basically, I just wanted to be able to use the standard sqlite3 module in web2py. With a few quick modifications to gluon/sql.py I had what I needed.

Enable sqlite3.Row

sqlite3.Row lets you refer to a column by name. So instead of having to use {{=row[0]}} you can use the much clearer {{=row['first_name']}}

In Class SQLDB(SQLStorage) find def init and then the section that reads as follows (In web2py 1.64.4 it was lines 681 - 696)

The change is simply adding
#make it so we can get results as a dictionary when using executesql
self.connection.rowfactory = sqlite3.Row
between lines 694 and 695

Make an alternate executesql that accepts placeholders

Now still in gluon/sql.py find the section where def executesql(self,query) is defined. In web2py v1.64.4 this was line 1006. Copy the entire function and then paste it in after the end of the function. rename it to executesql2(self, query, args)

{{for person in results:}}
{{=person['lastname']}}, {{=person['firstname']}} was born on {{=person['birth_date']}}
{{pass}}

Why do this?

Sure the example query above would be easy to do with web2py's DAL, but if you are doing more complex queries, working with legacy databases that don't follow web2py's conventions (no id field?), or just plain like writing sql, then writing your own sql can be very helpful. Enabling the use of placeholders helps avoid sql injections, etc. Using sqlite3.Row lets you refer to the database results in a much more natural (and readable) manner than having to use truple indexes.

The above modifications do not break the rest of web2py's DAL.

Does it work with other databases?

No, this just works with sqlite. I'm pretty sure that you could make similar modifications that would work with your database of choice though. Just look though your database's DB-API docs and look for similar options.