Working with DB2 and Python: My Approach¶

Author: Mohammad Sayem Chowdhury

In this notebook, I connect to a DB2 database using Python, create a table, insert data, run queries, and work with the results in pandas. All steps are based on my own experience and workflow.

In [ ]:
# If you're running this outside my environment, you may need to install these libraries:
# !pip install ibm_db ibm_db_sa ipython-sql
In [2]:
import ibm_db

To connect to DB2, you'll need your credentials (hostname, username, password, etc.). Enter them below:

In [ ]:
# Replace the placeholder values with your actual Db2 hostname, username, and password:

dsn_hostname = ""
dsn_uid = ""
dsn_pwd = ""
dsn_driver = "{IBM DB2 ODBC DRIVER}"
dsn_database = "BLUDB"
dsn_port = ""
dsn_protocol = "TCPIP"
dsn_security = "SSL"

Now I create the database connection using the ibm_db API.

In [ ]:
# Create database connection
dsn = (
    "DRIVER={0};"
    "DATABASE={1};"
    "HOSTNAME={2};"
    "PORT={3};"
    "PROTOCOL={4};"
    "UID={5};"
    "PWD={6};"
    "SECURITY={7};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd,dsn_security)

try:
    conn = ibm_db.connect(dsn, "", "")
    print ("Connected to database: ", dsn_database, "as user: ", dsn_uid, "on host: ", dsn_hostname)
except:
    print ("Unable to connect: ", ibm_db.conn_errormsg() )

Connected to database:  BLUDB as user:  pgs19748 on host:  6667d8e9-9d4d-4ccb-ba32-21da3bb5aafc.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud

Creating and Populating a Table¶

I create a table called INSTRUCTOR and insert a few rows of data for demonstration.

In [ ]:
# Drop the table if it exists from a previous run
dropQuery = "drop table INSTRUCTOR"
dropStmt = ibm_db.exec_immediate(conn, dropQuery)
In [ ]:
# Create the table
createQuery = "create table INSTRUCTOR(ID INTEGER PRIMARY KEY NOT NULL, FNAME VARCHAR(20), LNAME VARCHAR(20), CITY VARCHAR(20), CCODE CHAR(2))"
createStmt = ibm_db.exec_immediate(conn,createQuery)

Now I insert three rows of data into the INSTRUCTOR table. I use my own names for the first row.

In [ ]:
insertQuery = "insert into instructor(ID,FNAME,LNAME,CITY,CCODE) values(1, 'Mohammad', 'Sayem Chowdhury','Chittagong','BN')"
insertStmt = ibm_db.exec_immediate(conn, insertQuery)
In [ ]:
insertQuery2 = "insert into instructor(ID,FNAME,LNAME,CITY,CCODE) values(2, 'Bruce', 'Wayne','Gotham','US')"
insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2)
In [ ]:
insertQuery3 = "insert into instructor(ID,FNAME,LNAME,CITY,CCODE) values(3, 'Clark', 'Kent','Smallville','US')"
insertStmt3 = ibm_db.exec_immediate(conn, insertQuery3)

Querying the Table¶

I retrieve all rows from the INSTRUCTOR table and print the results.

In [ ]:
selectQuery = "select * from INSTRUCTOR"
selectStmt = ibm_db.exec_immediate(conn, selectQuery)
ibm_db.fetch_both(selectStmt)
Out[ ]:
{'ID': 1,
 0: 1,
 'FNAME': 'Mohammad',
 1: 'Mohammad',
 'LNAME': 'Sayem Chowdhury',
 2: 'Sayem Chowdhury',
 'CITY': 'Chittagong',
 3: 'Chittagong',
 'CCODE': 'BN',
 4: 'BN'}
In [ ]:
# Fetch the rest of the rows and print the ID and FNAME for those rows
while ibm_db.fetch_row(selectStmt) != False:
   print (" ID:",  ibm_db.result(selectStmt, 0), " FNAME:",  ibm_db.result(selectStmt, "FNAME"))

 ID: 2  FNAME: Bruce
 ID: 3  FNAME: Clark

Now I update my city in the table as an example of an update statement.

In [ ]:
updateQuery = "update INSTRUCTOR set CITY ='DHAKA' where FNAME='Mohammad'"
updateStmt = ibm_db.exec_immediate(conn, updateQuery)

Retrieving Data into Pandas¶

I use pandas to retrieve the contents of the INSTRUCTOR table into a DataFrame for further analysis.

In [13]:
import pandas
import ibm_db_dbi
In [ ]:
# Connection for pandas
pconn = ibm_db_dbi.Connection(conn)
In [ ]:
selectQuery = "select * from INSTRUCTOR"
pdf = pandas.read_sql(selectQuery, pconn)
pdf.LNAME[0]
Out[ ]:
'Sayem Chowdhury'
In [ ]:
pdf
Out[ ]:
ID FNAME LNAME CITY CCODE
0 1 Mohammad Sayem Chowdhury DHAKA BN
1 2 Bruce Wayne Gotham US
2 3 Clark Kent Smallville US

Once the data is in a pandas DataFrame, I can use all the usual pandas operations, like checking the shape:

In [17]:
pdf.shape
Out[17]:
(3, 5)

Closing the Connection¶

Always close your database connections to free up resources.

In [18]:
ibm_db.close(conn)
Out[18]:
True

Summary¶

In this notebook, I demonstrated how to connect to a DB2 database, create and populate a table, run queries, and work with the results in pandas—all using my own workflow and examples.

Notebook and analysis by Mohammad Sayem Chowdhury.

All code and explanations are my own. This notebook is shared under the MIT License.