cx_Oracle.NotSupportedError: Python value of type WindowsPath not supported

[Rakin061]

Hello,

I am using cx_Oracle to insert binary files from a directory as BLOB in a column of a table.

So, I am intended to use the PATH module of python3 and then insert the binary file by fetching the file using WindowsPath

Configurations:-

1. Python Version: 3.6.3 64-bit
2. cx_Oracle version: 6.2.1
3. Instant client_11_2
4. Oracle Database: 11g
5. OS: Windows10
6. Compiler: Pycharm Community Editiron 2017

Here's the cdoe:-

import sys
import cx_Oracle

from pathlib import Path

username = 'EWORDPR'
password = 'EWORDPR'
databaseName = "10.11.201.161:1521/ORCL"

connection = cx_Oracle.connect (username,password,databaseName) # Connection Established

cursor=connection.cursor()

my_file = Path("22 Apr 2018.txt")
print("File exists:",my_file.is_file()) #File Exists: True

rows = [ ('01787440110', 'Rakin','22-04-2017', my_file )]
cursor.executemany('insert into CHAT_HISTORY(MOBILE_NUMBER,USER_NAME,CHAT_DATE,USER_TRANSCRIPTS) values (:1, :2, :3, :4)', rows)

connection.commit()

cursor.close()

connection.close()

Then, I am getting the following errors:-

Traceback (most recent call last): File "F:/Python with ORACLE/orapy.py", line 53, in print(cursor.executemany('insert into CHAT_HISTORY(MOBILE_NUMBER,USER_NAME,CHAT_DATE,USER_TRANSCRIPTS) values (:1, :2, :3, :4)', rows)) cx_Oracle.NotSupportedError: Python value of type WindowsPath not supported.

Is there any way to export binary files from a directory and then directly insert the file in as BLOB using cx_Oracle ??

[anthony-tuininga]

Sure. You can do the following:

rows = [ ('01787440110', 'Rakin','22-04-2017', my_file.open("rb").read() )]

That will open the file and read its contents. This will work effectively for files that readily fit in memory and for those types of files this is the most efficient way to do that. For very large files you will need to use a LOB locator and stream the data instead.

[Rakin061]

Many thanks @anthony-tuininga

As you said I have induced the following update in my code:

fo = open("23 Apr 2018.txt", "rb")
content=fo.read()

blobvar = connection.createlob(cx_Oracle.BLOB)
blobvar.write(content)

rows = [ ('01787440110', 'Rakin','23-04-2017', blobvar)]
cursor.executemany('INSERT INTO CHAT_HISTORY(MOBILE_NUMBER,USER_NAME,CHAT_DATE,USER_TRANSCRIPTS) values (:1, :2, :3, :4)', rows)

Will the updated code work perfectly for very large files ? Or, is there any other performant option for production while dealing with very large files?

..... For very large files you will need to use a LOB locator and stream the data instead.

I didn't get the point. What I have done is to read the contents from the file and write it to the LOB. Could you please clarify me how I could stream the data directly using the lob locator ?

Thanks in advance!!

[anthony-tuininga]

The code will work for any file less than 1 GB provided you have the contiguous memory available to keep the file contents in memory! If you need to stream, you can do something like the following:

lobVar = cursor.var(cx_Oracle.CLOB)
cursor.execute("INSERT INTO CHAT_HISTORY(MOBILE_NUMBER,USER_NAME,CHAT_DATE,USER_TRANSCRIPTS) values (:1, :2, :3, empty_clob()) returning USER_TRANSCRIPTS into :4", ['01787440110', 'Rakin', '22-04-2017', lobVar)
lob = lobVar.getvalue()
offset = 1
lob.write(firstPart, offset)
offset += len(firstPart)
lob.write(secondPart, offset)
offset += len(secondPart)
# and so forth

[Rakin061]

Great!!

But do I have to split the original content into firstPart, secondPart.... manually ? The thing is that I have to save chat history of different user programmatically and concurrently. And, chat history file size will be different for different users.
So, The only difference I could figure out between your code and my code is I am writing the whole content at a time and you're splitting the content into parts and then writing it one by one.

And,
lob = lobVar.getvalue()
I couldn't find any method called getValue() in the official documentation of LOB Object.

Many thanks for your time @anthony-tuininga

[anthony-tuininga]

But do I have to split the original content into firstPart, secondPart.... manually ?

Not at all! Generally, if you're reading from a file you would do something like this:

offset = 1
size = 8388608  # or some value that makes sense to you or is optimal for your configuration
with open(fileName, "rb") as f:
    while True:
        data = f.read(size)
        if not data:
            break
        lob.write(data, offset)
        offset += len(data)

I couldn't find any method called getValue() in the official documentation of LOB Object.

That is because lobVar is a variable, not a LOB value! This documentation is what you want!

[Rakin061]

Thanks @anthony-tuininga .... Things are now absolutely clear to me.

I am closing the issue as well.