[DB-SIG] python-oracle queries

Bob Gailer bgailer at alum.rpi.edu
Mon Apr 21 10:25:23 EDT 2003


At 06:57 AM 4/20/2003 -0700, vishnu mahendra wrote:
>Oracle:

I use cxOracle instead of ODBC; here's some of my wrapper class:

import cx_Oracle
class Oracle:

   def __init__(self, conn_str):
     self.conn_str = conn_str
     self.conn = cx_Oracle.connect(self.conn_str)
     self.c = self.conn.cursor()

   def setp(self, pm):
     if pm:
       self.out_names = [p[0] for p in pm]
       list = [p[0] + ' = ' + p[1] for p in pm]
       pl = 'self.c.setinputsizes(' + ', '.join(list) + ')'
       return eval(pl)
     else:
       self.out_names = []
       return {}

   def exp(self, st, outparm = [], **indict): ## ex, adding parameters
     self.outparms = self.setp(outparm)
     self.c.execute(st, **indict)
     return self.getp()

   def getp(self): # return out parameter values
     return [self.outparms[pn].getvalue() for pn in self.out_names]

   def select(self, st, **indict): # ex select statement; fetch results
     try:self.c.execute(st, **indict)
     except cx_Oracle.DatabaseError:return 
traceback.format_exception_only(sys.exc_info()[0],sys.exc_info()[1])
     return self.c.fetchall()

conn - Oracle('asdf/asdf at asdf')

>how do I execute procedures and functions

x = conn.exp("declare p number; begin :p := 3; end;", 
[['p','cx_Oracle.NUMBER']])

It has been a while since I've worked with this code, so I'm not 100% sure 
of it,but I think with a little fiddling you could get it to work.

>can we pass parameters in queries?

sql = "select * from PER_PERSON_ANALYSES where person_id = :p1 and 
id_flex_num = :p2 "
x = conn.select(sql, person_id, id_flex_num)

I think that x now is a list of result rows.

>can we access results of a query by fieldnames?

You'd have to create your own map.

>how can we change date format so that we can store and retrieve date in 
>'03-aug-03' format.

I think you can use the oracle DATE function to convert date formats.

Bob Gailer
bgailer at alum.rpi.edu
303 442 2625
-------------- next part --------------

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.467 / Virus Database: 266 - Release Date: 4/1/2003


More information about the DB-SIG mailing list