CODEWITHSUNDEEP
python
MGA
MGA

Reputation: 325

Convert cx_Oracle.LOB data to string in python

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?

Upvotes: 9

Views: 14583

Answers (2)

Ckln
Ckln

Reputation: 305

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)

Upvotes: 6

MGA
MGA

Reputation: 325

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

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

Upvotes: 7

Related Questions