The world’s leading publication for data science, AI, and ML professionals.

How to convert DB2 queries to python scripts

Providing a simple 3-step template to upskill and transition to python

Photo by Christina @ wocintechchat.com on Unsplash
Photo by Christina @ wocintechchat.com on Unsplash

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.

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.

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.

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.

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?

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

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.

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.

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!

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

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.


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


Related Articles

Some areas of this page may shift around if you resize the browser window. Be sure to check heading and document order.