Python使用字典替换字符串中的变量

Question

也有类似的问题，但不完全是我的用例。我想使用字典引用来替换字符串内的变量。在本例中，字符串是一个sql语句，但不太相关。

SQL与变量{}-

qry = """SELECT
SUM(CASE WHEN {{cols_1}} is null AND {{cols_2}} > 0 THEN 1 ELSE 0 END) AS output
FROM mytable"""

字典——

dict = {'cols_1': 'id', 'cols_2': 'weather'}

最后会变成这样-

qry = """SELECT
SUM(CASE WHEN id is null AND weather > 0 THEN 1 ELSE 0 END) AS output
FROM mytable"""

我想用字典值替换cols_1和cols_2。但我不知道该怎么做?

def substitute_values(qry, dict):
if dict:
qry = qry.replace('{{cols_1}}','{{cols_2}}'),dict[]

return qry

转了一圈之后，谢谢你的指导。

Answer 1

使用模板。容易:

from string import Template
qry = """SELECT SUM(CASE WHEN $cols_1 is null AND $cols_2 > 0 THEN 1 ELSE 0 END) AS output FROM mytable"""
dict = {'cols_1': 'id', 'cols_2': 'weather'}
qry = Template(qry).safe_substitute(dict)

文档:https://docs.python.org/3/library/string.html template-strings

Answer 2

这是你要找的吗?

qry = """SELECT
SUM(CASE WHEN {{cols_1}} is null AND {{cols_2}} > 0 THEN 1 ELSE 0 END) AS output
FROM mytable"""
d = {'cols_1': 'id', 'cols_2': 'weather'}

for k,v in d.items():
qry = qry.replace('{{'+k+'}}', v)

print(qry)

SELECT
SUM(CASE WHEN id is null AND weather > 0 THEN 1 ELSE 0 END) AS output
FROM mytable

Answer 3

str.format可以使用两次:

print(qry.format().format(**d))

输出:

SELECT
SUM(CASE WHEN id is null AND weather > 0 THEN 1 ELSE 0 END) AS output
FROM mytable