Convert cx_Oracle.LOB data to string in python

Question

I have this list:

['ThisText', <cx_Oracle.LOB object at 0x02FDC080>]

Lst[1] content :

This is a string but as clob

Im populating the list from this query result as such :

import cx_Oracle

dsn_tns = cx_Oracle.makedsn('mario.connex.ro', '1529', service_name='EAIUAT')
conn = cx_Oracle.connect(user=r'LOGGING_DB05', password='passw0rd', dsn=dsn_tns)

c = conn.cursor()
c.execute("select column1, column2 from DB_Table where column3 = '1234'")

lst = []


for i in c:
    for j in i:
        lst.append(j)

table structure is :

Column1 = varchar
Column2 = clob
Column3 = varchar

I want to convert the clob value from lst[1] to string

str = ''.join(lst[1])

Error received :

TypeError: can only join an iterable

Printed the lst[1] type :

print(type(lst[1]))
<class 'cx_Oracle.LOB'>

Is there a way to convert the cx_Oracle.LOB value to string?

MGA · Accepted Answer · 2020-05-14 07:41:17Z

8

Solved : using cx.oracle.LOB.read() method to retrive LOB data.

str = ''.join(lst[3].read())

answered May 14, 2020 at 7:41

MGA

3451 gold badge2 silver badges9 bronze badges

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

1 Comment

Eric Kramer Over a year ago

As mentioned in other answers, this involves extra server round-trips (per row) so probably better to have your connection/cursor request the string/bytes during that phase.

Ckln · Accepted Answer · 2021-08-02 10:01:24Z

6

Your own answer is a solution, but you should be aware that using the .read() method from the cx.oracle.LOB object will make some DB round trip at the time you call it, because this will stream your LOB content that might cause some performances issues.

If you want to minimize your DB round trip and your LOBs (CLOBs or BLOBs) are smaller than 1GB you might directly obtain the LOB content as a string during the request using a Cursor.outputtypehandler

Documentation extract:

Fetching LOBs as Strings and Bytes

CLOBs and BLOBs smaller than 1 GB can queried from the database directly as strings and bytes. This can be much faster than streaming.

A Connection.outputtypehandler or Cursor.outputtypehandler needs to be used as shown in this example:

def output_type_handler(cursor, name, default_type, size, precision, scale):
    if default_type == cx_Oracle.DB_TYPE_CLOB:
        return cursor.var(cx_Oracle.DB_TYPE_LONG, arraysize=cursor.arraysize)
    if default_type == cx_Oracle.DB_TYPE_BLOB:
        return cursor.var(cx_Oracle.DB_TYPE_LONG_RAW, arraysize=cursor.arraysize)

id_val = 1
text_data = "The quick brown fox jumps over the lazy dog"
binary_data = b"Some binary data"
cursor.execute("insert into lob_tbl (id, c, b) values (:1, :2, :3)", [id_val, text_data, binary_data])

connection.outputtypehandler = output_type_handler
cursor.execute("select c, b from lob_tbl where id = :1", [id_val]) 
clob_data, blob_data = cursor.fetchone()
print("CLOB length:", len(clob_data)) print("CLOB data:", clob_data)
print("BLOB length:", len(blob_data)) print("BLOB data:", blob_data)

edited Aug 2, 2021 at 10:01

answered Jun 18, 2021 at 14:24

Ckln

3055 silver badges11 bronze badges

2 Comments

svobol13 Over a year ago

In successor library there is configuration to fetch lobs eagerly: python-oracledb.readthedocs.io/en/latest/api_manual/…

botenvouwer Over a year ago

@svobol13 you are linking to the old package not the "successor library" :|. As stated on the introduction page "The python-oracledb driver is the renamed, major version successor to cx_Oracle 8.3. For upgrade information, see Upgrading from cx_Oracle 8.3 to python-oracledb.".

Collectives™ on Stack Overflow

Convert cx_Oracle.LOB data to string in python

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related