How to use variables in SQL query when using Python and pyodbc

I am using Python to extract data from SQL by using ODBC to linking Python to SQL database. when I do the query, I need to use variables in the query to make my query result changeable. For example, my code is:

import pyodbc
myConnect = pyodbc.connect('DSN=B1P HANA;UID=***;PWD=***')
myCursor = myConnect.cursor()
Start = 20180501
End = 20180501
myOffice = pd.Series([1,2,3])
myRow = myCursor.execute("""
                         SELECT "CALDAY" AS "Date",
                                "/BIC/ZSALE_OFF" AS "Office"
                         FROM "SAPB1P"."/BIC/AZ_RT_A212"
                         WHERE "CALDAY" BETWEEN 20180501 AND 20180501
                         GROUP BY "CALDAY","/BIC/ZSALE_OFF" 
                         """)
Result = myRow.fetchall()
d = pd.DataFrame(columns=['Date','Office'])
for i in Result:
  d= d.append({'Date': i.Date,
               'Office': i.Office},
               ignore_index=True)

You can see that I retrieve data from SQL database and save it into a list (Result), then I convert this list to a data frame (d).

But, my problems are:

I need to specify a start date and an end data in myCursor.execute part, something like "CALDAY" BETWEEN Start AND End
Let’s say I have 100 offices in my data. Now I just need 3 of them (myOffice). So, I need to put a condition in myCursor.execute part, like myOffice in (1,2,3)

In R, I know how to deal with these two problems. the code is like:

office_clause = ""
if (myOffice != 0) {
  office_clause = paste(
    'AND "/BIC/ZSALE_OFF" IN (',paste(myOffice, collapse=", "),')'
  )
}
a <- sqlQuery(ch,paste(' SELECT ***
                         FROM ***
                         WHERE "CALDAY" BETWEEN',Start,'AND',End,'
                               ',office_clause1,'
                         GROUP BY ***
                       '))

But I do not know how to do this in Python. How can I do this?

Answer

You can use string formatting operations for this.

First define

query = """
SELECT 
    "CALDAY" AS "Date",
    "/BIC/ZSALE_OFF" AS "Office"
FROM 
    "SAPB1P"."/BIC/AZ_RT_A212"
WHERE 
    "CALDAY" BETWEEN {start} AND {end}
    {other_conds}
GROUP BY 
    "CALDAY","/BIC/ZSALE_OFF" 
"""

Now you can use

myRow = myCursor.execute(query.format(
    start='20180501'
    end='20180501',
    other_conds=''))

and

myRow = myCursor.execute(query.format(
    start='20180501'
    end='20180501',
    other_conds='AND myOffice IN (1,2,3)'))

Advertisement

Answer