# Incomplete dictionary interacting with queries **URL:** \ **Category:** Ignition\ **Tags:** python, ignition81\ **Created:** [July 31, 2024, 10:24am UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961 "2024-07-31T10:24:37Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![danielps1818](https://avatars.discourse-cdn.com/v4/letter/d/ecae2f/32.png) [@danielps1818](https://forum.inductiveautomation.com/u/danielps1818)\ **Post date:** [July 31, 2024, 10:24am UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/1 "2024-07-31T10:24:37Z")
Hi! I need a bit of help of how to do this. I have a named query: ```python system.db.runNamedQuery( "FUNCTIONS/UPDATE", { "ID": id, "PARAM_1": value1, "PARAM_2": value2, [...] "PARAM_N": valuen } ) ``` ```python UPDATE MY_TABLE PARAM_1 = COALESCE(:PARAM_1, PARAM_1), PARAM_2 = COALESCE(:PARAM_2, PARAM_2), [...] PARAM_N = COALESCE(:PARAM_N, PARAM_N), WHERE ID = :ID ``` The function update the table depending of the ID and **the values that receives that are not null**. My problems is that I recive the values in a dictionary, how may or may not contains the values to update. For example, this two are possible values that I could receive: ```python dict = { "PARAM_1": value1, "PARAM_3": value3, "PARAM_8": value8 } dict = { "PARAM_3": value3, "PARAM_4": value4, "PARAM_5": value5, "PARAM_8": value8 } ``` My first idea was to do this: ```python system.db.runNamedQuery( "FUNCTIONS/UPDATE", { "ID": id, "PARAM_1": dict[PARAM_1], "PARAM_2": dict[PARAM_2], [...] "PARAM_N": dict[PARAM_N] } ) ``` But if the dictionary doesn't contain the param it throws an exception. Also I thought of making a loop where I check if it exist and adding a null value if not, but I don't really like, is an ugly way of doing it (in my opinion). Any idea of how I could do this in a more elegant way? Any restructuring of the code, query or the idea itself are valid. The only thing I can't change is how I recive the data. Thanks in advance! ---
**Author:** ![Samuel\_Sueur](https://avatars.discourse-cdn.com/v4/letter/s/e36b37/32.png) [@Samuel\_Sueur](https://forum.inductiveautomation.com/u/Samuel_Sueur)\ **Post date:** [July 31, 2024, 10:34am UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/2 "2024-07-31T10:34:31Z")
Hi, Not an expert on this, but a namedQuery does not seem suitable for what you are trying to achieve. Considering your goal, I would use [system.db.runPrepUpdate | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/8.1/appendix/scripting-functions/system-db/system-db-runPrepUpdate) This way, you are able to build the SQL query using a for loop and your query will be dynamic. (no need to check for null values, just add "SET col = ? " to the query in the loop based on the number of elements in your dictionary). Beware of potential SQL Injections though, as you will be able to specify prepared arguments using "?" for the values, but you won't be able to use prepared arguments for the column names. ---
**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:** [July 31, 2024, 11:00am UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/3 "2024-07-31T11:00:42Z")
> [@Samuel\_Sueur](#): > > Not an expert on this, but a namedQuery does not seem suitable for what you are trying to achieve. Not just unsuitable. It can not be done safely. It would require the use of a query string parameter which defeats the main purpose of named queries. @danielps1818 The only safe way to accomplish this is through a script using `system.db.runPrepUpdate()`. With this there is no chance for SQL injection as the query structure is being provided to the driver by the code and any '?' placeholders will be properly sanitized. The only tricky part is insuring you maintain the order of the parameters as they are handed to you as you are generating the query and parameter list. If you need help doing that in an “elegant” way just ask. I’m away from my desk at the moment so can’t really post a script easily. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [July 31, 2024, 11:42am UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/4 "2024-07-31T11:42:27Z")
I assume you're getting a key error here ```python system.db.runNamedQuery( "FUNCTIONS/UPDATE", { "ID": id, "PARAM_1": dict[PARAM_1], "PARAM_2": dict[PARAM_2], [...] "PARAM_N": dict[PARAM_N] } ) ``` when the PARAM\_N does not exist correct? You could use `.get` which provides a default value if the key does not exist ie `dict.get(PARAM_N, None)` as None translates into a NULL in the db. Though at this point then are just updating the fields to their current value anyways in the db and so it's possible if you only wanted to update a single column, you would still get the full UPDATE statement for every column - this makes debugging confusing/you could potentially have to sift through a lot of useless SQL/columns. You could do something like ```python def createPrepStatement(table, payload, idColumnName, idx): """ Args: table: database table name payload: dictionary - column name in keys, values to update to in values idColumnName: name of the id column id: anyType, probably int, provdied to Id column Returns: query: str: the query string with ? interpolated args: [any], arguments to provided to the prepUpdate statement """ return "UPDATE %s SET %s WHERE %s=?"%( table, ', '.join(['%s=?'%(key) for key in payload.keys()]), idColumnName ), list(payload.values())+[idx] query, args = createPrepStatement('table', {'column1':1, 'columnStr':"hello"}, 'id', 1) system.db.runPrepQuery(query, args) ``` Dynamically make the update statement to only update what is needed. Now if you needed to debug your SQL Statements would not have any no-ops. ---
**Author:** ![danielps1818](https://avatars.discourse-cdn.com/v4/letter/d/ecae2f/32.png) [@danielps1818](https://forum.inductiveautomation.com/u/danielps1818)\ **Post date:** [July 31, 2024, 11:58am UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/5 "2024-07-31T11:58:24Z")
Amazing the `dict.get(value, default_value)` Didn't knew it existed. I'm going to try this: ```python system.db.runNamedQuery( "FUNCTIONS/UPDATE", { "ID": id, "PARAM_1": dict.get('PARAM_1', None), "PARAM_2": dict.get('PARAM_2', None), [...] "PARAM_N": dict.get('PARAM_N', None) } ) ``` Thanks a lot! ---
**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:** [July 31, 2024, 12:06pm UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/6 "2024-07-31T12:06:21Z")
Why the insistence on a Named Query? ---
**Author:** ![danielps1818](https://avatars.discourse-cdn.com/v4/letter/d/ecae2f/32.png) [@danielps1818](https://forum.inductiveautomation.com/u/danielps1818)\ **Post date:** [July 31, 2024, 1:24pm UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/7 "2024-07-31T13:24:56Z")
Because a `system.db.runPrepUpdate()` in my case would be something like this: ```python system.db.runPrepUpdate( "UPDATE MY_TABLE SET (?,?,?,?,?,?,[...],?) WHERE ID = (?)", [dict[PARAM_1], dict[PARAM_2], dict[PARAM_3], dict[PARAM_4], [...], dict[PARAM_N], ID], ) ``` and the problem of not having the key `PARAM_X` in the dict still occurs. _Also I don't know if the WHERE clause works correctly_. In my opinion, named queries are more "elegant" than writting the query in code and gives a level of abstraction that the `system.db.runPrepUpdate()` don't. Is not that aren't useful, I simply prefer the other way. How would you do this? ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [July 31, 2024, 1:28pm UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/8 "2024-07-31T13:28:43Z")
Did you see the function I wrote? As long as the key value of the dictionary is the column name in the database it will write the query for you for only the columns that exist in your dictionary. I am not against named queries but I am against No-Ops. I would not want to have to debug a query where there is a number of `someColumn='previousValue'` littered throughout each one. It makes things confusing. ---
**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:** [July 31, 2024, 1:28pm UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/9 "2024-07-31T13:28:56Z")
Using the named query **requires** you to assign to every column. That has negative consequences in many concurrent applications, and makes more work for the DB. Build SQL for a prep update that only has the assignments for the assignments actually needed. (Named queries cannot do this.) Place this common task in a project library script. Where you would have use `runNamedQuery()`, call the library function instead. Just as maintainable, at least, IMNSHO. (Project scripts are stored in a git-friendly form in the gateway filesystem.) ---
**Author:** ![danielps1818](https://avatars.discourse-cdn.com/v4/letter/d/ecae2f/32.png) [@danielps1818](https://forum.inductiveautomation.com/u/danielps1818)\ **Post date:** [July 31, 2024, 1:39pm UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/10 "2024-07-31T13:39:48Z")
Okey, okey, okeeyyy. It just clicked. I a bit dense today. I'll try it and I'll let you know. Sorry for the misunderstanding and the patience. I really appreciate your help! I'm going to try it now. Thank you! _PS: Doing an update with coalesce does actually update it in the db? If I do what I wrote with coalesce even with Null values in each param it gets a full update?_ ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [July 31, 2024, 2:16pm UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/11 "2024-07-31T14:16:28Z")
Yes your coalesce statement there is only on the value of what you're setting the column to so you will see SET for each column, whether it's a new value or the existing db value due to a NULL parameter depends on the dictionary you feed in. ---
**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:** [July 31, 2024, 2:16pm UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/12 "2024-07-31T14:16:43Z")
> [@danielps1818](#): > > If I do what I wrote with coalesce even with Null values in each param it gets a full update? Depends on the DB. Some will notice that the value is identical, but all databases that do that will still have to **check** , and that impacts the execution plan. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [July 31, 2024, 2:18pm UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/13 "2024-07-31T14:18:40Z")
In their Ignition logs they would see a `UPDATE table SET column_1=1, ...,column_N=n` for every column as the database would be the one that does this since you mention execution plan, I assume this is not happening on the JBDC level at all. ---
**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:** [July 31, 2024, 3:07pm UTC](https://forum.inductiveautomation.com/t/incomplete-dictionary-interacting-with-queries/93961/14 "2024-07-31T15:07:24Z")
> [@danielps1818](#): > > In my opinion, named queries are more "elegant" than writting the query in code and gives a level of abstraction that the `system.db.runPrepUpdate()` don't. And a drill with a screw is more elegant than a hammer and nail, but if you have a nail you wouldn't really want to use a drill to drive it. I would agree that the abstraction provided by named queries is great, but they are not always the right tool for the job. Personally, I would approach this with a script that looked like: ```python setList, paramList = [','.join(k + '= ?' for k, _ in payload.iteritems()), [v for _,v in payload.iteritems()]] query = 'UPDATE MY_TABLE {} WHERE id = ?'.format(setList) paramList.extend(rowId) system.db.runPrepUpdate(query, paramList) ``` It's essentially the same as what @bkarabinchak.psi already posted, just uses a different string formatting method. His is also more generic allowing you to also change the table.