KeyError with SQL Query

Ignition
1

Hello,

When I ran this script, it appears KeyError fault for line 15 and 65 (bold)…
What can be problem?

File “tagevent:valueChanged”, line 15, in valueChanged
File “tagevent:valueChanged”, line 65, in APIcall
KeyError: lastanswerid

..............
............
..........
			**if APIcall(last_time_sql_updated):** **#LINE 15**
				system.tag.write('[.]refresh_checklist.last_time_updated', utcnow.strftime(time_format))
				system.tag.write('[.]refresh_checklist', 0)
	while True:
		return_value = L2LTR.getChecklist(L2Lparams)
		if return_value['success']:
			if len(return_value['data']) > 0:
				checklists = return_value['data']

				for checklist in checklists:
					if checklist['answers'] != None:
						for answers in checklist['answers']:            		                   		
							if answers['task_hash'] == '9d5c922b886d42756596d336fb39bec2':
								checklist['sonuc'] = answers['answer']
								checklist['lastanswerid'] = answers['task_number'] - 5	                   			
								break
					if not 'sonuc' in checklist:
						checklist['sonuc'] = 0

                    #if not 'sonuc' in checklist:
                    	#checklist['sonuc'] = 0

                    #answers = checklist['answer']
                    #for answer in answers:
                        #answer_query += ", L2L_Question%(task_number)s='%(answer)s'" %answer
                        
					record = system.db.runQuery("select L2L_id from L2L_Checklist where L2L_id='%s'" % checklist['id'], database_name)
                    
					if record.getRowCount() > 0:
						system.db.runUpdateQuery(
							"update L2L_Checklist set L2L_id='%(id)s', L2L_name='%(name)s', L2L_number='%(number)s', "
							"L2L_description='%(description)s', L2L_closed='%(closed)s', L2L_closed_date='%(closeddate)s', "
							"L2L_area='%(area)s', L2L_line='%(line)s', L2L_created='%(created)s', L2L_sonuc='%(sonuc)s', L2L_lastanswerid='%(lastanswerid)s' "
							"where L2L_id='%(id)s'" %checklist, database_name)
					else:
**#LINE 65**
						system.db.runUpdateQuery("INSERT INTO L2L_Checklist VALUES('%(id)s', '%(name)s', '%(number)s', '%(description)s', '%(closed)s', '%(closeddate)s', '%(area)s', '%(line)s', '%(created)s', '%(sonuc)s', '%(lastanswerid)s')" %checklist, database_name)
2

A KeyError means that you are trying to access a key of a dictionary that does not exists. You say that it is happening during your system.db.runUpdateQuery - what do you see if you print the checklist right before that? I would expect you won’t see a key with ‘lastanswerid’.

Also unrelated but you might want to use system.db.runPrepUpdate query, use the keys of your dictionary to populate the columns and the values for the query values. This way you won’t have to worry about adding quotes around your arguments or formatting datetimes etc (as long as you use java datetime objects).

2 Likes
3

I agree with @bkarabinchak.psi:
You're only setting checklist['lastanswerid'] here:

So if checklist['answers'] is None, this key is never added. Unless you have some other place where it is set up and that you're not showing us...

2 Likes
4

Additionally if answers['task_hash'] does not equal your hardcoded value you also never add checklist['lastanswerid'].

2 Likes
5

Indeed, there’s also this.

6

Thanks a lot!
I was overlooked this. I wrote additional condition to run when key value is not available, it worked.

1 Like
7

Nice! Sounds like a good spot to use a try/except clause with KeyError

try:
    # some code where I access a dictionary key
except KeyError, e:
    # Do something in the event it does not exist