0

I have this Python script that calls a SQL command through the windows command prompt

UN1=str(sys.argv[1])

f_statement1 = """ set nocount on; set ansi_warnings off;

SELECT c.campaign_name,ca.campaign_id,ca.product_id,p.product_name 
FROM [AN_MAIN].[dbo].[campaign_addon] ca 
JOIN product p ON p.product_id = ca.product_id
JOIN campaign c ON c.campaign_id = ca.campaign_id 
WHERE ca.campaign_id IN""" + str(UN1)

Here I'm using the 'IN' function for multiple variables meaning it must be in brackets and comma separated if there's more than one. In the command prompt I have to make my string call the file 'addon.py' and it looks like this:

 addon.py (45123,51241)

So I'm forced to put my variables in brackets to make them one argument as far as python in concerned.

Is there a way to make the python take any number of variables without having to manually put the commas and brackets in?

i.e. addon.py 45123 51241

iBug
  • 35,554
  • 7
  • 89
  • 134
jhallvid
  • 1
  • Just pass as many command line arguments as you like (separated by spaces). Then catch those arguments by `sys.argv[1:]`. It will return you a `list` of those parameters. You can then prepare the `IN` clause by `'('+','.join(sys.argv[1:]) + ')'` – mshsayem Jan 23 '18 at 10:17
  • 1
    Related: https://stackoverflow.com/questions/4819356/sql-in-operator-using-pyodbc-and-sql-server and https://stackoverflow.com/questions/42123335/passing-a-list-of-values-from-python-to-the-in-clause-of-an-sql-query. Be careful how you handle passing args to SQL queries. Data is not code, so use placeholders. Methods like `','.join(args)` combined with string formatting are prone to quotation/escaping issues, and in the worst case [SQL injection](https://en.wikipedia.org/wiki/SQL_injection). – Ilja Everilä Jan 23 '18 at 10:18

2 Answers2

0

Got it by taking what mshsayem suggested with a change (sorry didn't understand your answer at first)

UN1=sys.argv[1:]
UN2=format(",".join(UN1))
f_statement1 = """ set nocount on; set ansi_warnings off; 
SELECT c.campaign_name,ca.campaign_id,ca.product_id,p.product_name 
FROM [AN_MAIN].[dbo].[campaign_addon] ca 
JOIN product p ON p.product_id = ca.product_id 
JOIN campaign c ON c.campaign_id = ca.campaign_id 
WHERE ca.campaign_id IN (""" + UN2 + """)"""
jhallvid
  • 1
-1

Try this:

UN1=sys.argv[1:] # this will provide you the argument list; i.e. ['45123','51241']

f_statement1 = """ set nocount on; set ansi_warnings off;

SELECT c.campaign_name,ca.campaign_id,ca.product_id,p.product_name 
FROM [AN_MAIN].[dbo].[campaign_addon] ca 
JOIN product p ON p.product_id = ca.product_id
JOIN campaign c ON c.campaign_id = ca.campaign_id 
WHERE ca.campaign_id IN ({})""".format(','.join(UN1))
mshsayem
  • 17,557
  • 11
  • 61
  • 69
  • Thanks but I got an error trying this: [4m[1mResults for CID ['436351', '436408']:[0m Msg 207, Level 16, State 1, Server 372788-ANEDWVN2\ANEDWVN2, Line 2 Invalid column name ',',4,3,6,3,5,1,',,, ,',4,3,6,4,0,8,','. When I put in addon.py 436351 436408 – jhallvid Jan 23 '18 at 10:28