[cx-oracle-users] blob streaming, possible improvement

[Joram A. <jor...@gm...>]

Hello,

I had to upload 20GB of documents from an ftp site, to a table with a BLOB
column.
In order to do this in a streaming fashion I had to write a little helper
class for the BLOB stream.
(see code example below)

It would be handy if the BLOB write function did not need an offset
parameter.
So writing without offset specified moves it automatically along ( offset
+= len(data) ) and the BLOB object remembers the last written offset (as
the instantiated Helper class in my example)

I think that is more in sync with the other file-type like write functions
(see also StringIO as an example)

Hopefully this is easy to integrate in cx_Oracle.
Thanks in advance
Joram



import cx_Oracle
import ftplib

dsn = "scott/tiger"
connection = cx_Oracle.connect(dsn)
cursor = connection.cursor()

ftp = ftplib.FTP()
ftp.connect("host", 21)
ftp.login("user", "passwd")

class Helper(object):
    def __init__(self, writer):
        print("initializing helper with writer {w}".format(w=writer))
        self.writer = writer
        self.offset = 1

    def write(self, data, offset=None):
        if not offset:
            offset = self.offset
        print("calling write on helper with {len} bytes of date, current
offset {offset}".format(len=len(data), offset=self.offset))
        self.writer.write(data, offset)
        self.offset += len(data)

try:
    attachment_id = 1
    file_name = "bigfile.txt"
    bigFileVar = cursor.var(cx_Oracle.BLOB)
    cursor.execute("""UPDATE ta_cs_attachments
                         SET file_data = empty_blob()
                           , upload_processed_flg = 'Y'
                       WHERE attachment_id = :attachment_id
                      RETURNING file_data INTO :file_data"""
                   , {'attachment_id' : attachment_id, "file_data":
bigFileVar})
    blob = bigFileVar.getvalue()
    helper = Helper(blob)
    ftp.retrbinary("RETR " + file_name, helper.write, 65536)
    connection.commit()

except cx_Oracle.DatabaseError, exc:
    error, = exc.args
    print(error)
except ftplib.error_temp, msg:
    print("error in uploading, {e}".format(e=msg))

cursor.close()
connection.close()
ftp.quit()