python db2查询_如何将DB2查询转换为python脚本

python db2查询

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.

*我随时欢迎您提供反馈。 如果您有其他技术，请在回复中分享。 解决问题的方法有很多，我只介绍了其中一种。 代码在不断发展，因此今天行之有效的明天可能行不通。

翻译自: https://towardsdatascience.com/how-to-convert-db2-queries-to-python-scripts-f46960ed8df9

python db2查询