Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://github.com/oracle/python-oracledb/issues/147 below:

`cursor.rowcount` is higher than the number of rows returned by `cursor.fetchall` · Issue #147 · oracle/python-oracledb · GitHub

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=}")

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

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

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

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4