CODEWITHSUNDEEP
pythonoracle-databaseblobcx-oracle
wolφi
wolφi

Reputation: 8361

How to insert a BLOB into Oracle with Python?

I'm trying to insert a lot of BLOBs (2 to 20 MB each) into Oracle 12 using cx_Oracle 6.3.

After a lot of googling and experimentation, I got the following code working. I'm new to Python and would like to know: Is the approach valid? Is there a faster way?

#!/usr/local/bin/python3
import io
import os
import cx_Oracle

pdf = open('hello.pdf', 'rb')
mem_file = io.BytesIO(pdf.read())
mem_file.seek(0, os.SEEK_END)
file_size = mem_file.tell()

con = cx_Oracle.connect("user", "***", "localhost:1512/ORCLPDB1", encoding="UTF-8")

# create table for this example
con.cursor().execute("CREATE TABLE t (id NUMBER, b BLOB) LOB(b) STORE AS SECUREFILE(COMPRESS)");

# prepare cursor
cursor = con.cursor()
my_blob = cursor.var(cx_Oracle.BLOB, file_size)
my_blob.setvalue(0, mem_file.getvalue())

# execute insert
cursor.execute("INSERT INTO t(id, b) VALUES (:my_id, :my_blob)", (1, my_blob))
con.commit()

con.close()

How about inserting an EMPTY_BLOB() and do an UPDATE later? Is it neccessary / beneficial to calculate the size of the BLOB before the insertion?

Upvotes: 2

Views: 7245

Answers (1)

Anthony Tuininga
Anthony Tuininga

Reputation: 7086

You can do something much simpler which will be considerably faster as well. Note that this approach only works when you are capable of storing the entire file contents in contiguous memory and the current hard limit is 1 GB, even if you have many terabytes of RAM available!

cursor.execute("insert into t (id, b) values (:my_id, :my_blob)",
        (1, mem_file.getvalue())

Inserting an empty_blob() value and returning the LOB locator for later update is faster than creating a temporary LOB and inserting it (as you are doing in your code) but inserting the data directly is even faster!

Upvotes: 1

Related Questions