# Using variables in a SQL WHERE IN() operator (scripting)

**URL:** <https://forum.inductiveautomation.com/t/using-variables-in-a-sql-where-in-operator-scripting/27130>\
**Category:** Ignition\
**Created:** [August 2, 2019, 10:43pm UTC](https://forum.inductiveautomation.com/t/using-variables-in-a-sql-where-in-operator-scripting/27130 "2019-08-02T22:43:16Z")\
**Posts on this page:** 1\
**Showing post:** 9

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [August 5, 2019, 8:09pm UTC](https://forum.inductiveautomation.com/t/using-variables-in-a-sql-where-in-operator-scripting/27130/9 "2019-08-05T20:09:17Z")

</div>

Making some assumptions on how you need to retrieve your possible model values:

```python
data = event.source.parent.getComponent(‘Selected CN Table’).data
parts = []
models = []
for i in range(data.getRowCount()):
	parts.append(data.getValueAt(i,'CN'))
	models.append(data.getValueAt(i,'MODEL'))

part_values = ", ".join("?" * len(parts))
model_values = ", ".join("?" * len(models))

query = """
SELECT 
	POI_HEADER.SERIAL_NUMBER, 
	POI_HEADER.MATERIAL_DESCRIPT, 
	POI_OPTION.PART 
FROM 
	POI_HEADER 
INNER JOIN 
	POI_OPTION 
		ON POI_HEADER.ORDER_NUMBER = POI_OPTION.ORDER_NUMBER 
WHERE POI_OPTION.PART IN (%s)
AND POI_HEADER.MODEL IN (%s)""" % (part_values, model_values)

result = system.db.runPrepQuery(query, parts + models, ‘NX_POI’)
event.source.parent.getComponent(‘Machine Selection Power Table’).data = result

```

The key part is swapping the string substitution to a tuple. Technically, you don’t need to make the separate `part_values` and `model_values` variables - again, I just find it more readable past a certain point to move things out to a new line. See [‘basic formatting’](https://pyformat.info/#simple) for more context.

Another option is to use named placeholders and a dictionary to hold the replacement values. This is, again, a choice to make based on readability; in my opinion it’s basically a wash with only two, but could be a _lot_ more usable if you had a lot more strings to replace:

```python
"""
WHERE POI_OPTION.PART IN (%(parts)s)
AND POI_HEADER.MODEL IN (%(models)s)""" % {"parts": part_values, "models": model_values}

```

---

_[View the full topic](https://forum.inductiveautomation.com/t/using-variables-in-a-sql-where-in-operator-scripting/27130)._