please email this to me and I will take a closer look. Thank you Brian. Massimo
On Jul 16, 9:21 pm, Brian M <[email protected]> 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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
