[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

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.

Recommended Posts

[Introduction to cx_Oracle] (16th) Handling of LOB types
[Introduction to cx_Oracle] (5th) Handling of Japanese data
[Introduction to cx_Oracle] (12th) DB Exception Handling
[Introduction to cx_Oracle] (17th) Date type handling
[Introduction to cx_Oracle] (Part 7) Handling of bind variables
[Introduction to cx_Oracle] Overview of cx_Oracle
[Introduction to cx_Oracle] (8th) cx_Oracle 8.0 release
[Introduction to cx_Oracle] (Part 3) Basics of Table Reference
[Introduction to cx_Oracle] (Part 11) Basics of PL / SQL Execution
Series: Introduction to cx_Oracle Contents
[Introduction to cx_Oracle] (Part 4) Fetch and scroll of result set
[Introduction to cx_Oracle] (10th) Update DML and DDL / DCL statements
[Introduction to cx_Oracle] (Part 2) Basics of connecting and disconnecting to Oracle Database
[Introduction to Udemy Python3 + Application] 65. Exception handling
Introduction to Scapy ① (From installation to execution of Scapy)
[Introduction to Data Scientists] Basics of Python ♬
[Introduction to cx_Oracle] (15th) Creating a result set in a format other than tuples
[Introduction to Udemy Python 3 + Application] 26. Copy of dictionary
[Python] How to use two types of type ()
[Introduction to Udemy Python 3 + Application] 19. Copy of list
From the introduction of pyethapp to the execution of contract
[Introduction to Python] Basic usage of lambda expressions
Introduction to MQTT (Introduction)
Introduction to Scrapy (1)
Introduction to Scrapy (3)
Introduction to Supervisor
Introduction of Python
Introduction of scikit-optimize
Introduction to Tkinter 1: Introduction
Introduction of PyGMT
Introduction to PyQt
Introduction to Scrapy (2)
[Linux] Introduction to Linux
Introduction to machine learning ~ Let's show the table of K-nearest neighbor method ~ (+ error handling)
Introduction to Scrapy (4)
Introduction to discord.py (2)
Introduction of cymel
Introduction to discord.py
Introduction of Python
[Chapter 5] Introduction to Python with 100 knocks of language processing
[Chapter 6] Introduction to scikit-learn with 100 knocks of language processing
[Introduction to Udemy Python3 + Application] 53. Dictionary of keyword arguments
[Chapter 3] Introduction to Python with 100 knocks of language processing
[Chapter 2] Introduction to Python with 100 knocks of language processing
[Introduction to Python] Basic usage of the library matplotlib
[Introduction to Udemy Python3 + Application] 52. Tupleization of positional arguments
[Introduction to cx_Oracle] (Part 13) Connection using connection pool (client side)
[Chapter 4] Introduction to Python with 100 knocks of language processing