List Comprehension not working in WebDev Resourse

I’m fairly new to python in general let alone the mix of jython and java which can be used scripting inside of Ignition, so it’s entirely possible that I’m missing something obvious here. I couldn’t find it though so I thought I would bring it here and have someone with a better knowledge teach me something.

I am writing a WebDev python resource file which acts as a data server for a mobile intranet dashboard. In this code I need to take the results of two separate queries and combine them. For reasons I am unaware of exactly the related data is stored in two separate database servers. The data and resulting data looks similar to below:

query1heads = ['ticket','id','grossWeight','tareWeight']
query2heads = ['id','name','type']

combinedDataHeads = ['ticket','id','grossWeight','tareWeight','name','type']

If the two tables were in the same database instance or even on the same server this is a fairly simple SQL operation, but alas they are not. I use system.db.runPrepQuery to return the data, then I tried to use a list comprehension to combine the two data sets, this all works in the script console, however, when I run the script live an error is generated stating that the variable i is referenced prior to being set.

#query1 and query2 are pyDataSets returned from system.db.runPrepQuery

q1 = [[col for col in row] for row in query1]
q2 = [[col for col in row] for row in query2]

#this line is where the error is generated live, but this works without error in the script console
combinedData =  [i + [q[1],q[2]] for q in q2 if q[0] == i[2] for i in q1]

I’ve worked around this by using what I would call more traditional for loops but I believe that both code snipits evaluate to the same result.

for i in q1:
     for q in q2:
          if q[0] == i[2]:
               combinedData.append(i + [q[1],q[2]])

Perhaps I’ve missed something obvious but I’m not sure so any insight into why this would work in the script console but not when run live from a WebDev Python resource would be much appreciated.

We are currently running Ignition 7.9.3 in case that is of importance.

Try moving the ‘if q[0] == i[2]’ to the end of the comprehension.
Consider breaking your join into two operations. One to convert q2 into a dictionary keyed on ‘id’, then the second to merge the rows in q1 with the joined elements from the dictionary.

That change does indeed work in both places, but that still begs the question of why the script console didn’t catch the error?

Also I guess I don’t fully understand how the comprehension syntax works so I think I will go and read up on that.