How to insert a BLOB into Oracle with Python?

Question

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?

Anthony Tuininga · Accepted Answer · 2018-06-04 14:35:33Z

1

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!

answered Jun 4, 2018 at 14:35

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wolφi Over a year ago

Thanks for the good explanation. Giving cursor.var the size of the BLOB is then not necessary, either?

Anthony Tuininga Over a year ago

You're welcome. And no, giving the size of the BLOB is not needed.

Collectives™ on Stack Overflow

How to insert a BLOB into Oracle with Python?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related