如何使用Python将BLOB插入Oracle? [英] How to insert a BLOB into Oracle with Python?

问题描述

我正在尝试使用cx_Oracle 6.3将很多BLOB(每个2至20 MB)插入Oracle 12.

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

经过大量的搜索和实验，我得到了以下代码.我是Python的新手，我想知道:这种方法有效吗?有没有更快的方法?

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()

如何插入EMPTY_BLOB()并稍后再执行UPDATE?在插入之前计算BLOB的大小是否必要/有益?

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?

推荐答案

您可以做一些简单得多的事情，而且速度也要快得多.请注意，这种方法仅在能够将整个文件内容存储在连续内存中且当前硬限制为1 GB的情况下才有效，即使您有许多TB的可用RAM！

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())

插入empty_blob()值并返回LOB定位器以供以后更新比创建临时LOB并插入它(如您在代码中所做的那样)要快，但是直接插入数据甚至更快！

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!

这篇关于如何使用Python将BLOB插入Oracle?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！