cursor.rowcount is higher than the number of rows returned by cursor.fetchall · Issue #147 · oracle/python-oracledb (original) (raw)

Hello,

  1. What versions are you using?

oracle database: 19c (19.12.0.0.0) platform.platform: Windows-10-10.0.19043-SP0 sys.maxsize > 2**32: True platform.python_version: 3.8.10 oracledb.version: 1.2.2

  1. Is it an error or a hang or a crash?

Unexpected behaviour.

  1. What error(s) or behavior you are seeing?

I originally posted here with more detail:
https://stackoverflow.com/questions/75487587/why-is-cursor-rowcount-higher-than-the-number-of-rows-returned-by-cursor-fetchal

When calling cursor.fetchall() (or cursor.fetchmany() in batches), the cursor.rowcount attribute is higher than the actual number of rows returned. However, this issue does not appear when using the same methods in cx_Oracle.

For example, when selecting all records from a table with 93 rows, cursor.rowcount is 96, whereas I would expect it to be 93 to match the number of rows in the table.

Notes on output (see output section below):

  1. After the first batch, rowcount is already 20 - I would have expected it to be 10 here
  2. After batch 8, rowcount is 100 - it has 'overshot' the actual table row count - which seems to indicate rowcount does not necessarily reflect the number of rows actually fetched, but the number of rows attempted to be fetched, at least initially
  3. After the final batch (and also after the fetchall() call), rowcount is 96 - I would expect it to be 93 here
    • From testing with other values for arraysize, it seems to follow the pattern r = t + (t mod a) where r = rowcount, t = total table rows, and a = arraysize
    • To illustrate, another example on the same table using arraysize = 20:
      After execute() call: cursor.rowcount=0
      After fetchall() call: cursor.rowcount=106
  4. Does your application call init_oracle_client()?

No.

  1. Include a runnable Python script that shows the problem.

import oracledb

SQL = "select id from example_table"

un = 'abc' pw = 'def' cs = 'ghi'

with oracledb.connect(user=un, password=pw, dsn=cs) as connection: with connection.cursor() as cursor: cursor.prefetchrows = 0 cursor.arraysize = 10 cursor.execute(SQL) print(f"After execute() call: {cursor.rowcount=}")

    batch = 0
    while True:
        rows = cursor.fetchmany()
        if not rows:
            break
        print(f"Batch {batch}: {cursor.rowcount=}")
        batch += 1

    rows = cursor.fetchall()
    print(f"After fetchall() call: {cursor.rowcount=}")

SQL

-- Create a table of ids from 1 to 93 create table example_table as

select rownum as id from dual connect by rownum <= 93

Output

After execute() call: cursor.rowcount=0 Batch 0: cursor.rowcount=20 Batch 1: cursor.rowcount=30 Batch 2: cursor.rowcount=40 Batch 3: cursor.rowcount=50 Batch 4: cursor.rowcount=60 Batch 5: cursor.rowcount=70 Batch 6: cursor.rowcount=80 Batch 7: cursor.rowcount=90 Batch 8: cursor.rowcount=100 Batch 9: cursor.rowcount=96 After fetchall() call: cursor.rowcount=96