Python,将case语句插入sql查询
问题描述:
我在PYTHON的SQL查询中多次使用CASE语句时遇到问题。 我想知道是否可以拿走CASE语句并创建一个模板,然后我可以将模板参数放回到原始查询中。就像这样:Python,将case语句插入sql查询
case_stm_template =
crs.execute("""
CASE
WHEN level_1 = 'Corporate'
THEN
CASE
WHEN ssr.rating = 'A' OR ssr.rating LIKE 'A[+-]'
THEN 'Corp A'
WHEN ssr.rating = 'AA' OR ssr.rating LIKE 'AA[+-]'
THEN 'Corp AA'
WHEN ssr.rating = 'BBB' OR ssr.rating LIKE 'BBB[+-]'
THEN 'Corp BBB'
ELSE NULL
END
WHEN level_1 = 'Government' AND level_2 = 'Provincial'
THEN 'Prov'
WHEN level_1 = 'Government' AND level_2 = 'Federal'
THEN 'Canada'
ELSE NULL
END
""")
要把它放到
crs.execute("""
SELECT
...
case_stm_template,
SUM(...)
FROM ....
WHERE ....
GROUP BY
case_stm_template
""".format(...))
这是行不通的。谁能帮我?谢谢!!!
答
什么:
case_stm_template="""
CASE
WHEN level_1 = 'Corporate'
THEN
CASE
WHEN ssr.rating = 'A' OR ssr.rating LIKE 'A[+-]'
THEN 'Corp A'
WHEN ssr.rating = 'AA' OR ssr.rating LIKE 'AA[+-]'
THEN 'Corp AA'
WHEN ssr.rating = 'BBB' OR ssr.rating LIKE 'BBB[+-]'
THEN 'Corp BBB'
ELSE NULL
END
WHEN level_1 = 'Government' AND level_2 = 'Provincial'
THEN 'Prov'
WHEN level_1 = 'Government' AND level_2 = 'Federal'
THEN 'Canada'
ELSE NULL
END
"""
然后
crs.execute("""
SELECT
...
%s,
SUM(...)
FROM ....
WHERE ....
GROUP BY
%s
""" % (case_stm_template, case_stm_template))
或者类似的规定。