36
votes

Is there a way to connect Python to Db2?

12

12 Answers

30
votes

The documentation is difficult to find, and once you find it, it's pretty abysmal. Here's what I've found over the past 3 hours.

You need to install ibm_db using pip, as follows:

pip install ibm_db

You'll want to create a connection object. The documentation is here.

Here's what I wrote:

from ibm_db import connect
# Careful with the punctuation here - we have 3 arguments.
# The first is a big string with semicolons in it.
# (Strings separated by only whitespace, newlines included,
#  are automatically joined together, in case you didn't know.)
# The last two are emptry strings.
connection = connect('DATABASE=<database name>;'
                     'HOSTNAME=<database ip>;'  # 127.0.0.1 or localhost works if it's local
                     'PORT=<database port>;'
                     'PROTOCOL=TCPIP;'
                     'UID=<database username>;'
                     'PWD=<username password>;', '', '')

Next you should know that commands to ibm_db never actually give you results. Instead, you need to call one of the fetch methods on the command, repeatedly, to get the results. I wrote this helper function to deal with that.

def results(command):
    from ibm_db import fetch_assoc

    ret = []
    result = fetch_assoc(command)
    while result:
        # This builds a list in memory. Theoretically, if there's a lot of rows,
        # we could run out of memory. In practice, I've never had that happen.
        # If it's ever a problem, you could use
        #     yield result
        # Then this function would become a generator. You lose the ability to access
        # results by index or slice them or whatever, but you retain
        # the ability to iterate on them.
        ret.append(result)
        result = fetch_assoc(command)
    return ret  # Ditch this line if you choose to use a generator.

Now with that helper function defined, you can easily do something like get the information on all the tables in your database with the following:

from ibm_db import tables

t = results(tables(connection))

If you'd like to see everything in a given table, you could do something like this now:

from ibm_db import exec_immediate

sql = 'LIST * FROM ' + t[170]['TABLE_NAME']  # Using our list of tables t from before...
rows = results(exec_immediate(connection, sql))

And now rows contains a list of rows from the 170th table in your database, where every row contains a dict of column name: value.

Hope this all helps.

17
votes

After lots of digging I discovered how to connect with DB2 using ibm_db.

First off, if you use a python version higher than 3.2 use

pip install ibm_db==2.0.8a

version 2.0.8 (the latest) will fail to install.

then use the following to connect

import ibm_db_dbi as db

conn = db.connect("DATABASE=name;HOSTNAME=host;PORT=60000;PROTOCOL=TCPIP;UID=username;PWD=password;", "", "")

list tables with

for t in conn.tables():
    print(t)

and execute SQL with

cursor = conn.cursor()
cursor.execute("SELECT * FROM Schema.Table")
for r in cursor.fetchall():
    print(r)

check this link for official not so accurate documentation

10
votes

ibm-db, the official DB2 driver for Python and Django is here:

Here's a recent tutorial for how to install everything on Ubuntu Linux:

I should mention that there were several older unofficial DB2 drivers for Python. ibm-db is the one you should be using.

3
votes

You can connect to db2 from python using jaydeapi First install library running pip install jaydeapi download db2jcc4.jar Then you can connect using below code : by passing hostname,portno, userid,password database name

import jaydebeapi

conn_src = jaydebeapi.connect(
    'com.ibm.db2.jcc.DB2Driver',
    ['YourHostName:PortNo/DatabaseName','userid','password'],'C:/db2jcc4.jar'
)

cursor=conn_src.cursor()
sql = 'Select * from schemaname.TableName fetch first 100 rows only '

cursor.execute(sql)
print("fetchall:")
result = cursor.fetchall()
for r in result:
    print(r)
3
votes

In addition to @prof1990 response:

Since 2.0.9 (Aug 16th 2018), also with Python 3 you can simply use:

pip install ibm_db

Reference:

https://github.com/ibmdb/python-ibmdb#updated-ibm_db

Example of connection here:

import ibm_db
ibm_db.connect("DATABASE=<dbname>;HOSTNAME=<host>;PORT=<60000>;PROTOCOL=TCPIP;UID=<username>;PWD=<password>;", "", "")

Full API documentation here:

