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.


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

SQLAlchemy installation


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'>


$ path/to/python/bin/easy_install sqlalchemy

$ path_to_python/bin/python 
>>> import sqlalchemy
>>> sqlalchemy.__version__

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: 

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 """


    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 = []
            result = connection.execute("SELECT name, value, description FROM params")
            for row in result:
                datas.append({'name': row.name,
                              'value': row.value,
                              'description': row.description})
        # Always return simple python data structure for catching purpose
        return datas