Wednesday, February 18, 2015

Using DB2 from Python

I have been searching on and off for several years to try and find a simple way to use Python for DB2 queries. Connecting to DB2 from Java is fairly simple--or at least straightforward, because nothing in Java is really that simple--but I have had trouble finding the information and steps for actually making Python speak to DB2. I have finally found the right combination of ingredients for the sauce, though, and it turns out to be extremely simple.

The module needed is called ibm_db. This works on either Python 2.x or Python 3.x and the module can be installed with pip. There are some other options and ways to do this that require additional installation or ODBC components, but this is much simpler. For some reason I could never find a straightforward description of how to do this, so I hope this description will be helpful.

A few notes on installation: On Ubuntu Linux, you will need to install pip to set things up this way. For Python 2.x, that is done with sudo install python-pip. For Python 3.x, that is done with sudo install python3-pip. To install ibm_db issue the command sudo pip install ibm_db for Python 2.x, or sudo pip3 install ibm_db for Python 3. Now you are ready to access DB2. (You can install with pip on other platforms as well, but you are on your own for figuring out how to get and use pip there.)

A simple query script to check things out would be:

This will work in either Python 2.x or Python 3.x. And, of course, substitute the info as needed in the connect statement, but no ODBC DSN or other external trappings need to be set up. Real world applications will need some error handling, etc., but this just demonstrates the basics. Also: there are other fetch methods than fetch_tuple which may be better suited to your needs. But just being able to write simple Python scripts to handle some basic data manipulation instead of having to copy and paste or operate in Java or at the command line is going to make my life much easier in a lot of ways.

4 comments:

  1. Hi there! So glad I found this. I am new to IBM DBs. Will this work for DB2?

    ReplyDelete
  2. Never mind haha, it's right there in the text ...

    ReplyDelete
  3. Port is tricky. Which port is working for you ?

    ReplyDelete
    Replies
    1. I think the default port is 50000, but our DBA has set specific ports for specific databases. Try 50000 or 50001, but you may have to check with whomever set up your database to find out.

      Delete