Metadata-Version: 1.0
Name: cy_Oracle
Version: 0.4.0
Summary: Extension to cx_Oracle to handle Oracle object types
Home-page: UNKNOWN
Author: Nuuk Zweiundvierzig
Author-email: nuuk42@yahoo.com
License: MIT
Description: 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)
                ...
                ...
        
        
Platform: UNKNOWN
