Passing list of strings as parameter to SQL query in Python-CodePudding

I have a Python program that generates a report for returns. When run, a GUI pops up allowing the user to select categories from a list. I am trying to format my query so that the generated report only includes categories in the list of selected categories. The formatting for the start and end dates works so I'm not sure what I'm doing wrong for the category formatting.

The code for my GUI

options = ['Bags Children','Bags Mens','Bandanas & Handkerchiefs','Belts Children','Belts Mens','Belts Womens','Cold Weather Childrens','Cold Weather Mens','Cold Weather Womens','Face Masks','Handbags Womens','Headwear Childrens',\
'Headwear Mens','Headwear Womens','Jewelry Mens','Scarves & Wraps Womens','Sleepwear Childrens','Sleepwear Mens','Sleepwear Womens','Slippers Childrens','Slippers Mens','Slippers Womens','Socks & Hosiery Childrens',\
'Socks & Hosiery Mens','Socks & Hosiery Womens','Sunglasses & Cases','Suspenders Childrens','Suspenders Mens','Suspenders Womens','Travel Accessories','Umbrellas & Rain Gear','Umbrellas & Rain Gear Childrens','Umbrellas & Rain Gear Mens',\
'Umbrellas & Rain Gear Womens','Undergarments Childrens','Undergarments Mens','Undergarments Womens','Waist Packs & Belt Bags','Wallets & Small Accessories Childrens','Wallets & Small Accessories Mens','Wallets & Small Accessories Womens',\
'Womens Wallets & Handbag Accessories']
text = "Select Category(ies): "
title = 'Returns Summary'
cat_output = multchoicebox(text, title, options)
title = 'Message Box'
message = "Selected Categories: "   str(cat_output)
msg = msgbox(message, title)
print(cat_output)

The output for print(cat_output) is in the format ['Face Masks', 'Belts Mens', 'Belts Womens'] displaying the selected categories.

GUI Display

The code for my SQL query

SQL1 = "SELECT i.Category, i.ItemName, r.OrderNumber, r.SKUReceived, r.UnitPrice, r.Quantity, o.CartID, o.MarketName, o.Email \
    FROM Returns AS r INNER JOIN Orders AS o ON r.OrderNumber = o.OrderNumber INNER JOIN Inventory AS i ON i.LocalSKU = r.SKU \
    WHERE (((r.Date) between '%s' and '%s') AND ((r.UnitPrice)>0) AND o.CartID != 12 AND r.Type = 'R' AND i.Category IN ({})) \
    ORDER BY r.OrderNumber;".format(cat_output) % (start, end)

The Error I get

Traceback               
    <module>    Z:\Python\Returns Summary 3.0.py    381     
    GetReturns  Z:\Python\Returns Summary 3.0.py    279     
ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name ''Bags Children', 'Belts Womens', 'Headwear Mens', 'Sleepwear Childrens''. (207) (SQLExecDirectW)")

CodePudding user response：

SQL syntax suggests IN clause like … IN (‘m’,’l’). From what I see in the question, you have it like …IN ([‘m’,’l’]). Try to use .format(“,”.join(repr(x) for x in cat_output))