please email this to me and I will take a closer look. Thank you
Brian.
Massimo
On Jul 16, 9:21 pm, Brian M <bmere...@gmail.com> wrote:
> 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
