Cursors and loops - Printable Version

Cursors and loops - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Cursors and loops (/thread-13055.html)

Cursors and loops - Clark - Sep-26-2018

Hello all

Very new Python user here, so be gentle.

If there is a better way to do what I am trying to do (self evident from the code), please let me know.

My problem is that the program starts to go through the loop once. If I uncomment out the mycursor.close, it fails on the mycursor.close line. If I leave it commented then it fails on the second iteration of the loopo at the assignment of mycursor = mydb.cursor().

for dfct in defects:
	if dfct == 0:
		break
	sql = "SELECT * FROM table_def_int_data WHERE defect_id='" + str(dfct) + "'"	
	mycursor = mydb.cursor()
	mycursor.execute(sql)
	myresult = mycursor.fetchone()
	print(myresult)
#	mycursor.close()

Please let me know what I am doing incorrect here. (Probably many things ha ha)

TIA

RE: Cursors and loops - Clark - Sep-26-2018

Never mind. Trial and error got me there eventually. :)

Still, if anyone has any useful tips about my coding that would be nice.

Here is the working version:

mycursor = mydb.cursor()
for dfct in defects:
	if dfct == 0:
		break
	# first get the data_id of the latest revision
	sql = "SELECT * FROM table_def_int_data WHERE defect_id='" + str(dfct) + "' AND revision = (SELECT MAX(revision) from table_def_int_data where defect_id='" + str(dfct) + "')"
	mycursor.execute(sql)
	myresult = mycursor.fetchone()
	sql = "SELECT * FROM table_def_int_data WHERE defect_id='" + str(dfct) + "' AND data_id = " + str(myresult[0])
	mycursor.execute(sql)
	myresult = mycursor.fetchone()
	print(myresult)
mycursor.close()