cy-Oracle 0.2

Extension to cx_Oracle to handle Oracle object types

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