Many companies are running common data analytics tasks using python scripts. They are asking employees to convert scripts that may currently exist in SAS or other toolsets to python. One step of this process is being able to pull in the same data with the new techniques. This article is about converting DB2 queries into python scripts.

許多公司正在使用python腳本運行常見的數據分析任務。 他們要求員工將SAS或其他工具集中當前可能存在的腳本轉換為python。 這一過程的第一步是能夠使用新技術提取相同的數據。 本文是關于將DB2查詢轉換為python腳本的。

How do you convert your queries to python? It may sound overwhelming but it’s easier than you think. Once you have a template for your data source, all you need to do is change the query and output filename.

您如何將查詢轉換為python？ 聽起來可能令人難以置信，但比您想像的要容易。 一旦有了用于數據源的模板，您要做的就是更改查詢和輸出文件名。

There are several ways you can do this, but I will outline an intuitive template that allows you to run a DB2 query on your local laptop/desktop.

有幾種方法可以執行此操作，但是我將概述一個直觀的模板，該模板允許您在本地筆記本電腦/臺式機上運行DB2查詢。

在DiscoDonuts使用您的DB2專業知識 (Use your DB2 expertise at DiscoDonuts)

Let’s pretend you work at a large donut company, DiscoDonuts. You have a query to you run the following query against DB2. Typically you might use a tool such as DataStudio. Pretty simple.

假設您在一家大型甜甜圈公司DiscoDonuts工作。 您有一個查詢要針對DB2運行以下查詢。 通常，您可能會使用諸如DataStudio之類的工具。 很簡單

SELECT store_id, donut_style, date, volume, net_sales
 FROM donutsdb.sales_data 
 WHERE date = '2020-08-16'
 WITH UR;

Now you have your manager asking you to start using python. Take a deep breath; it’s not that hard. After the code is set up, you just need to update two fields, the name of your output file and your query itself. Then you hit Run. How simple is that?

現在，您的經理要求您開始使用python。 深吸一口氣； 這并不難。 設置代碼后，您只需要更新兩個字段，即輸出文件的名稱和查詢本身。 然后您點擊運行。 這有多簡單？

初始一次性設置 (The initial one-time setup)

If you haven’t already, you will need to contact your IT department to have a tool (“IDE”) installed (such as PyCharm, VSCode, Jupyter Notebooks).

如果尚未安裝，則需要聯系您的IT部門以安裝工具(“ IDE”)(例如PyCharm，VSCode，Jupyter Notebooks)。

To connect to DB2, you will need to enter your own company’s database, hostname, and port id. Most likely, you already have this information in whatever tool you are currently using.

要連接到DB2，您將需要輸入自己公司的數據庫，主機名和端口ID。 您很可能已經在當前使用的任何工具中獲得了此信息。

模板 (The template)

First, fill in the database connection information. Now you can save this template for use time and time again.

首先，填寫數據庫連接信息。 現在，您可以一次又一次保存此模板以供使用。

For each query you want to run, you update the output filename and the actual query itself. The query is passed to DB2 so it is in the same DB2 format you are already using.

對于每個要運行的查詢，您將更新輸出文件名和實際查詢本身。 該查詢將傳遞到DB2，因此它與您已經在使用的DB2格式相同。

import ibm_db
import ibm_db_dbi
import pandas as pd# name your output file (and path if needed)
output_filename = "donut_sales.csv"# enter your query between the triple quotes
query = """ SELECT store_id, donut_style, date, volume, net_sales
 FROM donutsdb.sales_data 
 WHERE date = '2020-08-16'
 WITH UR; 
 """# one way to do credentialing
import getpass as gp                                     
uid=input('Enter uid:   ')                                                  
pwd=gp.getpass('Enter password (hidden): ')# connect to your database
db = (
    "DRIVER = {IBM DB2 ODBC DRIVER - DB2COPY1};"
    "DATABASE=<your donut database>;"
    "HOSTNAME=<your db2 hostname>;"
    "PORT=<your db2 port ####>;"
    "PROTOCAL=TCPIP;"
    'UID='+uid+';'
    'PWD='+pwd+';')
ibm_db_conn = ibm_db.connect(db, "", "")
pconn = ibm_db_dbi.Connection(ibm_db_conn)#optional if you are using the accelerator #ibm_db.exec_immediate(ibm_db_conn, "SET CURRENT QUERY ACCELERATION = ALL") df = pd.read_sql(query, pconn)
df.to_csv(output_filename,index=False)

Just hit Run. You will be asked to enter your credentials, your query will run on DB2, the data will be transferred back to your script and your file will be created!

只需點擊運行。 系統將要求您輸入憑據，查詢將在DB2上運行，數據將被傳輸回腳本并創建您的文件！

The data frame created can serve as your data for further analysis within the python script if you choose.

如果選擇的話，創建的數據框可以用作數據，以便在python腳本中進行進一步分析。

下一個查詢的三個步驟 (Three steps for your next query)

1. Update your output filename
   更新您的輸出文件名
2. Update your query
   更新查詢
3. Hit Run!
   點擊運行！

結論 (Conclusion)

It is not that hard to transfer your DB2 SQL knowledge to python. This is a great skill to have and share with others.

將DB2 SQL知識轉移到python并不難。 與他人共享和分享這是一項偉大的技能。

* I always welcome feedback. If you have another technique, share it in the responses. There are many ways to approach a problem and I have presented just one of many. Code is ever-evolving so what works today may not work tomorrow.

*我隨時歡迎您提供反饋。 如果您有其他技術，請在回復中分享。 解決問題的方法有很多，我只介紹了其中一種。 代碼在不斷發展，因此今天行之有效的明天可能行不通。