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 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 first_name, last_name, birth_date FROM person WHERE birth_date < ? AND first_name == ?""",dict("Brian", "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 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](http://www.web2py.com/AlterEgo/default/show/234)

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

AlterEgo

old web2py blog

Useful Links

Edit page