cy-Oracle 0.4.0
Extension to cx_Oracle to handle Oracle object types
License: MIT

cy_Oracle
=========

cy_Oracle is a simple Python module that improves the handling of
Oracle object types compared to cx_Oracle.
cy_Oracle supports a central declaration of a mapping between
Python classes and Oracle object types. This mapping can be
used in DML (SELECT, UPDATE, INSERT) as well as with PL/SQL
functions and procedures. Details are described in the online help
for the function "addTypemap".

>>> import cy_Oracle
>>> help(cy_Oracle.OracleDB.addTypemap)

The entry point for the client is the class cy_Oracle.OracleDB.
Here you define the connection parameter to the Oracle database
(User, Password, URL). 

>>> import cy_Oracle
>>> help(cy_Oracle.OracleDB) for details.

The class cy_Oracle.Connection derives from cx_Oracle.Connection.
It does not add any new methods, but override the method "cursor()".
Here we return an object of the class cy_Oracle.Cursor. 

>>> import cy_Oracle
>>> help(cy_Oracle.Connection.cursor) for details.

The class cy_Oracle.Cursor derives from cx_Oracle.Cursor.
Here we setup the input and output handlers to map between
Oracle object types and Python classes. 

>>> import cy_Oracle
>>> help(cy_Oracle.Cursor) for details.

Example 1
---------

This 'Hello World' example for the module cy_Oracle will 
list the names and the creation date of objects in the 
database schema "book"::

  1    from cy_Oracle import OracleDB
  2    # setup the DB connection
  3    myDB = OracleDB(user='book', password='book', databaseURL='bookdb')
  4    # connect to DB
  5    con = myDB.connect()
  6    # select some data from the view 'USER_OBJECTS'
  7    cur = con.cursor()
  8    cur.execute('SELECT object_name, created from user_objects')
  9    # loop the result
  10   for row in cur:
  11           print ('%s --> %s' % (row.object_name, row.created))
  12   # close down
  13   cur.close()	
  14   con.close()

In this example, cy_Oracle works just as cx_Oracle - the only difference
is line 11. Here, cy_Oracle gives you an object with names of the selected
columns as attributes whereas in cx_Oracle you will just get a collection.

The example needs a TNS-entry (here: bookdb) to locate the host of the 
database server. As an alternative, it is possible to include the host,
the port an the service name into the connection::

  3    myDB = OracleDB(databaseURL='user/password@dbhost:dbport/servicename')


Example 2
---------

Oracle object types are mostly used in complex database application
that are build from a mixture of SQL and PL/SQL. So, this "simple" 
example using object types is one that is not very useful. It will
list all columns of all tables an views in the database schema "book"::

  1   from cy_Oracle import OracleDB
  2   from cy_Oracle import Attribute
  3
  4   # class to receive the result - one object of type T_COL_INFO.
  5   class ColInfo(object):
  6           def __init__(self):
  7                   self.name=None
  8                   self.oracleDataType=None
  9                   self.oracleDataLenght=None
  10         
  11         def __str__(self):
  12                 return 'name=%s type=%s(%d)' % \
  13                         (self.name,self.oracleDataType,self.oracleDataLenght)
  14
  15  myDB = OracleDB(user='book', password='book', databaseURL='orcl')
  16  con = myDB.connect()
  17  cur = con.cursor()
  18  # Create a new object type in the database
  19  cur.execute(""" create or replace type T_COL_INFO as object(
  20                   NAME	      varchar2(85 char)
  21                  ,DATA_TYPE      varchar2(106 char)
  22                  ,DATA_LENGTH    number
  23                  ,COMMENTS       varchar2(4000 char))
  24              """)
  25  # create a view containing objects of the type T_COL_INFO
  26  cur.execute(""" create or replace view V_COL_INFO as
  27                    select 
  28                       c.TABLE_NAME as TABLE_NAME  
  29                      ,T_COL_INFO(c.COLUMN_NAME,c.DATA_TYPE,c.DATA_LENGTH,m.COMMENTS) 
  30                       as COL_INFO
  31                    from USER_TAB_COLUMNS c ,USER_COL_COMMENTS m
  32                    where c.COLUMN_NAME = m.COLUMN_NAME and c.TABLE_NAME = m.TABLE_NAME
  33              """)
  34  # Create a type-mapping between T_COL_INFO and the Python object ColInfo
  35  myDB.addTypemap(oracleTypename='T_COL_INFO',pythonClass=ColInfo,attrDict={
  36          'name' 			: Attribute('NAME'),
  37          'oracleDataType'	        : Attribute('DATA_TYPE'),
  38          'oracleDataLenght'	: Attribute('DATA_LENGTH'),
  39          })
  40  # select some objects from the database
  41  for row in cur.execute("""  select TABLE_NAME,COL_INFO from V_COL_INFO order by 1 """):
  42   print 'Table::%s "%s"::%s' % \
  43   (row.table_name,row.col_info.__class__.__name__,row.col_info)
  43  cur.close()	
  44  con.close()

