# Best Method for Searching Database with Power Table

**URL:** <https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321>\
**Category:** Ignition\
**Created:** [February 11, 2021, 6:57pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321 "2021-02-11T18:57:53Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 11, 2021, 6:57pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/1 "2021-02-11T18:57:53Z")

</div>

I have a power table that I am using to display the results of a query, but I want the query to have a dynamic where clause. I was using a few toggle buttons to determine if a dropdown’s value is used in the where clause or not. It works for simple searches, but I have 5 search parameters and the where clause expression is getting hairy fast. I thought about hard coding the where clauses for a case switch, but for 5 variables that would be 32 possible combinations for the where clause and I may add more. What is the best method to accomplish this? I also tried using table filters but it is super laggy.

---

<div class="post-metadata">

**Author:** ![Tim](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim/32/3361_2.png) [@Tim](https://forum.inductiveautomation.com/u/Tim)\
**Post date:** [February 11, 2021, 7:19pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/2 "2021-02-11T19:19:08Z")

</div>

Possibly the Row Selector component could help here. But I think you can do 5 dropdowns as well. Not sure I follow you completely. Do you need to do something like this to allow cases where one dropdown filter is not used?

```auto
WHERE (Column1 = '{Root Container.Column1 Dropdown.selectedLabel}'
OR '{Root Container.Column1 Dropdown.selectedLabel}' = 'ALL')

AND (Column2 = '{Root Container.Column2.selectedLabel}'
OR '{Root Container.Column2.selectedLabel}' = 'ALL')

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 11, 2021, 7:40pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/3 "2021-02-11T19:40:42Z")

</div>

I was using this expression to use in a `runPrepQuery`, but it is too confusing and doesn’t catch every case

```auto
concat("where ", 
		if({Root Container.serialToggle.selected} || {Root Container.ackToggle.selected}, '', concat({Root Container.Start Date.time}, " between ",
		concat("'",{Root Container.Start Date.date},"' and '",{Root Container.End Date.date},"'"))),
		if({Root Container.userToggle.selected},concat(" and ",{Root Container.User.type}, ' = ', "'",{Root Container.User.selectedStringValue},"'"),''),
		if({Root Container.styleToggle.selected},concat(" and style = '",{Root Container.style.text},"'"),''),
		if({Root Container.serialToggle.selected},concat(" and serial = ",toStr({Root Container.serial.intValue})),''),
		if({Root Container.ackToggle.selected},concat(" and ackno = ",toStr({Root Container.ackno.intValue})),''),
		if({Root Container.fabricToggle.selected},concat(" and fabric = '",{Root Container.fabric.text},"'"),''))

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 11, 2021, 7:45pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/4 "2021-02-11T19:45:44Z")

</div>

This is the kind of thing that ought to **not** be a binding. I would run a prepQuery in an asynchronous thread, constructing the query in the script with the chosen criteria (and using ? parameters for the data). Even better would be to have the background thread use system.util.sendRequest to have the query constructed and run from the gateway, allowing the client to not need legacy database access.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 11, 2021, 8:51pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/5 "2021-02-11T20:51:29Z")

</div>

Grade time, how bad did I do?

```auto
def dynamicQuery(table, colList, paramList, timeCol, timeWindow, selectAll):
	"""
		table = name of table to query
		colList = list of columns to filter by
		paramList = list of parameters to match with colList
		timeCol = name of timestamp column
		timeWindow = list of datetime str for start and end
		selectAll = True selects all columns in table, false selects only columns included in colList
	"""
	# Create selection 
	if selectAll:
		columns = '*'
	else:
		columns = ','.join(colList)
	
	# create where clause
	clause = ''
	tupList = zip(colList, paramList)
	count = len(tupList) - 1
	for i, tuple in enumerate(tupList):
		clause += '%s = %s' % (tuple[0],tuple[1]) 
		if i < count:
			clause += ' and '
			
	# Add Time contraint
	if timeCol:
		clause += ' and %s between %s and %s' % (timeCol, timeWindow[0], timeWindow[1])
	
	# Construct query 
	query = "SELECT %s FROM %s WHERE %s" % (columns, table, clause)

```

---

<div class="post-metadata">

**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\
**Post date:** [February 11, 2021, 9:28pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/6 "2021-02-11T21:28:39Z")

</div>

Using `tuple` as a variable name is bad practice. You’re overriding the built-in `tuple` type. Alarm bells should go off if your variables get syntax highlighted

Consider comparing the lengths of colList and paramList to avoid unexpected behavior

Then I think making things more terse improves readability in this case

```auto
	# Create selection 
	if selectAll:
		columns = '*'
	else:
		columns = ','.join(colList)

```

could be written as

```auto
columns = '*' if selectAll else ','.join(colList)

```

and this monster

```auto
	clause = ''
	tupList = zip(colList, paramList)
	count = len(tupList) - 1
	for i, tuple in enumerate(tupList):
		clause += '%s = %s' % (tuple[0],tuple[1]) 
		if i < count:
			clause += ' and '

```

could be written as

```auto
clause = ' and '.join([k + ' = ' + v for (k, v) in zip(colList, paramList)

```

Upon first read I thought it was awkward you used `join` earlier then decided not to for the `' and '`. Then I noticed you were using a count with `enumerate` when the whole point of `enumerate` is that it keeps track of the count for you

