Loading and Reading Binary files in Oracle Database using Python
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,
Let’s use the following table
CREATE TABLE demo_blob ( id NUMBER PRIMARY KEY, image_txt VARCHAR2(100), image BLOB);
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
Oralytics 