Developer: Open Source
   DOWNLOAD
 Oracle Database XE
 Python
 cx_Oracle
   TAGS
opensource, xe, webdev, All

Wrapping Your Brain Around Oracle + Python


by Catherine Devlin

Learn the aspects of Python most important for Oracle users.

Published March 2006

 Comments about this article? Visit the Python Discussion Forum

PL/SQL is ideal for programming tasks within Oracle Database. Most Oracle professionals, however, aren't confined to working strictly within the database itself. You've probably encountered many tasks for which PL/SQL has proven clumsy or even unusable—such as manipulating files on disk, invoking external programs or shell scripts, doing complex text parsing, and doing heavily object-oriented work. If PL/SQL is your only programming language, your Oracle work may suffer.

But which second language should you choose? You probably don't have much time to spare, so it should be an easy language—easy to learn as well as to use. The dynamic languages—especially Perl, Python, PHP, and Ruby—have attracted ever increasing attention, precisely for their ease of use. Many users of dynamic languages find that they can write working code many times as fast as they could in more-traditional languages such as C++ or Java.

Python, in particular, is not just easy to use but also easy to use well, producing code that is readable and well organized. This way, when you return to a piece of code months after it was written, you can understand it, modify it, and reuse it. Python's clean, elegant syntax is sometimes called "executable pseudocode," for its nearly self-documenting appearance. It is highly object-oriented and makes it easy to learn and follow good programming style, even for those of us without formal training in software engineering. Its smooth learning curve makes it appeal to novices and experts alike.

Like Perl's, Python's capabilities span the whole range of software needs; the language's simplicity doesn't imply shallowness or narrowness. You won't run up against gaps in Python's abilities that send you looking for a third language.

Finally, Python is open-source, cross-platform, and free of cost. There's no excuse not to give Python a try!

Rather than try to introduce Python thoroughly or teach it rigorously, this article jumps right into creating a useful sample application while demonstrating the aspects of Python most important for Oracle users. If it convinces you that Python is worth checking out further, links throughout the article can guide you to more information.

Getting Started

If you're running Linux, you probably already have Python; it's packaged with popular distributions. Otherwise, download it from www.python.org. Convenient RPMs and a Windows executable installer are available besides the source code. After installing, make sure the Python executable is in your PATH, and then type python at the command prompt to start the interactive interpreter. You should see something like

c:\>python
Python 2.4 (#60, Nov 30 2004, 11:49:19) [MSC v.1310 32 bigt (Intel)] on win32
Type "help", "copyright", "credits", or "license" for more information.
>>>
The >>> prompt is waiting to accept Python commands from you. Like SQL*Plus, Python allows you to issue commands ad hoc or to experiment with commands before writing full-blown scripts.

Tradition dictates your next move:

>>> print 'Hello, World'
The interpreter responds with
Hello, World
unless you typed Print or PRINT instead of print. If you did, you have learned that everything in Python—commands, variable names, and the like—is case-sensitive.

Working with a Text File

Suppose you have a standard init.ora file defining default parameters for all of your databases. You want to compare its contents with those of the init.ora file for a particular database instance.

Listing 1 init_default.ora

DB_BLOCK_SIZE=4096
COMPATIBLE=9.2.0.0.0
SGA_MAX_SIZE=104857600
SHARED_POOL_SIZE=50331648

Listing 2 init_orcl.ora

FAST_START_MTTR_TARGET=300
SHARED_POOL_SIZE=50331648
DB_BLOCK_SIZE=8192
COMPATIBLE=9.2.0.0.0
UNDO_MANAGEMENT=AUTO
SGA_MAX_SIZE=135339844
Begin by opening init_orcl.ora for reading.
>>> initFile = open('init_orcl.ora')
You have now opened the file and assigned a variable, initFile, to refer to it. (Python uses a single = for assignment and == for comparison, whereas PL/SQL uses := and =, respectively.) Note that you didn't have to declare initFile or specify what type of data it would hold; as a "dynamically typed" language, Python figured that out for itself.

Let's see what we have.

>>> firstLine = initFile.readline()
>>> firstLine
'FAST_START_MTTR_TARGET=300\n'
Here, readline is a method defined on the object initFile. If you are unfamiliar with object-oriented programming, this will be a new concept to you, but the Python language provides a great place to get familiar with it.

Your experience with PL/SQL may suggest that there's no need for the parentheses after readline, because you're not passing any arguments to it. In Python, however, omitting the parentheses yields a result you may not expect.

>>> firstLine = initFile.readline
>>> firstLine
<built-in method readline of file object at 0xb7d653c8>
Instead of actually invoking the readline method, you simply grabbed a pointer to it and inserted it into firstLine. This can be very useful in certain advanced programming techniques; for example, you can actually invoke that function from the place to which you have assigned it.
>>> firstLine()
'SHARED_POOL_SIZE=50331648\n'
For now, though, simply remember that using () is not optional when invoking Python functions. Let's insert a fresh string into firstLine to go on.
>>> firstLine = initFile.readline()
>>> firstLine
'DB_BLOCK_SIZE=8192\n'

Introspection

The \n at the end of firstLine is a newline character, and you don't want it. How can you get rid of it? Python comes with excellent documentation, but you don't even need to go that far—Python's introspection capabilities can help you figure out what to do right from the prompt.

In Python a string such as firstLine is an object. As an object, it has methods—functions defined to operate on it. Let's see what we have available to work with on firstLine.

>>> type(firstLine)
<type 'str'>
>>> dir(firstLine)
['__add__', '__class__', '__contains__', '__delattr__', 
 '__doc__', '__eq__', '__ge__', '__getattribute__', '__getitem__', 
 '__getnewargs__', '__getslice__', '__gt__', '__hash__', '__init__', 
 '__le__', '__len__', '__lt__', '__mod__', '__mul__', '__ne__', 
 '__new__', '__reduce__', '__reduce_ex__', 
 '__repr__', '__rmod__', '__rmul__', '__setattr__',
 '__str__', 'capitalize', 'center', 'count', 'decode',
 'encode', 'endswith', 'expandtabs', 'find', 'index', 'isalnum',
 'isalpha', 'isdigit', 'islower', 'isspace', 'istitle', 'isupper',
 'join', 'ljust', 'lower', 'lstrip', 'replace', 'rfind', 'rindex',
 'rjust', 'rstrip', 'split', 'splitlines', 'startswith', 'strip',
 'swapcase', 'title', 'translate', 'upper', 'zfill']
How about strip—that looks right. By convention, a brief documentation string is stored in each object's __doc__ method. (Names such as __doc__ that begin and end with two underscores are set aside for special system-defined methods and attributes.) Let's print out strip.__doc__ to see if it's what you need.
>>> print firstLine.strip.__doc__
S.strip([chars]) -> string or unicode

Return a copy of the string S with leading and trailing whitespace removed.
If chars is given and not None, remove characters in chars instead.
If chars is unicode, S will be converted to unicode before stripping

>>> firstLine = firstLine.strip()
>>> firstLine
'DB_BLOCK_SIZE=8192'
Next, you want to distinguish the parameter in firstLine from its value. It's tempting to show off Python's powerful regular-expression-handling abilities, but let's stick with a simpler way: the string method split().
>>> firstLine.split('=')
['DB_BLOCK_SIZE', '8192']

Variables and Assignment

Calling split() produced a list of the strings preceding and following the dividing character, =. Unfortunately, you didn't assign this result to any variables, so the result was dumped onto the screen and then forgotten. This time, you'll use multiple assignments to capture both results at once. You may want to use the up-arrow key to save yourself some typing.

>>> param, val = firstLine.split('=')
>>> param
'DB_BLOCK_SIZE'
>>> val

'8192'
Actually, you'll want to store the values for each of several parameters. This is a great place to use a powerful Python variable type called a dictionary. A dictionary is an unordered set of key: value pairs; in fact, it's very analogous to an ordinary (heap) table in Oracle Database. Both the key and the value can be numbers, strings, or other objects. You'll create an empty dictionary and populate it with what you've extracted so far.
>>> initParams = {}
>>> initParams[param] = val
>>> initParams
{'DB_BLOCK_SIZE': '8192'}
Now grab another line from the open file, and store it in the dictionary as well. This time you'll chain strip() directly onto the end of readline(), as though you were using a UNIX pipe.
>>> nextLine = initFile.readline().strip()
>>> param, val = nextLine.split('=')
>>> initParams[param] = val
>>> initParams
{'DB_BLOCK_SIZE': '8192', 'COMPATIBLE': '9.2.0.0.0'}

Writing Scripts

Now that you've practiced interactively with the interpreter, you're ready to write a Python script to handle the whole file. Use Ctrl-D (in Unix/Linux) or Ctrl-Z (in Windows) to exit the Python interpreter, and create a text file—call it readInitOra.py.

initFile = open('init_orcl.ora', 'r')
initParams = {}
rawTextLine = initFile.readline()
while rawTextLine:
    param, val = rawTextLine.strip().split('=')
    initParams[param] = val
    rawTextLine = initFile.readline()
print initParams
As you read this code, you are most likely thinking, "Where are the semicolons that end commands? Where are the BEGIN and END statements, or the curly brackets, that denote blocks?" Python doesn't use them. By convention, almost all programmers separate commands with line breaks and indicate code blocks with indentation, so that humans can read the code. Meanwhile, most languages other than Python require them to use a different set of signals to communicate with the compiler or interpreter. Using two sets of signals clutters the code, and it creates a great danger—your signals to the human eye can get out of sync with your signals to the computer, creating cruelly elusive bugs. Python, on the other hand, reads your code the way a human would, resolving the clutter and avoiding the danger.

Let's see the code work. At the operating system command prompt (not the Python interpreter prompt), type

c:\> python readInitOra.py
{'UNDO_MANAGEMENT': 'AUTO', 'COMPATIBLE': '9.2.0.0.0',
 'DB_BLOCK_SIZE': '8192', 'FAST_START_MTTR_TARGET' : '300',
 'SGA_MAX_SIZE': 157286400, 'SHARED_POOL_SIZE': '50331648'}
If, out of habit, you indented all the lines in readInitOra.py a few spaces, you confused Python and received a syntax error. Indentations indicate blocks of code, so don't indent when you don't mean it. Go back and make sure that each line that isn't in a block begins in column 1.

You'll actually want to use this code in a couple of places, so let's change it from a simple script to the definition of a function that accepts a parameter.

def read(fileName):
    initFile = open(fileName, 'r')
    initParams = {}
    rawTextLine = initFile.readline()
    while rawTextLine:
        param, val = rawTextLine.strip().split('=')
        initParams[param] = val
        rawTextLine = initFile.readline()
    return initParams

Nesting

Next you need to create a similar dictionary containing your default parameters from init_default.ora. You could read them into brand-new variables, of course, but instead let's show off how nicely objects nest in Python. You'll create a single parent directory, initParams, and nest a directory within it for each init.ora file. You'll also import the file you just wrote, so that you can call its read() function. Create a new text file called compareInitOra.py.

import readInitOra
initParams = {}                                     
# brackets denote a list that we can loop through
for fileName in ['init_orcl.ora', 'init_default.ora']:                          
    initParams[fileName] = readInitOra.read(fileName)
print initParams

c:\> python compareInitOra.py
{'init_orcl.ora': 
{'UNDO_MANAGEMENT': 'AUTO', 'COMPATIBLE': '9.2.0.0.0', 
 'DB_BLOCK_SIZE': '8192', 'FAST_START_MTTR_TARGET': '300',
 'SGA_MAX_SIZE': '157286400, 'SHARED_POOL_SIZE': '50331648'}
 'init_default.ora': 
{'COMPATIBLE': '9.2.0.0.0', 'DB_BLOCK_SIZE': '4096',
 'FAST_START_MTTR_TARGET': '300', 'SGA_MAX_SIZE': '100663296',
 'SHARED_POOL_SIZE': '50331648'}}
This time the output has some white space, to help you see the nested structure. You could easily write Python code to print it prettily or use Python's pprint module, but we're database people—so let's get this data into an Oracle database instead.

SQL from Python

To access a database, your Python interpreter needs to have a database module installed. You have many choices, all of which conform to a standardized API specification and will look very familiar to anyone experienced with using ODBC or JDBC programmatically. You'll use cx_Oracle, for its ease of installation. Just download a Windows installer or an RPM file matching your versions of Python and Oracle Database.

After cx_Oracle is installed, go back to the Python command-line interpreter to try it out. Because cx_Oracle is a module separate from the core Python language, you must import it before using it in any session or script.

>>> import cx_Oracle   
Remember to watch your capitalization! Now let's create a table in which to store your results.
>>> orcl = cx_Oracle.connect('scott/tiger@orcl')
>>> curs = orcl.cursor()
>>> sql = """CREATE TABLE INIT_PARAMS 
... ( fileName VARCHAR2(30),
...   param VARCHAR2(64),
...   value VARCHAR2(512) )"""
The triple quote (""") is a handy syntax for entering strings that include line breaks. The Python interpreter changes its prompt from >>> to ...—to remind you that you're continuing input begun on an earlier line.
>>> curs.execute(sql)
>>> curs.close()
Now that your table is ready, let's write recordInitOra.py to populate it.
import readInitOra, cx_Oracle
initParams = {}                                     
for fileName in ['init_orcl.ora', 'init_default.ora']:                          
    initParams[fileName] = readInitOra.read(fileName)
orcl = cx_Oracle.connect('scott/tiger@orcl')
curs = orcl.cursor()
for fileName in initParams.keys():   
    for param in initParams[fileName].keys():
        value = initParams[fileName][param]
        sql = """INSERT INTO INIT_PARAMS VALUES 
                 (:fileName, :param, :value)"""
        bindVars = {'fileName': fileName, 
                    'param': param, 'value': value}
        curs.execute(sql, bindVars)
curs.close()              
orcl.commit()        
That's all it takes! Note that this time you used bind variables in your SQL string and supplied values for them in a separate dictionary. Using bind variables helps keep you out of trouble with the SPCSP (Society for the Prevention of Cruelty to the Shared Pool).

Getting results from a query is just a little more complicated. After calling execute() on a cursor object, you can use either fetchone() to get one row at a time or fetchall() to get a list of all rows. In either case, each row takes the form of a tuple—an ordered series of values that can be accessed by a numerical index. For example, let's write compareInitOra.py to print out init_orcl.ora parameters that conflict with the current values in V$PARAMETER:

import readInitOra, cx_Oracle
def readLiveParams():
    liveParams = {}
    orcl = cx_Oracle.connect('scott/tiger@orcl')
    curs = orcl.cursor()
    curs.execute('SELECT name, value FROM V$PARAMETER')
    row = curs.fetchone()
    while row:
        (param, val) = (row[0], row[1])
        liveParams[param.upper()] = val
        row = curs.fetchone()
    return liveParams

liveParams = readLiveParams()
fileName = 'init_orcl.ora'
fileParams = readInitOra.read(fileName)
for (param, val) in fileParams.items():
    liveVal = liveParams.get(param)
    if liveVal != val:
        print """For %s, V$PARAMETER shows %s, 
                 but the file %s shows %s""" % \
              (param, liveVal, fileName, val)

Object-Relational Mapping Tools for Python

You may be interested in object-relational mapping (ORM) tools, which can take over the writing of SQL and provide an object-oriented interface for programmers. Oracle TopLink is an example of an object-relational mapper for Java. Oracle-compatible ORM tools for Python include:

This script introduces a few tricks you haven't seen yet:
  • Calling items() on the dictionary fileParams returns a list of (key, value) pairs. You can loop through these together, by specifying two loop variables in the for statement.
  • Calling liveParams.get(param) works like liveParams[param], except that liveParams[param] returns an error if param is not found in liveParams—much like an "ORA-01403: no data found in PL/SQL" message. The liveParams.get(param), on the other hand, returns None when param is not in liveParams.
  • Python can use the % operator for string substitution. Like C's printf, %s indicates that a value will be inserted in string form at that point. The values are taken, in order, from the tuple that follows the %.
  • The last line of code runs longer than you want to type without a line break, so you use a backslash to make an exception to Python's usual rule of interpreting a line break as the end of a command.

Web Publishing

Finally, let's expose this functionality through a Python Web application. First, you need to select which Python Web application platform to use—which might be harder than actually writing the application! Because they are very easy to write in Python, literally dozens of platforms are available. The most popular is Zope; a Zope application can be created and administered completely from within user-friendly Web pages. Or if you're intrigued by Ruby on Rails, you may want to consider TurboGears or Django—Python's own top-to-bottom application builders. TurboGears also includes support for conveniently including AJAX in your application. For now, let's choose a particularly simple framework, CherryPy. Download it here.

First, you modify compareInitOra.py slightly (see below) to create an html() function that returns a string with the text and tags you want. Then just seven more lines of code expose that function as a Web application.

Listing 3 compareInitOra

import readInitOra, cx_Oracle
def readLiveParams(instance):
    liveParams = {}
    orcl = cx_Oracle.connect('scott/tiger@' + instance)
    curs = orcl.cursor()
    curs.execute('SELECT name, value FROM V$PARAMETER')
    row = curs.fetchone()
    while row:
        (param, val) = (row[0], row[1])
        liveParams[param.upper()] = val
        row = curs.fetchone()
    return liveParams

fileName = 'init_orcl.ora'

def html(initOraFile, instance):
    result = ""
    fileParams = readInitOra.read(initOraFile)
    liveParams = readLiveParams(instance)
    for (param, val) in fileParams.items():
        liveVal = liveParams.get(param)
        if liveVal == val:
            result += "<font color='green'>%s</font><br>\n" % (val)
        else:
            result += "<font color='red'>%s (file) %s (live)</font></br>\n" % (val, liveVal)
    return result

import cherrypy
class WebApp:
    def index(self, initOraFile='init.ora', instance='orcl'):
        return html(initOraFile, instance)
    index.exposed = True
cherrypy.root = WebApp()
cherrypy.server.start()
When the script is run, it begins to function as a Web server on (by default) port 8080. Pointing a browser at http://localhost:8080/?initOraFile=init_orcl gives the HTML page you want.

Conclusion

Hopefully you've become intrigued by Python's ease, elegance, and readability. What you haven't seen yet is Python's power. Its capabilities include elegant exception handling, unit testing, object orientation, functional programming, GUI toolkits, Web frameworks, XML, Web services—virtually everything programmers do. You won't need to "graduate" to a different language as your work gets more advanced.


Catherine Devlin is an Oracle Certified Professional with six years of experience as an all-purpose DBA, PL/SQL developer, distributed systems architect, and Web application developer for small-scale Oracle OLTP systems. She works for IntelliTech Systems in Dayton, Ohio, and blogs at catherinedevlin.blogspot.com.

Send us your comments