CODEWITHSUNDEEP
pythonsql-serverjson
Tauseef Hussain
Tauseef Hussain

Reputation: 1079

Python: Use JSON list as parameters in SQL

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]))  )

The above code allows me to add a list as a parameter for the query. However my requirement is to add multiple parameters. In the above code _studios is the list that contains the parameters.

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

I have another list like: _vodest = ["VOD","EST"] I want to add this as a parameter for p.VODEST in the query.

I could have easily done this by using a % but it has to be noted that the list contains double quotes("WARNER") but the SQL query requires single quote('WARNER')

Upvotes: 0

Views: 201

Answers (1)

unutbu
unutbu

Reputation: 880299

Always use parametrized sql when possible:

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)

Upvotes: 2

Related Questions