Inserting and Retrieving BLOB Data in Oracle Using Python cx_Oracle

First, define an Oracle table that includes a BLOB column to store binary data:

CREATE TABLE aurora( date_forecast VARCHAR2(10), filename VARCHAR2(24), content BLOB );

Prepare the Python variables that hold the date, filename, and the binary content of the file you want to store:

date_forecast = '2011-06-29' filename = 'aurora.jpg' file = open('aurora.jpg', 'rb') content = file.read() file.close()

Build an INSERT statement that uses a bind variable (:blobData) for the BLOB column, and format the other values:

sqlStr = "INSERT INTO aurora (date_forecast, filename, content) VALUES ('%s', '%s', :blobData)" % (date_forecast, filename)

Tell the cursor that the bind variable blobData should be treated as a BLOB object:

cursor.setinputsizes(blobData=cx_Oracle.BLOB)

Execute the INSERT, passing the binary content as the value for blobData , and commit the transaction:

cursor.execute(sqlStr, {'blobData': content}) cursor.execute('commit')

To read the stored BLOB back, issue a SELECT that retrieves the latest row (or any row you need):

sqlStr = "SELECT date_forecast, filename, content FROM (SELECT date_forecast, filename, content FROM aurora ORDER BY date_forecast DESC) WHERE ROWNUM < 2" cursor.execute(sqlStr) result = cursor.fetchall()

The third element of the first row ( result[0][2] ) is the BLOB object. You can inspect its methods and read its data as a Python string:

print(dir(cx_Oracle.BLOB)) print(type(result[0][2].read()))

Finally, write the retrieved binary data back to a file on disk:

file = open('aurora.jpg', "wb") file.write(result[0][2].read()) file.close()

Following these steps allows seamless insertion and extraction of BLOB data between Python applications and Oracle databases using the cx_Oracle driver.