Python: Use JSON list as parameters in SQL

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

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

bobby-tables.com

bruno desthuilliers
– bruno desthuilliers

2015-07-08 12:13:18 +00:00
Commented Jul 8, 2015 at 12:13 — bruno desthuilliers
– bruno desthuilliers, Commented Jul 8, 2015 at 12:13

unutbu · Accepted Answer · 2015-07-08 12:12:38Z

2

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)

answered Jul 8, 2015 at 12:12

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tauseef Hussain Over a year ago

This is a much more cleaner and an efficient way. Thank you :)

Collectives™ on Stack Overflow

Python: Use JSON list as parameters in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related