Connecting to Oracle with SQLAchemy and very simple Plone integration
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