[Introduction to cx_Oracle] (16th) Handling of LOB types

Serial table of contents

Series: Introduction to cx_Oracle Table of Contents

Verification environment

--Using Oracle Cloud

• Oracle Linux 7.7 (VM.Standard2.1)
• Python 3.6
• cx_Oracle 8.0
• Oracle Database 19.6 (DBCS HP, 2oCPU)
• Oracle Instant Client 18.5

Advance preparation

Create the following table.

SQL> create table sample16(col1 number, col2 blob);

Basics of referencing / updating to LOB type

If the data is up to 1GB, it can be handled as str for CLOB type and byte for BLOB without any special consideration like VARCHAR2 type and RAW type. If it exceeds 1GB, it must be processed in a stream processing format that processes a fixed amount at a time. Performance is better without going through a stream, but even if one data is less than 1GB, fetchmany () will quickly consume memory, so memory resources and the amount of data to be fetched (average) Depending on the balance of size x number of records), consider stream processing even for small-sized LOBs.

Small data updates

In all cases, please prepare the data (file) to be processed separately. If you want to run the sample source in your own environment, please correct either the file name in the source or the name of the prepared file. Below is a sample of INSERT to BLOB type.

sample16a.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
    insert into sample16 values(1, :blobdata)
"""

with open('screenshot1.png', 'rb') as f:
    image = f.read()

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
    with connection.cursor() as cursor:
        cursor.execute(SQL, blobdata=image)
        cursor.execute("commit")

Small data reference

References are not as easy as updating, and you need to define the output type handler described in 9th. The following sample refers to the record registered earlier and saves it in a file with a different name.

sample16b.py

import cx_Oracle

def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
    if defaultType == cx_Oracle.DB_TYPE_BLOB:
        return cursor.var(cx_Oracle.DB_TYPE_LONG_RAW, arraysize=cursor.arraysize)

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
    select col2 from sample16 where col1 = 1
"""

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
    connection.outputtypehandler = OutputTypeHandler
    with connection.cursor() as cursor:
        cursor.execute(SQL)
        lobdata, = cursor.fetchone()
with open('screenshot2.png', 'wb') as f:
    f.write(lobdata)

Of course, the contents of both files are the same.

$ cmp screenshot1.png screenshot2.png
$

Large-scale data update

I will explain based on BLOB. The procedure is roughly as follows.

1. Insert an empty locator into the LOB column with the SQL function EMPTY_BLOB ()
2. Bind the LOB locator with the RETURNING clause of the INSERT statement
3. Stream data to the LOB locator

sample16c.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
    insert into sample16 values(2, empty_blob())
    returning col2 into :blobdata
""" # [1.]


with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
    with connection.cursor() as cursor:
        blobdata = cursor.var(cx_Oracle.DB_TYPE_BLOB) # [2.]
        cursor.execute(SQL, [blobdata])
        blob, = blobdata.getvalue()
        offset = 1
        bufsize = 65536 # [3.]
        with open('screenshot1.png', 'rb') as f: # [4.]
            while True:
                data = f.read(bufsize)
                if data:
                    blob.write(data, offset)
                if len(data) < bufsize:
                    break
                offset += bufsize
    connection.commit()

Below, it corresponds to the comment number.

1. The BLOB column uses the SQL function EMPTY_BLOB () to insert an empty LOB locator. Then use the RETURNING clause to receive the inserted empty LOB locator in the bind variable.
2. Prepare a variable on the Python side that corresponds to the bind variable. I'm using Cursor.var () to specify that it's a BLOB.
3. Specify the size of the buffer.
4. The opened file is written to the LOB column by the size of 3. via the LOB locator.

The flow of the UPDATE statement is the same, only the SQL statement changes. Update the LOB column with EMPTY_BLOB ().

Large-scale data reference

I will explain based on BLOB. The procedure is roughly as follows. It's easier than updating.

1. Bind the LOB column (LOB locator) in the SELECT list
2. Read the data in a stream to the LOB locator

sample16d.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
    select col2 from sample16 where col1 = 2
""" # [1.]


with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
    with connection.cursor() as cursor:
        cursor.execute(SQL)
        blob, = cursor.fetchone()
        offset = 1
        bufsize = 65536 # [2.]
        with open('screenshot3.png', 'wb') as f: # [3.]
            while True:
                data = blob.read(offset, bufsize)
                if data:
                    f.write(data)
                if len(data) < bufsize:
                    break
                offset += bufsize

Below, it corresponds to the comment number.

1. SELECT the BLOB column.
2. Specify the size of the buffer.
3. The LOB data is written to the opened file by the size of 2.