the you email it to me as an attachament . the indentation is messed up and hard to read.
Massimo On Jun 13, 12:21 am, Mariano Mara <mariano.m...@gmail.com> wrote: > Hi everyone. > > I'm currently trying to migrate a legacy oracle database to web2py and > on the past weeks I stumbled upon several issues related to how the database > was originally designed and how web2py expects to interact with it. > > Among them: > * I need support for ref cursors > * I need to write some complex sql queries that rely on oracle's > advanced functions > * A "bug" (??) on the web2py oracle's implementation (see > http://tinyurl.com/kjs2pk) > > I wanted to be able to interact freely between DAL objects and ad hocs > queries using web2py's ORM syntax. I came up with a subclassed version of > SQLDB that > so far helped me with this and I wanted to share it with everyone hoping > somebody > might find it useful. This might be redundant, > soon-to-be-or-already-obsolete (I have > high hopes on new DALs although I couldn't try it yet)or just a showcase of > bad programming > but since I already spent a few hours with it, I thought I can share :) > > Put it simple, what I did was to create a decorator that, applied to a > method that returns a list of tuples, will convert the result to a SQLRows > object mapped to > a "virtual" table. > This "virtual" table (one for manual queries, one for ref cursors) will > hold the results until a new ad-hoc query is executed. Of course you cannot > use them to > update, delete or insert any records in the db, but if you have a special > query and you > don't want to loose DAL's syntax when iteracting with it, this might help > you. People using > a database other than oracle can make it work with minimal patching. > Following, a full example of a refcursor (in case you don't know what it > is), an ad-hoc query example. Finally, the proper code to make it work. > > --- models/db.py > > db.define_table("Food", db.Field("name", type='string', unique=True), > db.Field("fav_food", type='string')) > db.Food.insert(name='Mariano', fav_food='spaghettini carbonara') > db.Food.insert(name='Gregorio', fav_food='hot dogs') > db.Food.insert(name='Justina', fav_food='hot dogs') > db.commit() > > --- store procedure returning a ref cursor: > > create procedure test_food (food_name in food.fav_food%type, > rc_out out sys_refcursor) > is > begin > open rc_out for > select * from food where fav_food=food_name; > > end test_food; > / > > -- in action: > KAL> variable rc refcursor > KAL> begin test_food('hot dogs', :rc); end; > 2 / > > PL/SQL procedure successfully completed. > > KAL> print rc > 2 Gregorio hot dogs > > 3 Justina hot dogs > > -- accesing it from web2py itself (ipython) and getting SQLRows object: > In [8]: rows = db.executerc("test_food", ['hot dogs']) > > In [9]: for row in rows: print(row.NAME, row.FAV_FOOD) > ....: > ('Gregorio', 'hot dogs') > ('Justina', 'hot dogs') > > -- executing ad hoc queries with executesql and getting SQLRows object: > In [10]: rows = db.executesql("Select name, fav_food from food where > name like 'Mar%'") > > In [11]: for row in rows: print row > ...: > <SQLStorage {'FAV_FOOD': 'spaghettini carbonara', 'NAME': 'Mariano'}> > > In [12]: type(rows) > Out[12]: <class 'gluon.sql.SQLRows'> > > -- my modified db.py > from gluon.sql import SQLDB, SQLRows > > # not sure why I imported these but here they are > from cx_Oracle import BFILE, BINARY, BLOB, CLOB, DATETIME, FIXED_CHAR, > LOB > from cx_Oracle import LONG_BINARY, LONG_STRING, NATIVE_FLOAT, NCLOB, > NUMBER > from cx_Oracle import STRING, TIMESTAMP > > # this is the inverse version of SQL_DIALECTS in gluon/sql.py > # we're mapping oracle's datatypes to web2py's > oracle_dialect = dict( > NUMBER='integer', > STRING='string', > DATETIME='datetime', > BLOB='blob', > CLOB='text', > FIXED_CHAR='boolean', > NATIVE_FLOAT='double', > TIMESTAMP='datetime') > > class mySQLDB(SQLDB): > > def __init__(self, uri='sqlite://dummy.db', pool_size=0, pools=0): > super(mySQLDB, self).__init__(uri, pool_size, pools) > > def buildSQLRow(fnc): > """Decorator to return a SQLRows object. > It will receive the result of the query, transform the db native > datatypes into ones understood by DAL and return the result""" > > def wrapper(self, *args): > rt1, rt2, rt3 = fnc(self, *args) > pargs = [] > try: > self.pop(rt3) > self['tables'].remove(rt3) > except KeyError: > pass > for ev in rt2: > pargs.append(SQLField(ev[0], > oracle_dialect[ev[1].__name__])) > self.define_table(rt3, *tuple(pargs), **dict(migrate=False)) > return SQLRows(self, rt1, *map(lambda x: "%s.%s" % (rt3, > x[0]), > rt2)) > return wrapper > > @buildSQLRow > def executesql(self, query, *args): > """Execute a query and return the result. If you have two > columns with the > same name, make sure you create different aliases for them""" > self['_lastsql'] = query > self._cursor.execute(query, args) > return self._cursor.fetchall(), self._cursor.description, > 'manquery' > > @buildSQLRow > def executerc(self, st_name, parameters): > """Execute a procedure with a ref cursor as result. > In the procedure, the ref cursor output parameter must be the > last one > parameter and the only one out value > Parameters: > st_name: the name of the [package.]procedure to execute > parameters: a list of parameters to pass to the procedure. > You > don't need to pass a ref cursor variable as the method will > append > it by default""" > rc = self._connection.cursor() > parameters.append(rc) > self._cursor.callproc(st_name, parameters) > return rc.fetchall(), rc.description, 'refcursor' > > db = mySQLDB('oracle://kal/aldos...@myora') > > # web2py models stuff... --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
