Python向Oracle数据库CLIENT_NUMBER列插入数据失败求助

我编写了Python脚本用于向Oracle数据库的CLIENT_NUMBER列添加数据，脚本无报错且弹出提示显示数据已添加，但实际数据并未插入到列中。在DBeaver中运行对应的SQL语句可以成功插入，数据库连接正常，问题出在Python脚本本身。

我的Python脚本 #

def insert_client_numbers(connection, cursor, table_name):
    client_numbers = client_numbers_text.get("1.0", tk.END).strip()

    if not client_numbers:
        messagebox.showerror("Error", "Please enter client numbers")
        return

    client_numbers = client_numbers.split('\n')

    # Create a string with comma-separated values
    client_numbers_str = ",".join(["'{}'".format(client_number) for client_number in client_numbers])

    # Use sys.odcivarchar2list for multiple insertion
    insert_statement = f"""
    INSERT INTO {table_name} (CLIENT_NUMBER)
    SELECT column_value FROM TABLE(sys.odcivarchar2list('{client_numbers_str}'))
    """

    try:
        cursor.execute(insert_statement)
        connection.commit()
    except Exception as e:
        messagebox.showerror("Error", f"Failed to add client numbers to the table: {str(e)}")
        return

    # Check that the rows have been added
    cursor.execute(f"SELECT * FROM {table_name}")
    rows = cursor.fetchall()

    # Convert the list of tuples to a string with the required formatting
    rows_formatted = ",\n".join([f"('{row[0]}')" for row in rows])
    print(f"Current rows in the table {table_name}:\n{rows_formatted}")  # Debug message

    messagebox.showinfo("Information", f"{len(client_numbers)} client numbers successfully added to the table {table_name}")

DBeaver中可正常运行的SQL语句 #

INSERT INTO virtual_magican (CLIENT_NUMBER)
SELECT column_value FROM TABLE(sys.odcivarchar2list('5533380','5651238', '75405689','9375691'));

• SQL拼接逻辑错误：脚本中生成的client_numbers_str会把所有客户号合并成一个带逗号的字符串（比如输入123和456会变成'123,456'），但sys.odcivarchar2list需要的是多个独立的单引号包裹参数（'123','456'），这导致实际只插入了一个包含逗号的无效值，而非多个目标客户号。
• 缺失SQL调试输出：脚本未打印最终生成的SQL语句，无法直观对比与DBeaver中有效SQL的差异。
• 未处理无效输入：未对拆分后的客户号做去空、去空格处理，若输入有空行或空格，会插入无效数据。

方案1：修正SQL拼接逻辑（快速修复） #

调整client_numbers_str的生成方式，确保每个客户号都被单独包裹单引号，再用逗号分隔：

# 替换原拼接代码
client_numbers_str = ",".join([f"'{num.strip()}'" for num in client_numbers if num.strip()])

修改后生成的SQL会和DBeaver中的一致，sys.odcivarchar2list能正确识别多个客户号参数。

方案2：使用参数化查询（推荐，防SQL注入） #

拼接字符串存在SQL注入风险，更安全的方式是利用Oracle的数组参数绑定：

def insert_client_numbers(connection, cursor, table_name):
    client_numbers = client_numbers_text.get("1.0", tk.END).strip()

    if not client_numbers:
        messagebox.showerror("Error", "Please enter client numbers")
        return

    # 过滤空行并去除每个客户号的多余空格
    client_numbers = [num.strip() for num in client_numbers.split('\n') if num.strip()]

    if not client_numbers:
        messagebox.showerror("Error", "No valid client numbers entered")
        return

    # 参数化的批量插入语句
    insert_statement = f"""
    INSERT INTO {table_name} (CLIENT_NUMBER)
    SELECT column_value FROM TABLE(sys.odcivarchar2list(:nums))
    """

    try:
        # 设置参数类型为字符串数组
        cursor.setinputsizes(nums=cx_Oracle.STRING)
        cursor.execute(insert_statement, nums=client_numbers)
        connection.commit()
    except Exception as e:
        messagebox.showerror("Error", f"Failed to add client numbers to the table: {str(e)}")
        return

    # 验证实际插入数量
    placeholders = ",".join([":p" + str(i) for i in range(len(client_numbers))])
    cursor.execute(f"SELECT COUNT(*) FROM {table_name} WHERE CLIENT_NUMBER IN ({placeholders})", client_numbers)
    inserted_count = cursor.fetchone()[0]

    print(f"Actual inserted client numbers: {inserted_count}")
    messagebox.showinfo("Information", f"{inserted_count} client numbers successfully added to the table {table_name}")

• 在执行SQL前添加打印语句，查看生成的SQL是否符合预期：

  print("Generated SQL:", insert_statement)
  

• 验证插入结果时，不要直接查询全表，而是根据插入的客户号精准统计，避免因表中原有数据干扰判断。

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