How to write and read clob type data in oracle database from python?

Question

I want to insert and read clob data through python.

I put data like below code.

cursor.execute("""insert into test values (:data)""", data=data.encode())

The data is like this.

b'{"metadata": {"dataVersion": "2", ...

The insert operation works fine.

However, when data is imported with the select statement, completely different data comes out.

cursor.execute("select columns from test")
a = cursor.fetchall()
a = a[1][0]
a

a:

'7B226D65746164617461223A207B226461746156657273696F6E223A202232222C20226D617463684964223A20224B525F35353934313639363034222C20227061727469636970616E7473223A205B2278544258423041787838786E425A41525A43704344554A734A6C674A4F6954636D4E5644704544356C71705058505A7A6D32695358674751452D6C314C516E356D5466384C51333636396F686377222C2022656634464E5971484A416C586C72346749445A61617950446B775A696A43544B59757964704642576B4F4B49724A3264472D737837637A524C73325274394A4A4C4D684C30506555793663726851222C20226B7A4B5A2D6134724A647952635949326A765A6E423854527367342D526B422D624838637373666C72754C474C3058614B337343356F6A68476252496D4C4D436D327063566E6170662D58326C51222C20225A4B50654A4E4F656F4766426162533464564177556A566E676133584F36483873622D46756273366F5832356C4B324B6579354C385232724F345F6E667672694D65685A6C7051514F74505A3977222C20226B3839526C4E5271596A677A34486158512D67695972376643574D2D4F415A785075476371557A58626B624...

Do I need to decode the data?

Or do I have to put it in another way when inserting?

insert into testvalues (:data) should have a space between test and values. — MT0
– MT0, Commented Nov 28, 2021 at 13:13
Looking at the beginning of the returned string we see 7B226D65746164617461223A207B, which is { " m e t a d a t a " : { when translated from hex to ASCII. So the data is there, it's just being returned as a string containing the hexadecimal equivalent. Perhaps if you didn't call encode() when inserting the data..? — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Nov 28, 2021 at 14:10
Take a look here cx-oracle.readthedocs.io/en/latest/user_guide/lob_data.html — Connor McDonald
– Connor McDonald, Commented Nov 29, 2021 at 2:40
@MT0 thanks.. There was a typo. There were spaces as I said when running. — dodododo
– dodododo, Commented Nov 29, 2021 at 10:37
@BobJarvis-ReinstateMonica How did you convert hex to ascii? No matter how hard I try, I can't — dodododo
– dodododo, Commented Nov 29, 2021 at 10:39

Avner Cohen · Accepted Answer · 2021-12-05 10:04:41Z

0

Option #1:

use read() function to convert the CLOB to string:

a = a[1][0].read()

Option #2:

Convert cx_Oracle.LOB data to string in python

answered Dec 5, 2021 at 10:04

Avner Cohen

901 gold badge1 silver badge5 bronze badges

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

Collectives™ on Stack Overflow

How to write and read clob type data in oracle database from python?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related