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)

elif not isjdbc and self.uri[:9] == 'sqlite://': self.dbname = 'sqlite' pathencoding = sys.getfilesystemencoding() or \ locale.getdefaultlocale()[1] if uri[9] != '/': dbpath = os.path.join( self.folder.decode(pathencoding).encode('utf8'), uri[9:]) else: dbpath = uri[9:] self.poolconnection(lambda : sqlite3.Connection(dbpath, checksamethread=False)) self.connection.createfunction('web2pyextract', 2, sqlite3web2pyextract) self.cursor = self.connection.cursor() self.execute = lambda a, *b: self._cursor.execute(a, *b)

and change it to read

elif not isjdbc and self.uri[:9] == 'sqlite://': self.dbname = 'sqlite' pathencoding = sys.getfilesystemencoding() or \ locale.getdefaultlocale()[1] if uri[9] != '/': dbpath = os.path.join( self.folder.decode(pathencoding).encode('utf8'), uri[9:]) else: dbpath = uri[9:] self.poolconnection(lambda : sqlite3.Connection(dbpath, checksamethread=False)) self.connection.createfunction('web2pyextract', 2, sqlite3web2pyextract) #make it so we can get results as a dictionary when using executesql self.connection.rowfactory = sqlite3.Row self.cursor = self.connection.cursor() self.execute = lambda a, *b: self._cursor.execute(a, *b)

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)

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 < :date AND first_name == :name""",dict(name = "Fred", date = "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 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.