pythonsqlcx-oracledatabase-cursorfetchall

python cx_Oracle cursor returns no rows for valid query

Beating my head against my desk on this one.

My cx_Oracle cursor is returning no rows for a valid query and I cannot figure out why.

The same connection returns expected results from other tables in same schema... for example if I simply change the referenced table name in the query from TABLE_A to TABLE_B it works fine.

The query works fine in SQL Developer/SQL Plus using the same user/connection string. I copied/pasted from cursor.statement during debug to verify exactly same statement, no typos.

I have reduced the queries to the simplest form possible;

1. select * from SCHEMA.TABLE_A

and

2. select * from SCHEMA.TABLE_B

Query 1 returns no rows when executed using cx_Oracle, but DOES return expected rows in SQLPlus/SQL Developer, etc.

Query 2 works as expected using cx_Oracle

I have verified privileges - but this is obviously working as I am using (and verified) the user/connection is same in cx_Oracle as in SQL tools.

the code couldn't be reduced/simplified anymore I do not think to isolate the problem - but I am probably being blind to something really obvious:

import cx_Oracle

db_conn=cx_Oracle.connect('user/password@localhost:1521/TEST_PDB')
cur = db_conn.cursor()
qry = 'select * from SCHEMA_NAME.TABLE_A'
cur.execute(qry)

{cx_Oracle.Cursor on {cx_Oracle.Connection to user@localhost:1521/TEST_PDB}}

cur.fetchall()

[]

cur.rowcount

0

Yet, the other table/query works:

qry = 'select * from SCHEMA_NAME.TABLE_B'
cur.execute(qry)
cur.fetchall()

[(12320573611891L, '23.5.2126981', 0, 'NEW', 'UPDATE', datetime.datetime(2016, 5, 24, 9, 0), 48, 0, None, None )]

This is the ONLY table I have encountered this issue with - that I know of so far.

I am using Oracle 12c, python 2.7.11 64bit, and ojdbc6.jar, and 64bit InstantClient

Any ideas ? Spot anything where my eyes or brain is just not seeing the obvious ?

Solution

  • I figured out where I was being blind to the issue.

    The records were not COMMITED. I was able to query the records in the session in which I inserted them (SQL Developer), using the same user.

    Which led to "what the heck is going on" I should have known / better.