Working with databases - Connecting to Db2 on cloud using python

In the below example, we will connect to Db2 on my ibm cloud account using my credentials below:

password: "***"
username: "*"
database: "bludb"
hostname: "2d46b6b4-cbf6-40eb-bbce-**.bs2io90l08kqb1od8lcg.databases.appdomain.cloud"
port: "32***"

Note: I have replaced some of the charachters with * for privacy reasons

Frist, we have to prepare the enviroment:

In [1]:
!pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.3
!pip uninstall sqlalchemy==1.4 -y && pip install sqlalchemy==1.3.24
!pip install ipython-sql

Collecting ibm_db==3.1.0
  Using cached ibm_db-3.1.0-py3-none-any.whl
Collecting ibm_db_sa==0.3.3
  Using cached ibm_db_sa-0.3.3-py3-none-any.whl
Collecting sqlalchemy>=0.7.3
  Using cached SQLAlchemy-1.4.35-cp39-cp39-win_amd64.whl (1.6 MB)
Collecting greenlet!=0.4.17
  Using cached greenlet-1.1.2-cp39-cp39-win_amd64.whl (101 kB)
Installing collected packages: greenlet, sqlalchemy, ibm-db-sa, ibm-db
  Attempting uninstall: greenlet
    Found existing installation: greenlet 1.1.2
    Uninstalling greenlet-1.1.2:
      Successfully uninstalled greenlet-1.1.2
  Attempting uninstall: sqlalchemy
    Found existing installation: SQLAlchemy 1.3.24
    Uninstalling SQLAlchemy-1.3.24:
      Successfully uninstalled SQLAlchemy-1.3.24
  Attempting uninstall: ibm-db-sa
    Found existing installation: ibm-db-sa 0.3.3
    Uninstalling ibm-db-sa-0.3.3:
      Successfully uninstalled ibm-db-sa-0.3.3
  Attempting uninstall: ibm-db
    Found existing installation: ibm-db 3.1.0
    Uninstalling ibm-db-3.1.0:
Found existing installation: SQLAlchemy 1.4.35
Uninstalling SQLAlchemy-1.4.35:
  Successfully uninstalled SQLAlchemy-1.4.35
Collecting sqlalchemy==1.3.24
  Using cached SQLAlchemy-1.3.24-cp39-cp39-win_amd64.whl (1.2 MB)
Installing collected packages: sqlalchemy
Successfully installed sqlalchemy-1.3.24
Requirement already satisfied: ipython-sql in c:\users\user\anaconda3\lib\site-packages (0.4.0)
Requirement already satisfied: ipython-genutils>=0.1.0 in c:\users\user\anaconda3\lib\site-packages (from ipython-sql) (0.2.0)
Requirement already satisfied: six in c:\users\user\anaconda3\lib\site-packages (from ipython-sql) (1.16.0)
Requirement already satisfied: ipython>=1.0 in c:\users\user\anaconda3\lib\site-packages (from ipython-sql) (7.29.0)
Requirement already satisfied: prettytable<1 in c:\users\user\anaconda3\lib\site-packages (from ipython-sql) (0.7.2)
Requirement already satisfied: sqlparse in c:\users\user\anaconda3\lib\site-packages (from ipython-sql) (0.4.2)
Requirement already satisfied: sqlalchemy>=0.6.7 in c:\users\user\anaconda3\lib\site-packages (from ipython-sql) (1.3.24)
Requirement already satisfied: setuptools>=18.5 in c:\users\user\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (58.0.4)
Requirement already satisfied: traitlets>=4.2 in c:\users\user\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (5.1.0)
Requirement already satisfied: pickleshare in c:\users\user\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (0.7.5)
Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in c:\users\user\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (3.0.20)
Requirement already satisfied: pygments in c:\users\user\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (2.10.0)
Requirement already satisfied: colorama in c:\users\user\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (0.4.4)
Requirement already satisfied: decorator in c:\users\user\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (5.1.0)
Requirement already satisfied: backcall in c:\users\user\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (0.2.0)
Requirement already satisfied: jedi>=0.16 in c:\users\user\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (0.18.0)
Requirement already satisfied: matplotlib-inline in c:\users\user\anaconda3\lib\site-packages (from ipython>=1.0->ipython-sql) (0.1.2)
Requirement already satisfied: parso<0.9.0,>=0.8.0 in c:\users\user\anaconda3\lib\site-packages (from jedi>=0.16->ipython>=1.0->ipython-sql) (0.8.2)
Requirement already satisfied: wcwidth in c:\users\user\anaconda3\lib\site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0->ipython>=1.0->ipython-sql) (0.2.5)
In [2]:
import ibm_db

Second, to connect to DB2 database, it requires the following information:

  • Driver Name
  • Database name
  • Host DNS name or IP address
  • Host port
  • Connection protocol
  • User ID (or username)
  • User Password
In [3]:
dsn_hostname = "2d46b6b4-cbf6-40eb-bbce-6251e6ba0300.bs2io90l08kqb1od8lcg.databases.appdomain.cloud"
dsn_uid = "sph70874"
dsn_pwd = "YAG9t9DkcNKmveyp"

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

Now let's build the dsn connection string using the credentials above

In [5]:
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)

print(dsn)

DRIVER={IBM DB2 ODBC DRIVER};DATABASE=BLUDB;HOSTNAME=2d46b6b4-cbf6-40eb-bbce-6251e6ba0300.bs2io90l08kqb1od8lcg.databases.appdomain.cloud;PORT=32328;PROTOCOL=TCPIP;UID=sph70874;PWD=YAG9t9DkcNKmveyp;SECURITY=SSL;

Third, we will establish the database connection

In [6]:
try:
    conn = ibm_db.connect(dsn, "", "")
    print ("Connected to database! ")

except:
    print ("Unable to connect: ", ibm_db.conn_errormsg() )

Connected to database!

Let's retrieve the metadata for the Database Client / Driver

In [7]:
client = ibm_db.client_info(conn)

print ("DRIVER_NAME:          ", client.DRIVER_NAME)
print ("DRIVER_VER:           ", client.DRIVER_VER)
print ("DATA_SOURCE_NAME:     ", client.DATA_SOURCE_NAME)
print ("DRIVER_ODBC_VER:      ", client.DRIVER_ODBC_VER)
print ("ODBC_VER:             ", client.ODBC_VER)
print ("ODBC_SQL_CONFORMANCE: ", client.ODBC_SQL_CONFORMANCE)
print ("APPL_CODEPAGE:        ", client.APPL_CODEPAGE)
print ("CONN_CODEPAGE:        ", client.CONN_CODEPAGE)

DRIVER_NAME:           DB2CLI.DLL
DRIVER_VER:            11.05.0600
DATA_SOURCE_NAME:      BLUDB
DRIVER_ODBC_VER:       03.51
ODBC_VER:              03.01.0000
ODBC_SQL_CONFORMANCE:  EXTENDED
APPL_CODEPAGE:         1252
CONN_CODEPAGE:         1208

Fourth, we will close the connection

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