Some of the information here may be outdated, please check the book instead

Edit page

Title:

Security Code:

Body: (use this wiki markup)

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](http://docs.python.org/library/sqlite3.html#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 is_jdbc and self._uri[:9] == 'sqlite://':
            self._dbname = 'sqlite'
            path_encoding = sys.getfilesystemencoding() or \
                locale.getdefaultlocale()[1]
            if uri[9] != '/':
                dbpath = os.path.join(
                  self._folder.decode(path_encoding).encode('utf8'),
                  uri[9:])
            else:
                dbpath = uri[9:]
            self._pool_connection(lambda : sqlite3.Connection(dbpath,
                                           check_same_thread=False))
            self._connection.create_function('web2py_extract', 2,
                                             sqlite3_web2py_extract)
            self._cursor = self._connection.cursor()
            self._execute = lambda *a, **b: self._cursor.execute(*a, **b)

and change it to read

elif not is_jdbc and self._uri[:9] == 'sqlite://':
            self._dbname = 'sqlite'
            path_encoding = sys.getfilesystemencoding() or \
                locale.getdefaultlocale()[1]
            if uri[9] != '/':
                dbpath = os.path.join(
                  self._folder.decode(path_encoding).encode('utf8'),
                  uri[9:])
            else:
                dbpath = uri[9:]
            self._pool_connection(lambda : sqlite3.Connection(dbpath,
                                           check_same_thread=False))
            self._connection.create_function('web2py_extract', 2,
                                             sqlite3_web2py_extract)
            #make it so we can get results as a dictionary when using executesql
            self._connection.row_factory = 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.row_factory = 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 first_name, last_name, birth_date FROM person WHERE birth_date < :date AND first_name == :name""",dict(name = "Fred", date = "2009-01-01"))

And in your view you can use

{{for person in results:}}
{{=person['last_name']}}, {{=person['first_name']}} 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.

AlterEgo

old web2py blog

Useful Links

Edit page