None
Blog / 2009 / November / 23

Selecting with cx_Oracle

November 23, 2009

cx_Oracle is the python database interface to use when using an Oracle back end. Specific versions are available for download for a particular combination of oracle and python version. The latest rpms (marked CentOS) seem to work fine on fedora. http://cx-oracle.sourceforge.net/ for installers.

Select Example

This example code shows a select using bind variables to pass parameters. Never concat strings!

self.dbConnection = cx_Oracle.connect("%s/%s@%s:%s/%s" % 
        (lDatabaseUsername, lDatabasePassword, lDatabaseHost, lDatabasePort, lDatabaseName))

lCursor = self.dbConnection.cursor()
lCursor.execute("SELECT id, column_number, column_description, optional, mapping FROM mytable WHERE id = :p1" % {'p1':pId})
lRows = lCursor.fetchall()
lDictionary = {}
for lRow in lRows:
    lDictionary[lRow[1]] = (lRow[2], lRow[3], lRow[4])

Insert Example

Here's an example of some code to insert into the database, again using bind variables, but using a slightly different parameter style.

lCursor = self.dbConnection.cursor()
lCursor.execute('INSERT INTO log (log_time, jobid, source) VALUES (SYSDATE,:p1,:p2)', p1=pJobId, p2=pSource)
lCursor.close()

Tags: python oracle cx_oracle