python - bind parameters by array index - Stack Overflow

admin管理员组

文章数量:1277899

I'm trying to migrate from cx_Oracle 8.1.0 to oracledb 2.5.1 library. Before such query was working:

update command_center set 
            E_ID = :1,
            E_ASPECTS = :2,
            E_CREATED_TIMESTAMP = :3
WHERE e_id = :1

def bulk_insert_or_update_data_to_targets(self, sql, target_data, id_index=0):
    for target_connections, data in target_data:
        for con in target_connections:
            cursor = con.cursor()
            cursor.executemany(sql, data, batcherrors=True)
            if cursor.getbatcherrors():
                self.logger.debug("data: " + str(data))
            cursor.close()

but currently I have following exception:

oracledb.exceptions.DatabaseError: DPY-4009: 4 positional bind values are required but 3 were provided

Is there any chance this could work the same way it worked in cx_Oracle before ... so I guess it retrieved the attribute by array index?

I'm trying to migrate from cx_Oracle 8.1.0 to oracledb 2.5.1 library. Before such query was working:

update command_center set 
            E_ID = :1,
            E_ASPECTS = :2,
            E_CREATED_TIMESTAMP = :3
WHERE e_id = :1

def bulk_insert_or_update_data_to_targets(self, sql, target_data, id_index=0):
    for target_connections, data in target_data:
        for con in target_connections:
            cursor = con.cursor()
            cursor.executemany(sql, data, batcherrors=True)
            if cursor.getbatcherrors():
                self.logger.debug("data: " + str(data))
            cursor.close()

but currently I have following exception:

oracledb.exceptions.DatabaseError: DPY-4009: 4 positional bind values are required but 3 were provided

Is there any chance this could work the same way it worked in cx_Oracle before ... so I guess it retrieved the attribute by array index?

• python
• cx-oracle
• python-oracledb

Share Improve this question Follow asked Feb 24 at 15:47 bilakbilak 4,96455 gold badges4646 silver badges8686 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

• Enable 'thick mode', see https://python-oracledb.readthedocs.io/en/latest/user_guide/initialization.html#enabling-python-oracledb-thick-mode
• Use named bind variables, instead of bind-by-position.

The latter is preferred because it doesn't run into the kind of issue you see. Even Thick mode has some variances with bind-by-position that depend if you are using SQL or PL/SQL statements.

I think the problem may be due to using :1 twice in the query.

You don't need E_ID = :1 in the SET clause, since you're setting the column to the same value it already has (because of WHERE E_ID = :1). So remove that:

update command_center set 
        E_ID = :1,
        E_ASPECTS = :2,
            E_CREATED_TIMESTAMP = :3
WHERE e_id = :1

本文标签： pythonbind parameters by array indexStack Overflow