First of all, we need a Python class to receive the data from the 
Oracle object type. In line 5, we create the class "ColInfo". To
use the class with cy_Oracle, it needs a constructor without 
parameters an "object" somewhere in its base classes.

Now, we need an Oracle object type. We are using the DDL-statement in
line 19 to create the type T_COL_INFO. It stores information about one colunm
of a table or a view in the database.

Next, we want to be able to receive objects of the type T_COL_INFO from the database.
In this example, we are creating the database view V_COL_INFO that contains this object 
in one of its columns - see line 26.

The interesting part of this example the code in line 35. In cx_Oracle, a SELECT
from the view V_COL_INFO would return an object of the Python class cx_Oracle.Object.
This is a generic class, used for all Oracle object types with the following
advantages and disadvantages:

* it is generic and contains all attributes of the Oracle object
* all instances become inaccessable when closing the database connection  
* you can not derive your own Python class from cx_Oracle.Object

With the method "addTypemap" we tell cy_Oracle that:

* we want an object of the Python class "ColInfo" each time a Oracle object
  of the object type "T_COL_INFO" shows up 
* we want an Oracle object of the object type "T_COL_INFO" each time a Python
  object of the class "ColInfo" shows up
* we tell cy_Oracle how the attributes of the two types are mapped. 


The output from the example depends on the objects in your database schema, but
it should be something like this::

        Table::BOOK "ColInfo"::name=BK_ISBN type=NUMBER(22)
        Table::BOOK "ColInfo"::name=BK_BOOK type=T_BOOK(1)

It shows, that:

* we got an object of the Python class "ColInfo" for each entry in view
  "V_COL_INFO".
* We can add or override methods in "ColInfo" as usual  

Example 3
---------

Oracle object types can be build as a composition using other object types.
The following example shows how cy_Oracle handles compositions and collections
of objects.

We are extending the example 2 to get one Python object for each table in our
schema containing:

* the name of the table
* the remaining free space in the table
* a collection of "ColInfo" (see Example 2) objects describing each of the columns.  

