Proposed patch to sql.py - sqlite3.ROW + alternate executesql

[Brian M]

I've recently started using web2py with some legacy databases (no id
field so no DAL) and would like to offer a patch to gluon/sql.py

1) Adjusts the SQLite connection to use sqlite3.ROW so that the
results of queries run with db.executesql (or my executesql2 below)
can be referred to by field name instead of just index. This lets you
say something like this in your views:

{{for row in results:}}
{{=row['first_name']}} {{=row['last_name']}}
{{pass}}

instead of having to use the less readable

{{for row in results:}}
{{=row[0]}} {{=row[1]}}
{{pass}}

2) Create an alternate executesql2 function that allows custom sql to
be used in conjunction with placeholders. This way you don't have to
worry as much about escaping variables you're using in your query
(avoids sql injections).

With the new function, a third "args" parameter has been added. This
can be either a dictionary or list.

In your controller:

#passing a dictionary (works with SQLite but not MS SQL Server)
results = db.executesql2("""SELECT firstname, lastname, birthdate FROM
person WHERE birthdate < :date AND first_name == :name""",dict(name =
"Fred", date = "2009-01-01"))

#passing a list (works with SQLite and MS SQL Server, possibly others)
results = db.executesql2("""SELECT firstname, lastname, birthdate FROM
person WHERE birthdate < ? AND first_name == ?""", ("Fred",
"2009-01-01"))

In the view:

<p>Results returned from SQLite</p>
{{for row in results:}}
{{=row['first_name']}} {{=row['last_name']}}
{{pass}}

<p>Results returned from MS SQL</p>
{{for row in results:}}
{{=row.first_name}} {{=row.last_name}}
{{pass}}


Here's the patch against the devel bzr branch.

=== modified file 'gluon/sql.py'
--- gluon/sql.py 2009-07-14 13:49:44 +0000
+++ gluon/sql.py 2009-07-17 01:47:13 +0000
@@ -699,6 +699,8 @@
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)
elif self._uri[:8] == 'mysql://':
@@ -1027,6 +1029,14 @@
return self._cursor.fetchall()
except:
return None
+
+ def executesql2(self, query, args):
+ self['_lastsql'] = query+" with "+str(args)
+ self._execute(query, args)
+ try:
+ return self._cursor.fetchall()
+ except:
+ return None

def _update_referenced_by(self, other):
for tablename in self.tables:


These changes make working with non-DAL compliant legacy databases in
web2py a bit easier and do not appear to hinder the normal use of DAL.
I've tested with SQLite and MS SQL Server, but it may also work with
other databases (PostgreSQL, Oracle?)

~Brian

[mdipierro]

please email this to me and I will take a closer look. Thank you
Brian.

Massimo