Passing a Date variable to a sql query with python-CodePudding

I´m trying to write a sql query in python, where I want the user to pass a date, saved in a variable. Then I want this to be processed in the query. While it´s pretty easy to do so in R, I´m curious if there´s an easy approach in python too. The Date should be passed where 2022-12-12 is.

date = input("this format  YYYY-MM-DD") 
query_new_Limits = """
SELECT #######
  , a.RULE_NAME
  , a.RESULT_VALUE
  , a.SUMMARY_DETAIL
  , a.REF_DATE
FROM ######## a ################### b
ON b.T_PORTFOLIO_ID = a.PORTFOLIO_ID
WHERE a.REF_DATE = TO_DATE('2022-12-12','YYYY-MM-DD')
  AND REGEXP_LIKE(a.PORTFOLIO_CODE,'^[[:digit:]] $')
  AND NOT b.POR_INVESTMENT_TYPE IN #######
  AND b.REF_TO_DATE > TO_DATE('2022-12-12','YYYY-MM-DD')
  AND a.RESULT_STATE_ID > 12
"""

CodePudding user response：

From the docs

SQL and PL/SQL statements that pass data to and from Oracle Database should use placeholders in SQL and PL/SQL statements that mark where data is supplied or returned. These placeholders are referred to as bind variables or bind parameters. A bind variable is a colon-prefixed identifier or numeral. For example, there are two bind variables (dept_id and dept_name) in this SQL statement:
sql = """insert into departments (department_id, department_name)
        values (:dept_id, :dept_name)"""
cursor.execute(sql, [280, "Facility"])
[or named binding]
cursor.execute("""
        insert into departments (department_id, department_name)
        values (:dept_id, :dept_name)""", dept_id=280,
        dept_name="Facility")

Which applied to your example gives:

date = input("this format  YYYY-MM-DD") 
query_new_Limits = """
SELECT #######
  , a.RULE_NAME
  , a.RESULT_VALUE
  , a.SUMMARY_DETAIL
  , a.REF_DATE
FROM ######## a ################### b
ON b.T_PORTFOLIO_ID = a.PORTFOLIO_ID
WHERE a.REF_DATE = TO_DATE(:date,'YYYY-MM-DD')
  AND REGEXP_LIKE(a.PORTFOLIO_CODE,'^[[:digit:]] $')
  AND NOT b.POR_INVESTMENT_TYPE IN #######
  AND b.REF_TO_DATE > TO_DATE(:date,'YYYY-MM-DD')
  AND a.RESULT_STATE_ID > 12
"""
cursor.execute(query_new_Limits, date=date)  # using kwarg binding