The exmple also shows that cy_Oracle is able to deal with composit Object
object types::

 1  """ Example using Oracle Object Types
 2  
 3  This example shows how to map composit
 4  Oracle object types as well collections
 5  to Python objects using cy_Oracle.
 6  """
 7  from cy_Oracle import OracleDB
 8  from cy_Oracle import Attribute
 9  from cy_Oracle import Collection
 10  
 11  # class to receive the result - one object of type T_COL_INFO.
 12  class ColInfo(object):
 13  	def __init__(self):
 14  		self.name=None
 15  		self.oracleDataType=None
 16  		self.oracleDataLenght=None
 17  	def __str__(self):
 18  		return 'name=%s type=%s(%d)' % \
 19  			(self.name,self.oracleDataType,self.oracleDataLenght)
 20  
 21  # Class the receive the result - one object of type T_TAB_INFO
 22  class TabInfo(object):
 23  	def __init__(self):
 24  		self.name=None
 25  		self.pctFree=None
 26  		self.colInfo=None
 27  	def __str__(self):
 28  		result = 'name=%s free=%d(%%)' % (self.name,self.pctFree)
 29  		for col in self.colInfo:
 30  			result = '%s\n    %s' % (result,col)
 31  		return result
 32  
 33  myDB = OracleDB(user='book', password='book', databaseURL='bookdb')
 34  con = myDB.connect()
 35  cur = con.cursor()
 36  
 37  # to be able to run this program more then once we must drop the existing types
 38  try:
 39  	cur.execute(""" drop type T_TAB_INFO """)
 40  except:
 41  	pass
 42  try:
 43  	cur.execute(""" drop type T_COL_INFO_NT """)
 44  except:
 45  	pass
 46  
 47  # Create a object type in the database
 48  cur.execute(""" create or replace type T_COL_INFO as object(
 49  		 NAME		varchar2(85 char)
 50  		,DATA_TYPE      varchar2(106 char)
 51  		,DATA_LENGTH	number
 52  		,COMMENTS       varchar2(4000 char))
 53  	    """)
 54  # Create a collection type in the database
 55  cur.execute(""" create or replace type T_COL_INFO_NT as table of T_COL_INFO """)
 56  # Create the type that describes one table 
 57  cur.execute(""" create or replace type T_TAB_INFO as object(
 58  		 NAME varchar2(30 char),
 59  		 PCT_FREE number,
 60  		 COL_INFO T_COL_INFO_NT)
 61  	    """)
 62  # create a view containing objects of the type T_COL_INFO
 63  cur.execute(""" create or replace view V_TAB_INFO as 
 64                  select T_TAB_INFO(
 65                     t.TABLE_NAME 
 66                    ,t.PCT_FREE
 67                    ,cast(MULTISET( 
 68  	                SELECT T_COL_INFO(c.COLUMN_NAME,c.DATA_TYPE,c.DATA_LENGTH
 69  			,m.COMMENTS) from 
 70  	                USER_TAB_COLUMNS c,USER_COL_COMMENTS m
 71  	                where c.TABLE_NAME=t.TABLE_NAME and c.TABLE_NAME=m.TABLE_NAME)
 72                     as T_COL_INFO_NT )
 73                  ) as TAB_INFO
 74                  from user_tables t
 75              """)
 76  
 77  # Create a type-mapping between T_COL_INFO and the Python object ColInfo
 78  myDB.addTypemap(oracleTypename='T_COL_INFO',pythonClass=ColInfo,attrDict={
 79  	'name' 			: Attribute('NAME'),
 80  	'oracleDataType'	: Attribute('DATA_TYPE'),
 81  	'oracleDataLenght'	: Attribute('DATA_LENGTH'),
 82  	})
 83  # Create a type-mapping between T_TAB_INFO and the Python object ColInfo
 84  myDB.addTypemap(oracleTypename='T_TAB_INFO',pythonClass=TabInfo,attrDict={
 85  	'name' 			: Attribute('NAME'),
 86  	'pctFree'		: Attribute('PCT_FREE'),
 87  	'colInfo'		: Collection('COL_INFO','T_COL_INFO_NT')
 88  	})
 89  
 90  # select some objects from the database
 91  for row in cur.execute("""  select TAB_INFO  from V_TAB_INFO """):
 92  	print '\n%s' %  (row.tab_info)
 93  cur.close()	
 94  con.close()


The output from the example depends on the objects in your database schema, but
it should be something like this::

        name=BOOK free=10(%)
            name=BK_BOOK type=T_BOOK(1)
            name=BK_ISBN type=NUMBER(22)
            name=BK_BOOK type=T_BOOK(1)
            name=BK_ISBN type=NUMBER(22)
        ...
        ...