https://github.com/ibmdb/python-ibmdb/wiki/APIs

1
votes

IBM's Db2 is available for various platforms. If you are trying to connect to a Db2 which lives on an IBM i server (formerly known as AS/400, iSeries, or System i), then ibm_db requires a product called Db2 Connect, which is rather expensive. Most people who use Python to connect to Db2 for i use ODBC (usually through PyODBC).

I'm not completely sure about the situation with Db2 on their z (mainframe) servers, but I would think it also requires Db2 Connect.

0
votes

You can use ibm_db library to connect DB2.

query_str = "SELECT COUNT(*) FROM table_name"

conn = ibm_db.pconnect("dsn=write","usrname","secret")
query_stmt   = ibm_db.prepare(conn, query_str)
ibm_db.execute(query_stmt)
0
votes

This is for future reference:

Official installation docs say:

Python 2.5 or later, excluding Python 3.X.

pip install ibm_db

It only worked on Python 2.7 for me; it didn't for 3.X. Also, I had to make Python 2.7 default (instead of Python 3) so that the installation would work (otherwise, there would be installation errors).

Official docs sample usage:

import ibm_db 
ibm_db.connect("DATABASE=name;HOSTNAME=host;PORT=60000;PROTOCOL=TCPIP;UID=username; PWD=password;", "", "")
0
votes

Version: ibm-db 3.0.2 - ibm-db==3.0.2

pip install ibm-db

Released: Jun 17, 2020

Connect to a local or cataloged database:

import ibm_db
conn = ibm_db.connect("database","username","password")

Connect to an uncataloged database:

import ibm_db
ibm_db.connect("DATABASE=name;HOSTNAME=host;PORT=60000;PROTOCOL=TCPIP;UID=username;
            PWD=password;", "", "")
0
votes

There is a way in which one can connect to IBM db2 using nothing but Python requests library.
Have a look at the following video, found it quite useful:
https://youtu.be/PSNBDwgf9ts
You need nothing but the requests library and your db2 credentials. Worked for me.

0
votes

How I managed to do in 2021. What you will need:

  • Python 3.7
  • PipEnv
  • Ibm-db

Ibm-db version is not important but this lib only works with Python 3.7 (current python version is 3.9).

Install Python 3.7.6 in your machine (this is the version that worked).

In your IDE create a new python file.

Let' create a Virtual Enviroment to make sure we will use Python 3.7

pip install pipenv

After installing

pipenv install --python 3.7

Activate the Virtual Environment

pipenv shell

You can use pip list to verify if you are in the new Virtual Enviroment - if list only shows 3 or 4 libs, it's because you are

Now you can download Ibm_db

pip install ibm-db

You may add this to your code to confirm what is the version you are using

from platform import python_version
print(python_version())

Now accessing the DB2

import ibm_db_dbi as db

# Connect to DB2B1 (keep Protocol as TCPIP)
conn = db.connect("DATABASE=DBNAME;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=Your User;PWD=Your Password;", "", "")

Checking all tables available

for t in conn.tables():
    print(t)

Your SQL code:

sql_for_df = """SELECT *
                FROM TABLE
                WHERE ..."""

Visualizing as DataFrame

First install pandas as it will not be present in your Virtual Environment

pip install pandas

After that import to your code and play around

import pandas as pd 

df = pd.read_sql(sql_for_df, conn) 
df.head()

To exit the VIrtual Enviroment just write exit in your terminal. If you want to remove the Virtual Enviroment write in the terminal pipenv --rm

That's pretty much all I could learn so far. I hope it helps you all.

0
votes
# Install : ibm_db package
# Command : pip install ibm_db

import ibm_db
import sys


def get_connection():
    db_name = ""
    db_host_name = ""
    db_port = ""
    db_protocol = ""
    db_username = ""
    db_password = ""

    try:
        conn = ibm_db.connect(
        f"DATABASE = {db_name}; HOSTNAME = {db_host_name}; PORT = {db_port}; PROTOCOL = {db_protocol}; "
        f"UID = {db_username}; PWD = {db_password};", "", "")
        return conn
    except:
        print("no connection:", ibm_db.conn_errormsg())
        sys.exit(1)

get_connection()