Compare single value with a query dataset

[Mithun_Reddy_Basvana]

Hi,
I am running a simple query to return one column of data. I want to compare one value which is inputted into the ignition project by a operator to these column of data to see if it matches . i am getting the query values to print but the if statement is not running .How can i do these?

#define a query
	query = """
	SELECT xx FROM xx
	where xx = (?)
	"""
	#call the query and return the results in a variable called dataset
	dataset = system.db.runPrepQuery(query,['xxxxx'],'yyyy')
	print dataset
	#iterate through the results to print the values at each row,column
	for row in range(dataset.rowCount):
	    print "row: " + str(row)
	    for col in range(dataset.columnCount):
	        print dataset.getValueAt(row,col)      
	if scannedpart == dataset.getValueAt(row,col):
		message = "equal"
	else:
		message = "not equal"

[pturmel]

Please edit your post (with the pencil icon) so it is formatted for readability:

• Remove any white space or punctuation from in front of "I am running", as it triggers a one-line display.

• Highlight all of your pasted code then click the </> button (preformatted text). That will mark the code as code, and it will display with proper indentation and with color coding.

[Mithun_Reddy_Basvana]

Thank you edited

[pturmel]

You are checking for equality after the loops have run and row & col are off the end. You must check within the loop, where you are currently printing the column value. I recommend returning True as soon as you find a match, and returning False if you get all the way through the loops.

You probably should not be using print -- it doesn't behave well in many cases.

[Mithun_Reddy_Basvana]

That helped thank you

[Mithun_Reddy_Basvana]

But, after moving the IF statement into the loop it only performs the equal operation on the last row of the dataset how can i get it to perform on all the rows?

for row in range(dataset.rowCount):
							    	#print "row: " + str(row)
							    	 for col in range(dataset.columnCount):
							        	#print dataset.getValueAt(row,col)
							        	if scannedPart == dataset.getValueAt(row,col):
							        		message = "True"
							        	else:
							        		message = "False"

[pturmel]

Start with your result preset to false. When the comparison is true, set the result to true and break out of the loop. A function return is simplest, but the break statement is an option.

[Transistor]

Is this what you want?

message = "False"
for row in range(dataset.rowCount):
    for col in range(dataset.columnCount):
        if scannedPart == dataset.getValueAt(row,col):
            message = "True"
            break   # Break out of inner loop.
    if message = "True":
        break       # Break out of outer loop.

Python doesn't have a neat way of breaking out of nested loops. You could create a function and break out using a return statement and this might be neater.

[lrose]

No need for nested loops at all, it's a single column.

return any(val for val in dataset.getColumnAsList(dataset.getColumnIndex(0) if val == selectedPart)

This will return true if there is a match and false otherwise, and any() is short circuiting so it will return at the first match.

[Mithun_Reddy_Basvana]

Thank you. I had to change a couple more things I had to move col to the first for loop and row to the second one I don't completely understand why these works do you?

result = "false"
								for col in range(dataset.columnCount):
							    	#print "row: " + str(row)
							    	 
							    	 for row in range(dataset.rowCount):
							        	#print dataset.getValueAt(row,col)
							        	
							        	if scannedPart == dataset.getValueAt(row,col):
							        		result = "true"
							        		message = "equal"
							        		break
							        	else:
							        		message = "Not equal"

[lrose]

It would not matter, in this instance if the outer loop was rows or columns, there must have been another error.

[Mithun_Reddy_Basvana]

I have a problem with the above after running the below code. Seems like after comparing 1 row in the dataset and if it is not equal its performing the "else" statement i only want it to perform the else statement after comparing all the rows in the dataset and only if the overall result is not equal

result = "false"
								for col in range(dataset.columnCount):
							    	#print "row: " + str(row)
							    	 
							    	 for row in range(dataset.rowCount):
							        	#print dataset.getValueAt(row,col)
							        	
							        	if scannedPart == dataset.getValueAt(row,col):
							        		result = "true"
							        		message = "Equal"
							        		break
							        	else:
						
											perform a API call

[lrose]

@Mithun_Reddy_Basvana

Seems like after comparing 1 row in the dataset and if it is not equal its performing the "else" statement

Because that is what it is doing. That is exactly what you have programmed it to do.

You stated in your original post that this is only a single column. From this last post I take that to mean that if there is no match, then you want to do something.

This script is all you need.

if all(val != selectedPart for val in dataset.getColumnAsList(0)):
    #perform API call

Lets talk about whats happening here:

1. dataset.getColumnAsList(0) This function returns the entirety of a column as a list. Since you only have a single column, there is no need to loop through the columnCount, the code will only execute once anyway. The added syntax for a for loop confuses what the script is actually accomplishing.

2. val != selectedPart for val in dataset.getColumnAsList(0) This is a special way of writing a for loop in python. It is roughly equivalent to:

   for val in dataset.getColumnAsList(0):
      val != selectedPart
   

   The general idea here is that for each value in the list we are comparing that to the value in selectedPart. If they are not equal it will be True.

3. all() This function takes an iterator of boolean items if any of them is false (i.e. val == selectedPart) then it will immediately return false.

You can almost read this as:

if all values in column 0 are not equal to selectedPart then perform API call.

As a complete aside, the formatting in your last code blocks while better, still hurts my eyes a bit to look at.

[Mithun_Reddy_Basvana]

Thank you for the detailed info. it seems converting a column into a List is not working I am not able to print a List

print dataset
print dataset.getColumnIndex(value)
print dataset.getColumnName(0)
print dataset.getColumnAsList(0)

below is the output for the above 4 commands. I am not seeing a list

<PyDataset rows:18 cols:1>
0
value

my ignition platform version is 7.9.17 does these have anything to do with these?

[pturmel]

@Mithun_Reddy_Basvana

ignition platform version is 7.9.17

That definitely makes a difference. In old versions, the .getColumnAsList() method was on selected implementations of Dataset. And PyDataset was quite distinct from Dataset.

This has changed over time, such that Dataset implements .getColumnAsList() in the interface now, and PyDataset implements Dataset.

You're kind of [expletived] with that old version.

[Mithun_Reddy_Basvana]

Thank you. Any other way to create a List in these version so the below command works?
if all(val != selectedPart for val in dataset.getColumnAsList(0)):

[lrose]

Not simply, that I’m aware of, but that’s okay. We can achieve the same type of thing like this:

if all(dataset.getValueAt(row,0) != selectedPart for row in range(dataset.getRowCount()):
    #perform API call

Would be better to use xrange() but I don’t remember if you had to import that in 2.3 or not.

[Mithun_Reddy_Basvana]

Thank you that works