how to pass multiple cli arguments to retrive months in SQL

Question

I have a sample table of monthly wise net_sales data

Region          Area            HQ         Month          Sales
 AAA            xyz             Ax          03            10000
 BBB            klm             Bk          03            20000
 AAA            xyz             Ax          04            23000
 BBB            klm             Bk          04            70000 
 BBB            klm             Bk          05            78000

I need only particular month data, for that I did ( executing in python )

month_value = '';
if sys.argv[1:]:
      month_value = sys.argv[1]

query = """select * from sales_data where Month = '%s'""" %(month_value)

So I'm passing the month value as a cli argument. Example: python file_name.py 03

I get

Region          Area            HQ         Month          Sales
 AAA            xyz             Ax          03            10000
 BBB            klm             Bk          03            20000

Is there any way to pass mulitple arguments for different month values to get more than one month data? ( should work for one month and more than one month )

Ex: python file_name.py 03 04

I need to get

Region          Area            HQ         Month          Sales
 AAA            xyz             Ax          03            10000
 BBB            klm             Bk          03            20000
 AAA            xyz             Ax          04            23000
 BBB            klm             Bk          04            70000

Can you please help me with this?

thanks in advance!

Giovanni · Answer 1 · 2020-09-19T19:22:19.847

0

If I get what you're asking for I think the following code can solve you problem:

month_value = sys.argv[1:]
query = "select * from sales_data where Month IN("

for month in month_value:
  query += "'%s'," %month
query = query[:-1] + ")"

I'm assuming that you'll have a list containing all the numbers of the months you want to analyze (change the values in the list as you prefer also with strings).
The last line of code query = query[:-1] + ")" it's removing the last unnecessary comma and add the closing bracket.

edited Sep 19 '20 at 19:22

answered Sep 19 '20 at 19:08

Giovanni

212
2
12

can you tell me how to pass the month_value as cli agruments? @Giovanni – astroboy Sep 19 '20 at 19:14
Hi, actually you're very near to do it by yourself in fact with `sys.argv[1:]`, in fact it returns a list of all the parameters (excluding the program name `sys.argv[0]`). So you can just assign it to the `month_value` variable: `month_value = sys.argv[1:]`. I'm also supposing that your only cli arguments are the months, if it wasn't clear. – Giovanni Sep 19 '20 at 19:20

score 0 · Answer 2 · answered Sep 20 '20 at 02:54

First, you are using the older, de-emphasized (not deprecated yet) way of string formatting. The current way is with str.format (Python 2.6+) or the newer F-string (Python 3.6+).

# NOT RECOMMENDED
query = """SELECT * FROM sales_data WHERE `Month` = '%s'""" %(month_value)

# str.format
query = """SELECT * FROM sales_data WHERE `Month` = {}""".format(month_value)

# f-string
query = f"""SELECT * FROM sales_data WHERE `Month` = {month_value}"""

Second, for SQL queries especially with command line arguments from user, do NOT string format queries but use parameterization where you create a prepared statment with placeholder(s) and then bind parameter values in a subsequent cursor.execute call. Do not confuse the unquoted %s used in parameterization with the quoted '%s' symbol used in the outmoded string modulo method which you use.

query = """select * from sales_data where Month = %s"""

cursor.execute(query, [month_value])

Therefore, to retrieve list of month values, dynamically build prepared statement with placeholders then pass list into execute call. Do not confuse formatting below with my caution above. Below formats the prepared statement for dynamic number of placeholders and in next step binds values.

Script

month_vals = sys.argv[1:]

prms = ", ".join(["%s" for _ in month_vals])
query = """SELECT * FROM sales_data WHERE `Month` IN ({})""".format(prms)

cursor.execute(query, month_vals)

CLI

$ python file_name.py 03 06 09

how to pass multiple cli arguments to retrive months in SQL

2 Answers2