Python：在SQL中使用JSON列表作为参数

Question

res_vod = db.execute(""" 
SELECT CAST ((SUM(r.SalesVolume)/1000.0) AS decimal(6,1))
FROM RawData r
INNER JOIN Product p 
ON r.ProductId = p.ProductId 
INNER JOIN Calendar c 
ON r.DayId = c.DayId
WHERE c.WeekCodeInYear BETWEEN 30 AND 50
AND p.VODEST IN ('VOD')
AND p.Distributor IN ({})
GROUP BY c.WeekCodeInYear 
ORDER BY c.WeekCodeInYear""".format(', '.join(["'" + studio + "'" for studio in _studios]))  )

上面的代码允许我添加一个列表作为查询的参数。但是，我的要求是添加多个参数。 在上面的代码中_studios是包含参数的列表。你知道吗

_studios = ["WARNER","TF1","GAUMONT","PATHE","STUDIOCANAL","FRANCETV","M6SND"]

我还有另外一张单子： _vodest = ["VOD","EST"] 我想在查询中添加这个作为p.VODEST的参数。你知道吗

我可以通过使用%轻松地完成这一点，但是必须注意的是，列表包含双引号("WARNER")，但是SQL查询需要单引号('WARNER')

Answer 1

Always use parametrized sql如果可能：

def placemarks(n):
    return ','.join(['%s']*n)

sql = """SELECT CAST ((SUM(r.SalesVolume)/1000.0) AS decimal(6,1))
         FROM RawData r
         INNER JOIN Product p 
         ON r.ProductId = p.ProductId 
         INNER JOIN Calendar c 
         ON r.DayId = c.DayId
         WHERE c.WeekCodeInYear BETWEEN 30 AND 50
         AND p.VODEST IN ({})
         AND p.Distributor IN ({})
         GROUP BY c.WeekCodeInYear 
         ORDER BY c.WeekCodeInYear""".format(
             placemarks(len(_vodest)), 
             placemarks(len(_studios)))
args = [_vodest + _studios]
res_vod = db.execute(sql, args)