Friday, November 30, 2018

Load Text Into Oracle Table CLOB Column Using Python Part 2

The texts in some records are too long, e.g., more thatn 22k characters. This is inconvenient when we try to edit it online. I use chunkstring(line2,900) to divide the text into multiple strings of fixed length of 900 characters and stored them in multiple records. uuid.uuid1().hex is used to generate an universally unique id.
import cx_Oracle
import uuid
def chunkstring(string, length):
    return (string[0+i:length+i] for i in range(0, len(string), length))
connection = cx_Oracle.connect("xxxxx","xxxx",\
                               "golden.maxapex.net:1521/xxxx",\
                               encoding = "UTF-8", nencoding = "UTF-8")
cur = connection.cursor()
cur.setinputsizes(T1 = cx_Oracle.CLOB)
cur.setinputsizes(T2 = cx_Oracle.CLOB)
f = open('id_baike2.txt',  \
        encoding="utf8")
line1 = f.readline()
line2 = f.readline()
while line2:
    vuid = uuid.uuid1().hex
    data_list = list(chunkstring(line2,900))
    for idx, v in enumerate(data_list):
        cur.execute("INSERT INTO t_text_clob_m (ID, SID, SEQ, T1)  \
               VALUES (:ID, :SID, :SEQ, :T1)",\
                    ID = int(line1), SID = vuid, SEQ = idx+1, T1 = v)
    # create table t_text_clob_m (id number, sid raw(32), seq number, t1 clob);
    line1 = f.readline()
    line2 = f.readline()
f.close()
connection.commit()
cur.close()
connection.close()

No comments: