Nested for loop with cursors only gets the first desired result on python

Question

I have the following piece of code I've been working on and I can only get to do the trick for the first row of the "input" table. I know there is clearly no relation between the two cursors on the nested loop but I have tried a nested while-for loop and obtained the same result. I am using python 3.4.

The idea is to compare rows in table input with rows in table comparetable and if the result given by the formula is less than 5 print the correspondents ids (row[0] & rowx[0]) of the given tables and the result of the formula in a new different table called output. The problem is it only works for the first record of the input table.

db_connection = pypyodbc.connect(connection_str)
db_connection2 = pypyodbc.connect(connection_str)
db_connection3 = pypyodbc.connect(connection_str)

db_cursor = db_connection.cursor()
db_cursor2 = db_connection2.cursor()
db_cursor3 = db_connection3.cursor()

db_cursor.execute("SELECT * FROM input")
db_cursor2.execute("SELECT * FROM comparetable")

for row in db_cursor:
    for rowx in db_cursor2:
        idoriginal = row[0]
        idcomparable = rowx[0]
        result = formula(float(row[1]), float(row[2]), float(rowx[1]), float(rowx[2]))
        if result < 5:
            db_cursor3.execute("INSERT INTO output (id, idcomparable, result) VALUES (?, ?, ?)",(idoriginal,
                                idcomparable, result))
        db_cursor3.commit()

Any ideas? Hope is a silly thing. I have already checked this post but I still dont know how to solve it.

How do you initialize `db_connection`, `db_connection2` and `db_connection3`? — NPE, Dec 20 '14 at 21:38
I edited the code to answer the second question. The idea is to compare rows in table input with rows in table comparetable and if the result given by the formula is less than 5 print the correspondents ids (row[0] & rowx[0]) of the given tables and the result of the formula in a new different table called output. The problem is it only works for the first record of the input table. — Maik, Dec 20 '14 at 21:48

score 1 · Accepted Answer · answered Dec 20 '14 at 21:52

The for rowx in db_cursor2: "exhausts" db_cursor2 the first time through -- nothing is left to loop on the second and later times through the outer loop.

So, you must move the db_cursor2.execute("SELECT * FROM comparetable") to inside the outer loop for row in db_cursor:, just before the inner loop for rowx in db_cursor2: so the latter will have rows to loop upon over and over again!-)

Nested for loop with cursors only gets the first desired result on python

1 Answers1