How to connect Python to Db2

Question

Is there a way to connect Python to Db2?

Answer 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.