Tuesday, September 6, 2011

How to connect DB2 with Python pyodbc

There are very few infromation about it and this is how I connect to DB2 from python and web2py.

My environment
-----------------------------------------------------------
OS: Windows 7 x86
iSeries Access: V5R3M0 - SI26600
Python: 2.5
pyodbc: pyodbc-2.1.7.win32-py2.5.exe
web2py: 1.98.2 (Source Code)
-----------------------------------------------------------

Pre-Requirements
IBM iSeries Access is installed on your machine so that you have the iSeries Access ODBC Driver.If you use DB2, it's already installed on you machine, isn't it?

1. Install pyodbc
Download and install from http://code.google.com/p/pyodbc/downloads/list.

2. Create ODBC Data Source

2.1 Go to: Control Panel - System and Security - Administrative Tools - Data Sources (ODBC)
2.2 Click Add and select iSeries Access ODBC Driver


2.3 Type Data source name and System (Your AS400 Name)


2.4 On Server tab, type SQL default library


2.5 Click Advanced button at the bottom 
2.6 Change Commit mode: from (*CHG) to (*NONE)

Very important:  If you don't change, you might get the following error.
Error: ('HY000', '[HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - TEST in MYLIB not valid for operation. (-7008)


3. Connect from web2py
Now you are ready to connect.

3.1 Restart your web2py if it's already started
3.2 Create new app like AS400
3.3 Edit DB.PY as follows.
# -*- coding: utf-8 -*-
db = DAL('db2://DSN=MYDSN;UID=YourID;PWD=YourPassword')
db.define_table('test',
    Field('name'))
3.4 Save and click "database administration"



3.5 Check on AS400




3.6 Now click on "Insert new test" from web2py, input Name and click submit



3.7 Check on AS400 - Oh my got! It works !!!


* You can create separate DSN to connect different libraries

4 Connect from Python Shell
Notice: I'm using "." instead of "/". If you want,  you can change "Naming Conversion" to *SYS on the Server tab in the data source you created.

4.1 SELECT

>>> import pyodbc
>>> conn = pyodbc.connect('DSN=MYDSN;UID=YourID;PWD=YourPassword')
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT * FROM MYLIB.TEST")
<pyodbc.Cursor object at 0x01E6A598>
>>> for row in cursor:
print row
4.2 INSERT
(1, 'Omi Chiba')
>>> cursor.execute("INSERT INTO MYLIB.TEST VALUES(DEFAULT, 'PYTHON ROLL')")
<pyodbc.Cursor object at 0x01E6A598>




4.3 Check on AS400




23 comments:

  1. Hi,

    Could you do a video Tutorial on this..??

    i work with AS400 but don't know alot of python, but i am very interested.

    Thanks

    ReplyDelete
    Replies
    1. I have read your blog its very attractive and impressive. I like it your blog.

      Java Online Training Java EE Online Training Java EE Online Training Java 8 online training Core Java 8 online training

      Java Online Training from India Java Online Training from India Java Online Training

      Delete
  2. Master,

    I've nerver done video tutorial but maybe I can do without voice because I'm a little shy :)

    I'm also an AS400 Programmer over 10 years and started python last summer so it should be pretty easy.

    Are you interested in connecting AS400 with web2py or just "4 Connect from Python Shell" ?

    I have an application running on our intranet site and it's directly talk to AS400, User can input the data in the web form...etc. That's why I love web2py !!

    ReplyDelete
  3. hi omi san..thanks for the neat post..just like you, i'm an as400 programmer who's trying to learn python.
    may i know how you've used web2py in your company as far as as400 applications are concerned? have you used it to interface with your as400 data?
    and how's the performance of web2py in production? how many users access your web form?
    sorry to post these many questions, as i'm eager to start python here in our company. many thanks!

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Java Online Training Java Online Training Java Online Training Java Online Training Java Online Training Java Online Training

      Hibernate Online Training Hibernate Online Training Spring Online Training Spring Online Training Spring Batch Training Online Spring Batch Training Online

      Delete
  4. >may i know how you've used web2py in your company as far as as400 >applications are concerned? have you used it to interface with your >as400 data?

    Yes, I created a couple apps to use existing AS400 data. It's working pretty well !! You can also set different database for output file so it will be more flexible. For example, I use AS400 data for input and SQL 2008 R2 for output tables.

    >how's the performance of web2py in production? how many users access >your web form?
    Our AS400 is in Japan but still performace is OK. I mean the access speed is not super fast but enough for daily operations. Of course it will be more fast if your AS400 is closed to your location. We have about 100 users use the apps but I don't here any compain about performances.

    I strongly recommend using web2py with AS400. You can create simple input menus much faster than if you program on AS400 and it provides the better user interface to users.

    ReplyDelete
  5. hi omi san

    thanks for the quick reply. btw, i was also the one who posted on your other entry about stored procs returning values via pyodbc. i hope you can suggest another library/tool to achieve this purpose, as i am planning to call as400 programs from web2py but get the user to see the result/s.

    about web2py implementation, can i know if you've implemented it in the as400 server itself? i haven't come across any material about this, and i only got to implement the iseriespython port in our box. appreciate if you can let me know how you implemented your web2py.

    lastly, as an as400 programmer yourself, can you recommend any good web2py resource/tutorial that can help me pick up speed with implementing and using web2py in our company?

    thanks a lot!

    ReplyDelete
    Replies
    1. > i hope you can suggest another library/tool to achieve this purpose, as i am >planning to call as400 programs from web2py but get the user to see the >result/s.
      I will let you know when I find the way !

      >can i know if you've implemented it in the as400 server itself?
      No, it's not on AS400 server but different box. In my case, I installed on Windows 2008 R2 x86 server with apache just like I posted on the different article.

      >any good web2py resource/tutorial that can help me pick up speed with >implementing and using web2py in our company?

      Once you made a db connection with pyodbc, it's just web2py app. If you're really new to web2py, I recommend to do overview secion of official web2py document.

      http://web2py.com/books/default/chapter/29/3

      Also, database abstraction layer, here you can learn most of SQLs to operate AS400 tables.

      http://web2py.com/books/default/chapter/29/6

      Delete
  6. hi omi san..thanks for the reply..will look into this

    ReplyDelete
  7. hi omi san..if i'm looking towards doing mobile app development using python, which platform would you suggest? thanks

    ReplyDelete
  8. Do you mean web app running on mobile ? Then I would say try web2py first because recently we're trying to adapt mobile layout. Default welcome app should look fine on the most of mobile.

    ReplyDelete
  9. hi omi san..are you talking about the jquery mobile plugin? saw a demo in youtube and looks kind of neat. you have some sample codes in web2py that makes use of jquery mobile?

    btw, i tried viewing web2py using my ipod, it's not 'mobile' yet and the login form will not work properly (the keypad will just suddenly roll down again after a few keystrokes and won't let me finish my data entry)

    ReplyDelete
  10. Oh... so web2py is not mobile friendly yet. I don't have much experience with development for mobile. Maybe you can try using Bootsratp with web2py. http://twitter.github.com/bootstrap/

    ReplyDelete
  11. This is just wonderful guidance and helped me at right time! thanks OBIEE Online Training

    ReplyDelete
  12. Appreciation for nice Updates, I found something new and folks can get useful info about BEST ONLINE TRAINING

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. Hi Omi
    auth_membership and auth_permissions are created as AUTH_00001 and AUTH_00002

    what is your suggestion?
    Also when i register a user i get an error in the browser but the record is created.

    ReplyDelete
  15. Wonderful bloggers like yourself who would positively reply encouraged me to be more open and engaging in commenting.So know it's helpful.
    Python Training in Chennai

    ReplyDelete
  16. Really it was an awesome article...very interesting to read..You have provided an nice article....Thanks for sharing..
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete