python-3.x - 使用字典中的引用替换字符串中的单词

Question

我有 2 个字典，并希望使用另一个字典作为参考来替换 SQL 查询中的值：

params_to_replace = {'tvchview': [{'source': 'table', 'channel': ['abc', 'abcd']}],
                     'tvtot_du': [{'source': 'table1', 'channel': ['ab']}]} 

queries = {'tvchview': 'SELECT DISTINCT id, channel AS value FROM ${source} WHERE channel IN (${channel});',
           'tvtot_du': "SELECT id, SUM(duration) AS value FROM ${source} WHERE channel IN ('${channel}') GROUP BY id;"}

所需的输出是：

{'tvchview': 'SELECT DISTINCT id, channel AS value FROM table WHERE channel IN ('abc', 'abcd');',
 'tvtot_du': "SELECT id, SUM(duration) AS value FROM table1 WHERE channel IN ('ab') GROUP BY id;"}

这是我得到的地方，但不知道如何继续。我不确定是否需要最后两行。我想拆分字符串并替换单词，但不确定这是否是最好的方法。也可能存在引用可能是其他词的情况，不仅是源和通道，基本上字典中的任何内容都应该被替换：

for k, v in queries.items():
    if k in params_to_replace:
        for v1 in params_to_replace.values():
            res = {k: v for d in v1 for k, v in d.items()}
            a = [el.split() for el in list(queries.values())]

标签： python-3.x

Answer 1

尝试这个 -

params_to_replace = {
        'tvchview': [{'source': 'table', 'channel': ['abc', 'abcd']}],
        'tvtot_du': [{'source': 'table1', 'channel': ['ab']}]
    }

queries = {
        'tvchview': 'SELECT DISTINCT id, channel AS value FROM ${source} WHERE channel IN (${channel});', 
        'tvtot_du': "SELECT id, SUM(duration) AS value FROM ${source} WHERE channel IN ('${channel}') GROUP BY id;"
    }

expected_output = {
        'tvchview': "SELECT DISTINCT id, channel AS value FROM table WHERE channel IN ('abc', 'abcd');",
        'tvtot_du': "SELECT id, SUM(duration) AS value FROM table1 WHERE channel IN ('ab') GROUP BY id;"
    }

for k, v in params_to_replace.items():

    # Because the tvchview has => (${channel}) and 
    # The tvtot_du has => ('${channel}') in the queries dictionary
    if k == 'tvtot_du':
        queries[k] = queries[k].replace("${source}", v[0]['source']) \
                    .replace("${channel}", ", ".join(item for item in v[0]['channel']))
    else:
        queries[k] = queries[k].replace("${source}", v[0]['source']) \
                    .replace("${channel}", ", ".join("'" + item + "'" for item in v[0]['channel']))

print(queries == expected_output)