I wanted to be able to use db.executesql with MS SQL 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 pyodbc module in web2py. With a few quick modifications to gluon/sql.py I had what I needed.

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)

def executesql(self, query):
    self['_lastsql'] = query
    self._execute(query)
    try:
        return self._cursor.fetchall()
    except:
        return None

def executesql2(self, query, args):
    #make self['_lastsql'] tell us the args used too
    self['_lastsql'] = query+"       with ARGS >> "+str(args)
    self._execute(query, args)
    try:
        return self._cursor.fetchall()
    except:
        return None

Now in your controller, you can use db.executesql2 as follows:

results = db.executesql2("""SELECT firstname, lastname, birthdate FROM person WHERE birthdate < ? AND first_name == ?""",dict("Brian", "2009-01-01"))

And in your view you can use

{{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 pyODBC's rowObjects 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?

Yes, you can do this with SQLite too - refer to More Flexible executesql for SQLite

You can probably use similar techniques with other databases as well, just check their documentation.