未使用Python将行添加到Oracle DB表列

qgelzfjb  于 2023-03-29  发布在  Oracle
关注(0)|答案(1)|浏览(142)

我编写了一个Python脚本来向“CLIENT_NUMBER”列添加信息,但它不起作用,这意味着它不向列添加数字。如果在DBeaver中运行SQL脚本时,所有内容都被添加了,为什么会出现这种情况?

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}")

我使用以下方法填充行:

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

在Python脚本中,我删除了分号,但仍然不成功。没有错误,并显示消息:“Information”,f”{len(client_numbers)} clients added to the {table_name} table”问题出在Python脚本本身。我不明白为什么。数据库本身是可操作的,所有内容都连接得很好,没有任何问题。您知道问题可能是什么,需要修复什么吗?
我用的是甲骨文数据库。
我试过很多方法,但都无济于事

pokxtpni

pokxtpni1#

只需尝试:

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

对sys.odcivarchar2list应用表函数对我来说没有意义,因为sys.odcivarchar2list已经返回了一个表。

相关问题