Condensing these into one line helps them read like language and then you don’t have to keep track of so many moving pieces. Especially for stuff that just gets thrown away like `count`. Which is ambiguous, since “count” could be interpreted as iteration rather than length

and obviously if there’s human input you’re open to SQL injection and you’d want to redo this to use `runPrepQuery`

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [February 11, 2021, 9:37pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/7 "2021-02-11T21:37:32Z")

</div>

> [@chasondeshotel](#):
>
> Condensing these into one line helps them read like language and then you don’t have to keep track of so many moving pieces. Especially for stuff that just gets thrown away like `count` . Which is ambiguous, since “count” could be interpreted as iteration rather than length

Not to mention that taking out the additional evaluation and assignment per loop will make the code more efficient.

> [@chasondeshotel](#):
>
> obviously if there’s human input you’re open to SQL injection and you’d want to redo this to use `runPrepQuery`

In light of this I think even the zip can be removed:

```auto
clause = ' and '.join([k + ' = ?' for k in colList])

```

---

<div class="post-metadata">

**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\
**Post date:** [February 11, 2021, 9:41pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/8 "2021-02-11T21:41:04Z")

</div>

```auto
clause = ' and '.join([col + ' = ?' for col in colList])

```

Good call. That’s definitely the way to go. Then `system.db.runPrepQuery(query, [paramList])` and you’ve made your code more readable and protected against SQL injection

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 11, 2021, 9:51pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/9 "2021-02-11T21:51:58Z")

</div>

That is pretty slick. Thanks for the suggestions. Note I had to remove the brackets around paramList because it is already a list

```auto
def dynamicQuery(db, table, colList, paramList, timeCol, timeWindow, selectAll):
	"""
		db = name of database schema
		table = name of table to query
		colList = list of columns to filter by
		paramList = list of parameters to match with colList
		timeCol = name of timestamp column
		timeWindow = list of datetime str for start and end
		selectAll = True selects all columns in table, false selects only columns included in colList
	"""
	# Create selection 
	if selectAll:
		columns = '*'
	else:
		columns = ','.join(colList)
	
	# create where clause
	clause = ' and '.join([k + ' = ?' for k in colList])
			
	# Add Time contraint
	if timeCol:
		clause += ' and %s between %s and %s' % (timeCol, timeWindow[0], timeWindow[1])
	
	# Construct query 
	query = "SELECT %s FROM %s WHERE %s" % (columns, table, clause)

	return system.db.runPrepQuery(query,paramList,database = db)

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 11, 2021, 10:01pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/10 "2021-02-11T22:01:50Z")

</div>

You need ‘?’ parameters for your time window, too.

Finally, I recommend you use your DB flavor’s identifier quoting for column names. (Double quotes are the standard, which most DBs can be made to accept.)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [February 11, 2021, 10:03pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/11 "2021-02-11T22:03:44Z")

</div>

So, you can take it a bit further if you really want to:

```auto
def dynamicQuery(db, table, colList, paramList, timeCol, timeWindow, selectAll):
	"""
		db = name of database schema
		table = name of table to query
		colList = list of columns to filter by
		paramList = list of parameters to match with colList
		timeCol = name of timestamp column
		timeWindow = list of datetime str for start and end
		selectAll = True selects all columns in table, false selects only columns included in colList
	"""
	# Create selection 
	columns = '*' if selectAll else ','.join(colList)

	# create where clause
	clause = ' and '.join([k + ' = ?' for k in colList])
			
	# Add Time contraint
	if timeCol:
		clause += ' and %s between ? and ?' % (timeCol)
	        paramList.extend(timeWindow)

	# Construct query 
	query = "SELECT %s FROM %s WHERE %s" % (columns, table, clause)

	return system.db.runPrepQuery(query,paramList,database = db)
```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 11, 2021, 10:08pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/12 "2021-02-11T22:08:39Z")

</div>

Appreciate the pointers!

Also, I don’t know why, but this terrifies me 🤣  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/8/a8aa63ea2e4e3c7f50600baa561d28ed18601892.png)

---

<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:** [February 11, 2021, 11:05pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/13 "2021-02-11T23:05:22Z")

</div>

Man, _someone_ should really write a module that exposes [`DBQuery`](http://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.0/com/inductiveautomation/ignition/gateway/datasource/query/DBQuery.html) to scripting… 🙂

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 11, 2021, 11:36pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/14 "2021-02-11T23:36:26Z")

</div>

Meh. A solution looking for a problem. I think that sort of SQL code generation should be restricted to situations where multiple DB flavors must be supported. Or for rather complicated cases. Otherwise the cure is worse than the disease.

---

<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:** [February 11, 2021, 11:45pm UTC](https://forum.inductiveautomation.com/t/best-method-for-searching-database-with-power-table/43321/15 "2021-02-11T23:45:39Z")

</div>

> [@pturmel](#):
>
> I think that sort of SQL code generation should be restricted to situations where multiple DB flavors must be supported

Yeah, that's fair. I think it'd be great to expose it in some way, for stuff like Exchange resources (could drastically simplify Sales Engineering's Ad Hoc Trend template for Vision, for instance), but it's tricky to "expose" things like that without overwhelming new users.