python-3.x - 使用字典替换 SQL 查询中的参数

Question

我将 SQL 查询作为字符串：

SELECT DISTINCT a, b AS value FROM ${source} WHERE b IN (${string}) AND slice_start >= ${date} AND slice_start <= ${date1};

我需要使用字典替换参数，它来自 JSON 文件，如下所示：

my_dict = {"source": "table", "string": "abc", "date": "2020-01-01", "date1": "2020-01-02"}

我很困惑如何替换以开头的每个字符串$以及如何"在末尾和每个参数的请求处附加（但不是在源上），因此可以稍后执行 SQL 查询。这就是我现在的位置：

for k, v in target_groups_queries.items():
    if isinstance(v, dict):
        for k1, v1 in v.items():
            queries = v1['sql'].split()
            final_string = ' '.join(str(my_dict.get(word, word)) for word in queries)
            print(final_string)

    else:
        pass

标签： python-3.x

Answer 1

您可以使用字典或关键字解包，如下所示：

my_dict = {"source": "table", "string": "abc", "date": "2020-01-01", 
           "date1": "2020-01-02"}
sql = '''SELECT DISTINCT a, b AS value 
         FROM ${source} 
         WHERE b IN (${string}) 
             AND slice_start >= ${date} 
             AND slice_start <= ${date1};'''                                               

sql.format(**my_dict)                                                                      

结果：

In [32]: from pprint import pprint                                                                  

In [33]: pprint(sql.format(**my_dict))                                                              
('SELECT DISTINCT a, b AS value FROM $table WHERE b IN ($abc) AND slice_start '
 '>= $2020-01-01 AND slice_start <= $2020-01-02;')