Loading and Reading Binary files in Oracle Database using Python

Posted on Updated on

Most Python example show how to load data into a database table. In this blog post I’ll show you how to load a binary file, for example a picture, into a table in an Oracle Autonomous Database (ATP or ADW) and how to read that same image back using Python.

Before we can do this, we need to setup a few things. These include,

  1. create a table in the database to store the pictures

Let’s use the following table

CREATE TABLE demo_blob (
   id     NUMBER PRIMARY KEY,
   image_txt   VARCHAR2(100),
   image   BLOB);
  1. create a connection to the database

Now let’s get onto the fun bit of loading a image file into this table. The image I’m going to use is the cover of my Data Science book published by MIT Press.

I have this file saved in ‘…/MyBooks/DataScience/BookCover.jpg’.

#Read the binary file
with open (".../MyBooks/DataScience/BookCover.jpg", 'rb') as file:
    blob_file = file.read()

#Display some details of file
print('Length =', len(blob_file))
print('Printing first part of file')
print(blob_file[:50])

Now define the insert statement and setup a cursor to process the insert statement;

#define prepared statement
inst_blob = 'insert into demo_blob (id, image_txt, image) values (:1, :2, :3)'

#connection created using cx_Oracle - see links earlier in post
cur = con.cursor()

Now insert the data and the binary file.

#setup values for attributes
idNum = 1
imageText = 'Demo inserting Blob file'
#insert data into table
cur.execute(inst_blob, (idNum, imageText, blob_file))

#close and finish
cur.close() #close the cursor
con.close() #close the database connection

The image is now saved in the database table. You can use Python to retrieve it or use other tools to view the image.
For example using SQL Developer, query the table and in the results window double click on the blob value. A window pops open and you can view on the image from there by clicking on the check box.

Now that we have the image loads into an Oracle Database the next step is the Python code to read and display the image.

#define prepared statement
qry_blog = 'select id, image_txt, image from demo_blob where id = :1'

#connection created using cx_Oracle - see links earlier in post
cur = con.cursor()
#setup values for attributes 
idNum = 1 
#execute the query
#query the data and blob data
connection.outputtypehandler = OutputTypeHandler
cur.execute(qry_blob, (idNum)) 
id, desc, blob_data = cur.fetchone()

#write the blob data to file
newFileName = '.../MyBooks/DataScience/DummyImage.jpg'
with open(newFileName, 'wb') as file:
   file.write(blob_data)
#close and finish 
cur.close() #close the cursor 
con.close() #close the database connection