sql - 使用Python和Pyodbc时如何在SQL查询中使用变量(How to use variables in SQL query when using Python and pyodbc)

Question

I am using Python to extract data from SQL by using ODBC to linking Python to SQL database.

(我正在使用Python通过使用ODBC将Python链接到SQL数据库来从SQL提取数据。)

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).

(您可以看到我从SQL数据库检索数据并将其保存到列表（结果）中，然后将该列表转换为数据框（d）。)

But, my problems are:

(但是，我的问题是：)

1. I need to specify a start date and an end data in myCursor.execute part, something like "CALDAY" BETWEEN Start AND End

   (我需要在myCursor.execute部分中指定开始日期和结束数据，例如"CALDAY" BETWEEN Start AND End)

2. Let's say I have 100 offices in my data.

   (假设我的数据中有100个办公室。)

   Now I just need 3 of them (myOffice).

   (现在我只需要3个（myOffice）。)

   So, I need to put a condition in myCursor.execute part, like myOffice in (1,2,3)

   (因此，我需要在myCursor.execute部分中放置一个条件，例如myOffice in (1,2,3))

In R, I know how to deal with these two problems.

(在R中，我知道如何处理这两个问题。)

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.

(但是我不知道如何在Python中做到这一点。)

How can I do this?

(我怎样才能做到这一点？)

  ask by Feng Chen translate from so

Answer 1

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)'))