before getting to question I should tell you that I'm a rookie.
My problem is I have 2 cursors (on different sql connections), first cursor gets results from sql database, while second cursor makes changes to rows in the results. Problem is I want to refresh contents of the first cursor's result once the second cursor makes changes.
My cursors work on buffered sql connections.
Part of code that I got stuck (I deleted some unnecessary lines) :
// more loops here before the sample -
cursor2.execute(query2)
for result in cursor2 :
if (str(result[3]) == "None") :
name = result[1]
query3 = ("UPDATE accounts SET entry1 = %s WHERE name = %s")
cursor3.execute(query3,(entry,name))
sql3.commit()
break
elif (str(result[4]) == "None") :
name = result[1]
query3 = ("UPDATE accounts SET entry2 = %s WHERE name = %s")
cursor3.execute(query3,(entry,name))
sql3.commit()
break
This sample seems not helpful but; Result[3] gives me column "entry1" , result[4] = "entry2".
Cursor3 changes content of entry1 or entry2 depending on the situation. But after cursor3 commited , when loop returns to "if (str(result[3]) == "None") :" part, it still sees result[3] as None. When I check MysqlDatabase , "entry1" seems succesfully updated. I think I somehow need to refresh result in cursor2 but I don't know how.
I tried to turn buffered to false but I had "unreaded result" errors that I couldn't get rid of. (like I said I'm a rookie). Any advice ?
You are getting unread result found error with normal cursor because you have not read all
the rows from cursor2 and you are sending queries through cursor3 on the same connection.
With buffered=True, the cursor fetches all rows within execute and keeps them in memory, but as the data is in memory and not fetched from server in every iteration, it contains stale data.
So to update the data in every iteration, you need to run cursor2.execute every time, also use cursor.fetchall() which returns a tuple of tuples, each tuple being a row.
cursor2.execute(query2)
rows = cursor2.fetchall()
while i < len(rows):
result = rows[i]
i += 1
if (str(result[3]) == "None") :
name = result[1]
query3 = ("UPDATE accounts SET entry1 = %s WHERE name = %s")
cursor3.execute(query3,(entry,name))
sql3.commit()
cursor2.execute(query2)
rows = cursor2.fetchall()
i=0
elif (str(result[4]) == "None") :
name = result[1]
query3 = ("UPDATE accounts SET entry2 = %s WHERE name = %s")
cursor3.execute(query3,(entry,name))
sql3.commit()
cursor2.execute(query2)
rows = cursor2.fetchall()
i=0
But this is not at all a good solution since for every row containing entry1 or entry 2 as None you will be doing one execute and will fetching all data
The good way is to call cursor.execute() once and then use the rows = cursor.fetchall() and update all rows in the table based on the data get for the first time.