Python cx_oracle executemany how many rows insert into DB-CodePudding

I use cx_oracle executemany function to insert data into a table in Oracle. I would like to check after commit the action what is the actual amount of records append to the table. can it be done and how? Thanks

CodePudding user response：

I don't use Python, but - as your question is related to Oracle and if you can "move" the insert process into a stored procedure you'd then call from Python, you could utilize sql%rowcount attribute which returns number of rows affected by the most recent SQL statement ran from within PL/SQL.

Here's an example:

SQL> set serveroutput on
SQL> begin
  2    insert into test (id, name)
  3      select 1, 'Little' from dual union all
  4      select 2, 'Foot'   from dual union all
  5      select 3, 'Amir'   from dual;
  6    dbms_output.put_line('Inserted ' || sql%rowcount || ' row(s)');
  7  end;
  8  /
Inserted 3 row(s)
         ^
         |
    value returned by SQL%ROWCOUNT

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

If you are using a cursor with the executemany method, then use the cursor's rowcount attribute to retrieve the number of rows affected by executemany.

There are many nuances associated with the Cursor objects executmany method fro SELECT and DML statements. Take a look at the cx_Oracle documentation for details at https://cx-oracle.readthedocs.io/en/latest/user_guide/batch_statement.html#batchstmnt

It may be helpful if you could post a code snippet of what is being attempted to elicit a more accurate response.