update set values with dictionary with multiple values per key-CodePudding

I have a sqldb table looking like this below

| tag | unit | description|
|:---- |:------:| -----:|
| 1a | %    | mass flow |
| 2a | *    | head flow |

and I want to update values into the table with a dictionary below

 {'1a': ['%', 'head flow'], '2a': ['%', 'head flow'], '3a': ['%', 'mass flow']}

I want to update values using for loop like this

for tag in tag_list:
          key=tag
          unit_value=(dictionary[tag][0] if tag in source_dictionary else None)
          desc_value=(dictionary[tag][1] if tag in source_dictionary else None)
          cursor.execute("UPDATE tag_metadata SET unit= ? , description = ? where tag_name= ?",unit_value,desc_value,key)
      cursor.commit()

I just wonder if it would work.. if not, how can I make it work?

thank you

CodePudding user response：

Its possible. Just slight modification to your dictionary. Let me know if you have issues

dictionary

d={'1a': "%, head flow", '2a': "%, head flow", '3a': "%, mass flow"}

#Create ma expression

m_expr1 = create_map([lit(x) for x in chain(*d.items())])

Map Values and split string into list

df.withColumn('tag1', split(m_expr1[lower(df['tag'])],'\,')).show()

CodePudding user response：

You could do this:

for tag in d:
    cursor.execute("UPDATE tag_metadata SET unit = ? , description = ? where tag_name = ?", 
                   d[tag][0], d[tag][1], tag)
cursor.commit()

This would update all the records mentioned in d with the values as you described.

Or, if you have to deal with more values, a variant of this:

for tag in d:
    cursor.execute("UPDATE tag_metadata SET unit = ? , description = ? where tag_name = ?", 
                   *(d[tag] [tag]))

This adds tag as the last value to the list of values that's already in d for that tag, and then uses the unpacking operator to spread them in the call to .execute().

However, in the latter case, you have to be sure the order of fields in the dictionary matches the order in which they appear in the query, whereas in the former case, you have control over this with the indices.

In either case, you can use d as you defined it.