Metadata-Version: 1.0
Name: cy-Oracle
Version: 0.2
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). 
        
        See 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. 
        
        See 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 mapo between
        Oracle object types and Python classes. 
        
        See 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.
        
        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 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  
        
        
        
Platform: UNKNOWN
