如何在Python中向Oracle数据库插入BLOB？解决TypeError报错问题

错误原因 #

你触发的TypeError: oracledb.base_impl.DbType object is not callable，是因为错误地将oracledb.BLOB当作构造函数调用了。oracledb.BLOB是数据库类型的常量标识，不是可实例化的函数，不能直接传入参数。

修正方案 #

方案一：直接传入字节数据（推荐） #

oracledb会自动将Python的bytes类型映射为Oracle的BLOB类型，无需手动创建BLOB对象。只需将绑定变量中的oracledb.BLOB(payload_text.encode())替换为编码后的字节数据即可。

方案二：显式指定绑定类型（可选） #

如果需要明确声明字段类型，可以在执行SQL前调用cursor.setinputsizes()指定类型，再传入字节数据。

修正后的完整代码 #

import oracledb
from datetime import datetime

# Connection details (replace with your credentials)
username = "username"
password = "password"
dsn = "database"

# Table and data details
table_name = "TRX_DTL"
orderid = 12345
payload_text = "This is the BLOB text to be inserted."

# Create a connection
try:
    with oracledb.connect(user=username, password=password, dsn=dsn) as connection:
        try:
            with connection.cursor() as cursor:
                # Construct the INSERT statement with bind variables
                sql = """
                INSERT INTO {} (orderid, create_ts, payload)
                VALUES (:orderid, :create_ts, :payload)
                """.format(table_name)

                # 方案一：直接传入编码后的字节数据
                cursor.execute(sql, {
                    "orderid": orderid,
                    "create_ts": datetime.now(),
                    "payload": payload_text.encode('utf-8')  # 直接传bytes类型
                })

                # 方案二（可选）：显式指定类型后传入字节数据
                # cursor.setinputsizes(payload=oracledb.BLOB)
                # cursor.execute(sql, {
                #     "orderid": orderid,
                #     "create_ts": datetime.now(),
                #     "payload": payload_text.encode('utf-8')
                # })

                connection.commit()
                print("BLOB data inserted successfully!")

        except oracledb.Error as e:
            print("Error occurred:", e)

except oracledb.Error as e:
    print("Error connecting to database:", e)

内容的提问来源于stack exchange，提问作者Nevin Thomas