python cx_oracle cursor.rowcount returning 0 but cursor.fetchall returns data

Question

I have this code where I am executing a select sql statement from python code using cx_oracle package:

import cx_Oracle

try:
    cur = conn.cursor()
    result = cur.execute('select * from table1')
    print(str(cur.rowcount))
    print(cur.fetchall())

except Exception as e:
    print(e)

When I execute the above code I see 0 coming in for cur.rowcount but I see following data getting printed for cur.fetchall():

[('185',), ('1860',), ('1908',)]

cx_Oracle package documentation does mention Cursor.rowcount as a valid operation so I am not sure why in my code it is returning 0 even though the data is coming?

What if you print(str(cur.rowcount)) after print(cur.fetchall())? — CristiFati
– CristiFati, Commented Aug 31, 2017 at 16:58
@CristiFati Yes then it does print. Why is this behavior? Is it the requirement in cx_Oracle package? In pymysql package it doesn't need to be this way — user2966197
– user2966197, Commented Aug 31, 2017 at 17:18
I've never used cx_Oracle (at least not directly). But, quote from the doc (link you provided): "This read-only attribute specifies the number of rows that have currently been fetched from the cursor ...". Regarding pyMySQL, I know nothing of that either, but they are 2 separate packages wrapping 2 different DB systems.... I don't think there's any restriction for them to behave the same way. — CristiFati
– CristiFati, Commented Aug 31, 2017 at 17:26

Anthony Tuininga · Accepted Answer · 2017-09-01 22:02:19Z

9

The documentation states that cursor.rowcount specifies the number of rows that have currently been fetched. Immediately after the cursor.execute() call has been made, no rows have been fetched, so the result is 0. If you call cursor.fetchone() then result will be 1 and if you call cursor.fetchmany(5), then the result will be 6, and so forth (assuming there are enough rows to satisfy your requests, of course!).

answered Sep 1, 2017 at 22:02

Anthony Tuininga

7,2162 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gerrat · Accepted Answer · 2020-02-14 15:55:42Z

6

cx-oracle.readthedocs mentioned Cursor.rowcount specified number of rows affected by insert, update and delete statement. You are using a select statement.

cur.execute('select * from table1')
result = cur.fetchall()
print (len(result)) # this will return number of records affected by select statement
print (result)

edited Feb 14, 2020 at 15:55

Gerrat

29.9k9 gold badges78 silver badges104 bronze badges

answered Aug 31, 2017 at 17:40

S.Yang

1163 bronze badges

2 Comments

user2966197 Over a year ago

I don't think thats true. The document says

This read-only attribute specifies the number of rows that have currently been fetched from the cursor (for select statements)

S.Yang Over a year ago

Your question is explained here: [link] (bitbucket.org/anthony_tuininga/cx_oracle/issues/16/…)

Collectives™ on Stack Overflow

python cx_oracle cursor.rowcount returning 0 but cursor.fetchall returns data

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related