I am trying to pass a few arguments as variable to a SQL script but I am having issues returning an output.
Given below is my code:
start_date = '2020-03-01'
end_date = '2020-03-02'
I pass these into the below query
cursor.execute('select bill_number from table
where created_at between {} and {}'.format(start_date, end_date))
The above returns no output but I know data exists for this SQL script
After executing a query you need to fetch the results:
records = cursor.fetchall()
It is very important that you don't use format for SQL queries as it's susceptible to SQL injection attacks; instead use:
query = "select bill_number from table where created_at between %s and %s"
cursor.execute(query, (start_date, end_date))
records = cursor.fetchall()
If you want to add filters you just need to adjust the query and add the parameter:
query = "select bill_number from table where created_at between %s and %s and product=%s"
cursor.execute(query, (start_date, end_date, product))
In order to use a list as parameter you can use IN and a tuple:
>>> query = "select * from clients where added between %s and %s and score in %s"
>>> data = ('2019-01-01', '2020-03-01', tuple([1,2,3]))
>>> cursor.execute(query, data)
>>> rows = cursor.fetchall()
>>> len(rows)
32
>>>
Make sure you read the docs as they contain a lot of valuable information.
product = 'prod_a' how could I edit the above query sql equivalent : "select bill_number from table where created_at between %s and %s and product = 'prod_a" I tried passing product = (%s) and passed that argument but that doesn't work
and product IN %s in the SQL query and (start_date, end_date, tuple(products)) for the arguments or use a for loop.