Python cx_Oracle executemany() how many rows inserted into DB

Question

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

Answer 1

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.

Answer 2

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>

Answer 3

Method executemany has a parameter named "parameters". The "parameters" is a list of sequences/dictionaries. Size of this list determines how many times the statement is executed (each time the statement is executed, database returns number of rows affected). Finaly you can get this information, but it will be a list of integers ( one for each execution)

Let me :

SQL> Create table abc_1
      (id number)
      ;
    Table created

Python:

dsn = cx_Oracle.makedsn("xxx.xxx.xxx.xxx", 1521, service_name="xxxx")
con = cx_Oracle.connect(user="xxxx", password="xxxx", dsn=dsn)

tab_cursor = con.cursor()
tab_query = '''
    insert into abc_1 (id) select :x from dual  where :y >= level connect by level<=10
'''

foo = {"x": 1, "y": 5} # it will insert 5 rows
foo2 = {"x": 1, "y": 6} # it will insert 6 rows
foo3 = {"x": 1, "y": 10} # it will insert 10 rows

tab_cursor.executemany(tab_query, parameters=[foo, foo2, foo3], arraydmlrowcounts=True)

print("Rows inserted:", tab_cursor.getarraydmlrowcounts())

Output shows how many rows was inserted for each execution:

Rows inserted: [5, 6, 10]