Connecting to DB2 from Python

To access DB2 data from python the DB2 driver ibm_db has been installed. This driver provides a Python interface for connecting to IBM DB2 and is comprised of:

• ibm_db: Python driver for IBM DB2 databases. Uses the IBM Data Server Driver for ODBC and CLI APIs to connect to IBM DB2 and Informix.
• ibm_db_dbi: Python driver for IBM DB2 databases that complies to the DB-API 2.0 specification.

Import the driver

Once you have the Db2 drivers installed, you need to import the ibm_db driver into your notebook.

import ibm_db

Connect to DB2 PR_SAIL Database

To connect to the database, we need to provide the DB2 driver with a lot of low-level details to enable it to setup the connection with the database. You could be able to use the example code below, at a minimum, you will need to change the user name, and password. 

#Settings
database = "pr_sail"
hostname = "db2.database.ukserp.ac.uk"
port = "60070"
protocol = "TCPIP"
uid = "username"
pwd = "password"
security = "ssl"
ssl_client_keystoredb = "R:\UKSeRP\DB2_SSL\chi.kdb"
ssl_client_keystash = "R:\UKSeRP\DB2_SSL\chi.sth"
#Generate the connection string
conn_str = (
    "DATABASE={0};"
    "HOSTNAME={1};"
    "PORT={2};"
    "PROTOCOL={3};"
    "Security={4};"
    "UID={5};"
    "PWD={6};"
    "SSLClientKeystoredb={7};"
    "SSLClientKeystash={8};"
).format(
    database,
    hostname,
    port,
    protocol,
    security,
    uid,
    pwd,
    ssl_client_keystoredb,
    ssl_client_keystash,
)


#Connect
conn = ibm_db.connect(conn_str, "", "")

Add Pandas support

ibm_db_dbi is needed to provide high level DB-API 2.0 to support Pandas.

import ibm_db_dbi
import pandas

conni = ibm_db_dbi.Connection(conn)

sql = 'SELECT * FROM SCHEMA.TABLE FETCH FIRST 10 ROW ONLY'
df = pandas.read_sql(sql,conni)
print(df)

• Page:
  Connecting to DB2 from Python