创建Oracle DB插入准备语句的Python函数

8fsztsew  于 2023-10-16  发布在  Oracle
关注(0)|答案(1)|浏览(124)

我试图插入一些记录到Oracle数据库表。我在python中构建了一个DB连接器,并具有构建准备好的语句所需的函数,但是插入一直失败,并出现错误:
"oracledb.exceptions.DatabaseError: DPY-4009: 0 positional bind values are required but 13 were provided"
下面是我的函数:

def insert(self, schema: str, table_name: str, column_names: List, values: tuple):
        """
        Inserts one row into the specified table.

        :param table_name: The name of the table to insert data into.
        :param values: A tuple containing the values to insert into a row.
        """
        column_name_placeholders = ', '.join(f'"{columns}"' for columns in column_names)
        values = self.replace_none_with_null(values)
        statement = f"INSERT INTO {schema}.{table_name} ({column_name_placeholders}) VALUES ({column_name_placeholders})"
        cursor = self.get_connection().cursor()
        cursor.execute(statement, values)
        cursor.commit()
        cursor.close()

我用空字符串''替换了'None'值。列表值的长度也等于13(与占位符的长度相同?')包括空字符串。
我创建了一个测试函数来测试正在准备什么语句,它确实准备了正确的语句:

INSERT INTO random_table ("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8", "col9", "col10", "col11", "col12", "col13") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);

我能做错什么?另外,上面的最佳实践是在python中创建预处理语句还是应该使用绑定变量?

tyky79it

tyky79it1#

您希望在查询中包括绑定值占位符。
使用?作为匿名绑定值:

def insert(self, schema: str, table_name: str, column_names: List, values: tuple):
    """
    Inserts one row into the specified table.

    :param table_name: The name of the table to insert data into.
    :param values: A tuple containing the values to insert into a row.
    """
    assert len(column_names) == len(values), "Should have equal number of columns and values."
    column_name_placeholders = ', '.join(f'"{columns}"' for columns in column_names)     
    bind_placeholders = ', '.join("?" for _ in values)
    values = self.replace_none_with_null(values)
    statement = f'INSERT INTO "{schema}"."{table_name}" ({column_name_placeholders}) VALUES ({bind_placeholders})'
    cursor = self.get_connection().cursor()
    cursor.execute(statement, values)
    cursor.commit()
    cursor.close()

:name用于命名绑定值:

def insert(self, schema: str, table_name: str, column_names: List, values: tuple):
    """
    Inserts one row into the specified table.

    :param table_name: The name of the table to insert data into.
    :param values: A tuple containing the values to insert into a row.
    """
    assert len(column_names) == len(values), "Should have equal number of columns and values."
    column_name_placeholders = ', '.join(f'"{column}"' for column in column_names)     
    bind_placeholders = ', '.join(f":{column}" for column in column_names)
    values = self.replace_none_with_null(values)
    statement = f'INSERT INTO "{schema}"."{table_name}" ({column_name_placeholders}) VALUES ({bind_placeholders})'
    cursor = self.get_connection().cursor()
    cursor.execute(statement, values)
    cursor.commit()
    cursor.close()
  • 注意:在Oracle中,如果您使用带引号的标识符(由"双引号包围),那么您必须**使用正确的标识符大小写。默认情况下,Oracle会将未加引号的标识符转换为大写,因此您可能需要确保传递给函数的所有标识符也都是大写。

相关问题