python 操作Oracle数据库对clob的插入、更新和读取

使用insert、update操作数据库时，字符超过4000会提示字符串过长，导致数据不能成功插入或更新，
解决方案就是将字符转换成clob对象写入

import cx_Oracle
from config import ORACLE_TC3_USER, ORACLE_TC3_PASSWORD, ORACLE_TC3_DB


def oracle_connect(username, password, db):
    """oracle 连接"""

    oracel_conn = cx_Oracle.connect(username, password, db)
    cursor = oracel_conn.cursor()

    return oracel_conn, cursor


oracel_conn, oracle3_client = oracle_connect(ORACLE_TC3_USER, ORACLE_TC3_PASSWORD, ORACLE_TC3_DB)


# 更新/插入4000+字符串
info = "我是超长的字符串" * 4000
# 将信息转换成clob对象
clob_data = oracle3_client.var(cx_Oracle.CLOB)
clob_data.setvalue(0, info)
# 将最新的聊天记录列表更新至数据库
update_info = "update USER_ROOM set info=:1 where room_name='{0}'".format("wanger_lisi")

oracle3_client.prepare(update_info)
oracle3_client.execute(None, {"1": info})
oracel_conn.commit()

当然，查询clob类型的数据也要按照相应的方法去读取

# clob数据读取
info_sql = "select * from USER_ROOM where room_name='{0}'".format("wanger_lisi")
info_query = oracle3_client.execute(info_sql).fetchone()
if info_query:
    # clob对象读取
    if info_query[4]:
        info = info_query[4].read()
    else:
        info = None

打赏

如果您觉得不错，请打赏作者吧～