python写入数据到oracle clob字段-JZTXT

环境:

Python:3.6.5

#!/usr/bin/env python
#coding=utf-8
import os, json, urllib, datetime, shutil
import time
import cx_Oracle

gl_mysql_server="192.168.1.118"
gl_user_name="hxl"
gl_password= "mysql"
gl_db_name="db_test"

##create table tb_test
##(
##id number not null primary key,
##name1 varchar(32),
##name2 varchar(32),
##name3 varchar(32),
##name4 varchar(32),
##name5 varchar(32),
##name6 varchar(32),
##createtime date default sysdate,
##modifytime date default sysdate
##);

##create table TB_OGG_TEST
##(
##  id          NUMBER not null,
##  create_time DATE default sysdate,
##  modify_time DATE default sysdate,
##  fbactid     VARCHAR2(12),
##  b655        DATE,
##  b656        DATE,
##  b676        DATE,
##  b677        DATE,
##  b678        DATE,
##  b679        DATE,
##  b680        DATE,
##  b681        DATE,
##  b682        DATE,
##  b683        DATE,
##  remark      clob
##)


def insert_data():
    db =  cx_Oracle.connect('hxl','oracle','192.168.1.101:1521/slnngk',encoding="UTF-8")
    # 得到一个可以执行SQL语句的光标对象
    cursor = db.cursor()

    db.autocommit=False ## 关闭字自动提交

    for i in range(184961, 200001):
        str_i = str(i)
        curr_time = datetime.datetime.now()
        time_str = datetime.datetime.strftime(curr_time, '%Y-%m-%d %H:%M:%S')

        l_id = i
        l_create_time=time_str
        l_modify_time=time_str
        l_fbactid ='B'+str(i)
        l_b655 = time_str
        l_b656 = time_str
        l_b676 = time_str
        l_b677 = time_str
        l_b678 = time_str
        l_b679 = time_str
        l_b680 = time_str
        l_b681 = time_str
        l_b682 = time_str
        l_b683 = time_str
        l_remark='a'*2**20

        insert_sql = "insert into TB_OGG_TEST(id,fbactid,b655,b656,remark) values ('%s','%s',to_date('%s','YYYY-MM-DD HH24:MI:SS'),to_date('%s','YYYY-MM-DD HH24:MI:SS'),:remark)" %(l_id,l_fbactid,l_b655,l_b656)
        ##print(insert_sql)
        update_sql="update tb_test set name1='%s' where id=%s" %("update"+str_i,i)
        delete_sql="delete from tb_test where id=%s" %(i)
        try:
            # 执行sql语句
            cursor.setinputsizes(remark=cx_Oracle.CLOB)
            cursor.execute(insert_sql,remark=l_remark)
            # 提交到数据库执行
            if (i % 2) == 0:
                db.commit()
        except Exception as err:
            # Rollback in case there is any error
            print("sql语句执行错误", err)
            db.rollback()
    db.commit()
    cursor.close()
    db.close()
    return 0

if __name__ == '__main__':
    print("开始时间:"+time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
    l_flag = insert_data()
    print("结束时间:"+time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))

字段