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

8fsztsew 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(125)

我试图插入一些记录到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中创建预处理语句还是应该使用绑定变量？

oracle

来源：https://stackoverflow.com/questions/77110088/python-function-to-create-an-oracle-db-insert-prepared-statement

1条答案

按热度按时间

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会将未加引号的标识符转换为大写，因此您可能需要确保传递给函数的所有标识符也都是大写。

赞(0）回复(0）举报 2023-10-16

我来回答

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

1条答案

相关问题

热门标签

最新问答