pythonoracle-databasepython-oracledb

How to insert Blob in oracle db in python


I am new to python and i am trying to insert a blob to an oracle db table. but its failing with 'TypeError: oracledb.base_impl.DbType object is not callable

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)

                # Bind variables and execute the statement
                cursor.execute(sql, {
                    "orderid": orderid,
                    "create_ts": datetime.now(),  # Use current timestamp
                    "payload": oracledb.BLOB(payload_text.encode())  # Encode text and create BLOB
                })

                connection.commit()  # Commit the transaction
                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)

Solution

  • Instead of oracledb.BLOB(payload_text.encode()) we can use the bytes() function to convert it into bytes and it will get inserted without any issues. Please see below the modified code

                    cursor.execute(sql, {
                        "orderid": orderid,
                        "create_ts": datetime.now(),  # Use current timestamp
                        "payload": bytes(payload_text,'utf-8')  # Encode text and create BLOB
                    })