CODEWITHSUNDEEP
pythonsqlcx-oracle
Parag Ahire
Parag Ahire

Reputation: 37

cx_Oracle query returns zero rows

Why does the below code not work ? It returns zero rows even though I have many multiple matching the search criteria.

A simple query of the form select * from Table_1 works fine and returns positive number of rows

import cx_Oracle
def function_A (data):
    connection = cx_Oracle.connect('omitted details here')

    for index in range(len(data)):
        # connection is already open
        cursor = connection.cursor()

        query = "select * from Table_1 where column_1=:column1 and column_2=:column2 and column_3=:column3 and column_4=:column4"
        bindVars={'column1':data[index][3], 'column2':data[index][4], 'column4':data[index][5], 'column5':data[index][6]}
        cursor.execute(query, bindVars)
        cursor.arraysize = 256
        rowCount = 0
        resultSet = cursor.fetchall()
        if (resultSet != None):
            logger.debug("Obtained a resultSet with length = %s", len(resultSet))
            for index in range(len(resultSet)):
                logger.debug("Fetched one row from cursor, incrementing counter !!")
                rowCount = rowCount + 1
                logger.debug("Fetched one row from cursor, incremented counter !!")
            logger.debug("Successfully executed the select statement for table Table_1; that returned %s rows !!", rowCount)
            logger.debug("Successfully executed the select statement for table Table_1; that returned %s rows !!", cursor.rowcount)

Please ignore minor formatting issues, code runs just does not give me a positive number of rows.

Code is being run on IBM AIX with python2.6 and a compatible version of cx_Oracle.

Upvotes: 2

Views: 8084

Answers (3)

Roy learns to code
Roy learns to code

Reputation: 89

banging my head against monitor on this one... you have to do something like below to check, as the cursor value changes once you operate on it:

    result_set = DB_connector.execute(sql)
    result_list = result_set.fetchall()  # assign the return row to a list
    if result_set.rowcount == 0:
        print('DB check failed, no row returned')
        sql_result_set = None
    else:
        for row in result_list:  # use this instead result_set 
            print('row fetched: ' + row[0])
            sql_result_set.append(row[0])
            print('DB test Passed')

Upvotes: 0

Ward
Ward

Reputation: 2852

Oracle CX's cursor object has a read-only rowcount property. Rowcount is returning how many rows are returned with fetch* methods.

Say the query yields 5 rows, then the interaction is like this

  1. execute rowcount = 0
  2. fetchone rowcount = 1
  3. fetchone rowcount = 2
  4. fetchall rowcount = 5

Thay way you do not need to manually track it. Your query issues will have to be resolved first offcourse :)

Upvotes: 5

Ben
Ben

Reputation: 52863

Your query returns 0 rows because there are 0 rows that match your query. Either remove a predicate from your WHERE clause or change the value you pass into one.

It's worth noting that you're not binding anything to column3 in your bindVars variable. I'm also not entirely certain why you're iterating, cursor.rowcount, as you have it gives you the number of rows that have been fetched by the cursor.

Generally, if you think a SELECT statement is not returning the correct result then take it our of code and run it directly against the database. Bind all variables first so you can see exactly what you're actually running.

Upvotes: 1

Related Questions