SQLAlchemy (The Python SQL Toolkit and Object Relational Mapper) allow Oracle connection through the cx_oracle driver. This how-to describes how to install SQLAlchemy for Oracle Database and how to integrate it in buildout and use it in a browser view.

Requirements

  • Python 2.4 or higher
  • easy_install
  • Oracle 9i or higher

SQLAlchemy installation

cx_oracle

See http://cx-oracle.sourceforge.net/ for the good version, depending on your Python and Oracle versions.

$ export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
$ export PATH=$PATH:$ORACLE_HOME/bin
$ path_to_python/bin/easy_install cx_oracle

$ path_to_python/bin/python 
[...]
>>> import cx_Oracle
>>> cx_Oracle
<module 'cx_Oracle' from '[...]/cx_Oracle.so'>

SQLAlchemy

$ path/to/python/bin/easy_install sqlalchemy

$ path_to_python/bin/python 
[...]
>>> import sqlalchemy
>>> sqlalchemy.__version__
'0.5.4'

Simple connection to Oracle with Python

$ path_to_python/bin/python 
[...]
>>> import sqlalchemy
>>> sqlalchemy.create_engine('oracle://username:password@database')
>>> connection = oracle_db.connect()
>>> result = connection.execute("SELECT test_column FROM test_table")
>>> for row in result:
...     print row
... 
('NAME 1',)
('NAME 2',)
[...]
>>> connection.close()

Plone/Zope integration

Integration in buildout.cfg

As cx_oracle and SQLAlchemy are already installed in our python, we don’t need to list them as dependencies in the buildout.cfg file.

However we have to declare in buildout.cfg where are the Oracle binaries:

$ vi buildout.cfg
...
environment-vars =
    ...
    ORACLE_HOME /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
    PATH /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin: 
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
...

A browser view example

$ vi browser.py

# Zope imports
from zope.interface import implements
from Products.Five.browser import BrowserView
from plone.memoize import ram

# Python import
from time import time
import sqlalchemy

# Product imports
from interfaces import IExampleBrowserView
 
class ExampleBrowserView(BrowserView):
    """ See IExampleBrowserView for documentation """

    implements(IExampleBrowserView)

    def __init__(self, context, request):
        self.context = context
        self.request = request
   
    @ram.cache(lambda *args: time() // (60 * 60)) # 60 minutes
    def getOracleParamDatas(self):
        oracle_db = sqlalchemy.create_engine('oracle://username:password@database')
        connection = oracle_db.connect()
       
        datas = []
        try:
            result = connection.execute("SELECT name, value, description FROM params")
            for row in result:
                datas.append({'name': row.name,
                              'value': row.value,
                              'description': row.description})
        finally:
            connection.close()
       
        # Always return simple python data structure for catching purpose
        return datas

Références