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.
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?
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')
I was using this expression to use in a runPrepQuery, but it is too confusing and doesn’t catch every case
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},"'"),''))
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.
1 Like
Grade time, how bad did I do?
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)
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
# Create selection
if selectAll:
columns = '*'
else:
columns = ','.join(colList)
could be written as
columns = '*' if selectAll else ','.join(colList)
and this monster
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
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
3 Likes
Not to mention that taking out the additional evaluation and assignment per loop will make the code more efficient.
In light of this I think even the zip can be removed:
clause = ' and '.join([k + ' = ?' for k in colList])
2 Likes
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
That is pretty slick. Thanks for the suggestions. Note I had to remove the brackets around paramList because it is already a list
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)
1 Like
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.)
3 Likes
So, you can take it a bit further if you really want to:
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)Appreciate the pointers!
Also, I don’t know why, but this terrifies me 
4 Likes
Man, someone should really write a module that exposes DBQuery to scripting… 
5 Likes
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.
1 Like
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.