如何修复错误“Oracle ORA-01036 存在问题：非法变量名/编号”？

Question

我一直在编写有关为 Blob 值插入值的代码，因为我无法在 Oracle 数据库中单独完成它，这让我用 Python 编写它，这似乎是唯一可能的方法。我一直在寻找其他解决方案，但在尝试之后，我似乎还没有找到适合我的程序的解决方案。我正在做的是插入。希望这是有道理的。

# importing module
import cx_Oracle


def convertToBinaryData(filename):
    # Convert digital data to binary format
    with open(filename, 'rb') as file:
        binaryData = file.read()
    return binaryData


def insertBLOB(r_id, r_name, r_email, r_address, r_description, r_hpno, r_whatsapp, r_peakhour, r_rating, r_cert):
    # Create a table in Oracle database
    try:

        con = cx_Oracle.connect('Hazim11/87097980@localhost')
        # Now execute the sqlquery
        cursor = con.cursor()
        cert_pic = convertToBinaryData(r_cert)
        print(con.version)

        sql_insert_blob_query = """INSERT INTO RESTAURANT (R_ID,R_NAME,R_EMAIL,R_ADDRESS,R_DESCRIPTION,R_HPNO,R_WHATSAPP,R_PEAKH,R_RATING,R_CERT) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""

        insert_blob_tuple = (r_id, r_name, r_email, r_address, r_description,
                             r_hpno, r_whatsapp, r_peakhour, r_rating, cert_pic)

        cursor.execute(sql_insert_blob_query, insert_blob_tuple)

        con.commit()

        # Creating a table employee
        # cursor.execute("INSERT INTO RESTAURANT (R_ID, R_NAME, R_EMAIL, R_ADDRESS, R_DESCRIPTION, R_HPNO, R_WHATSAPP, R_PEAKH, R_RATING, R_CERT) VALUES ('R100', 'MasterGrill_0', '[email protected]', '145, Jalan Desa Taman Desa, 58100, Kuala Lumpur', 'Clean and Friendly Staff', '030-1110 3333', '020-1110 3333', '8:00 PM', '8/10',", cert_pic)

        print("INSERT VALUES OF RESTAURANT SUCCESSFUL")

    except cx_Oracle.DatabaseError as e:
        print("There is a problem with Oracle", e)

    # by writing finally if any error occurs
    # then also we can close the all database operation
    finally:
        if cursor:
            cursor.close()
        if con:
            con.close()


insertBLOB("R100", "MasterGrill_0", "[email protected]", "145, Jalan Desa Taman Desa, 58100, Kuala Lumpur", "Clean and Friendly Staff",
           "030-1110 3333", "020-1110 3333", "8:00 PM", "8/10", r"C:\Users\HP\Pictures\Dota 2 Wallpapers\Wallpaper PC.jpg")

Answer 1

cx-oracle 不使用 %s 作为占位符。它使用编号或命名的占位符。如果参数在序列中，请使用数字：:1、:2 等。

sql_insert_blob_query = """
    INSERT INTO RESTAURANT (R_ID,R_NAME,R_EMAIL,R_ADDRESS,R_DESCRIPTION,R_HPNO,R_WHATSAPP,R_PEAKH,R_RATING,R_CERT) 
    VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9)"""

如果参数在字典中，请使用名称：:key1< /code>、:key2 等。

%s 由 MySQL 驱动程序使用。