How to connect python to db2

问题:

Is there a way to connect python to DB2 ?

回答1:

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.

回答2:

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

• https://code.google.com/p/ibm-db/

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

• http://programmingzen.com/2011/05/12/installing-python-django-and-db2-on-ubuntu-11-04/

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

回答3:

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

回答4:

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)

回答5:

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)

回答6:

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;", "", "")

回答7:

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