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.
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!-)