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




35 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
  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
  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. Thanks very much. was able to connect to IBM i using pyodbc.

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

    ReplyDelete
  17. Thanks for such a great article here. I was searching for something like this for quite a long time and at last I’ve found it on your blog. It was definitely interesting for me to read  about their market situation nowadays.
    Data Science training in Chennai
    Data science online training

    ReplyDelete
  18. I have read a few of the articles on your website now, and I really like your style of blogging. I added it to my favourites blog site list and will be checking back soon.
    Data Science training in rajaji nagar
    Data Science with Python training in chennai
    Data Science training in electronic city
    Data Science training in USA
    Data science training in bangalore

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

    ReplyDelete

  20. Your very own commitment to getting the message throughout came to be rather powerful and have consistently enabled employees just like me to arrive at their desired goals.
    Python Training in Chennai
    Datascience Training in Chennai
    RPA Training in Chennai
    DevOps Training in Chennai
    AWS Training in Chennai

    ReplyDelete
  21. Hello Omi.. Can you let me know how can I fetch large chunk of data from AS400 to pandas dataframe. I am finding difficult to retrieve the data into my Python application. Small data was fine but data like in millions of records are taking very long and also memory issue.
    I am using PYODBC to connect to my ibm i server.

    Thanks

    ReplyDelete
  22. Really i am Enjoy Reading all the Articles...Thanks for Such an Interesting Information's and waiting to read many more Articles like this....
    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    ReplyDelete
  23. Thanks for such a great article here. I was searching for something like this for quite a long time and at last, I’ve found it on your blog. It was definitely interesting for me to read about this information.
    devops training in chennai | devops training in anna nagar | devops training in omr | devops training in porur | devops training in tambaram | devops training in velachery

    ReplyDelete
  24. I was searching for something like this for quite a long time and at last, I’ve found it on your blog. It was definitely interesting for me to read about this information. I truly adored reading your posting. Thank you!
    python training in chennai

    python online training in chennai

    python training in bangalore

    python training in hyderabad

    python online training

    python flask training

    python flask online training

    python training in coimbatore

    ReplyDelete
  25. Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you..Keep update more information..
    DevOps Training in Chennai

    DevOps Course in Chennai

    ReplyDelete
  26. your article is very interesting to read and its informative.thanks for sharing.Angular training in Chennai

    ReplyDelete


  27. Dotnet Training in Chennai
    Core java Training in Chennai
    Web design Training in Chennai
    Nice blog thank you .For your Sharing It's a pleasure to read your post.It's full of information I'm looking for and I'd like to express that "The content of your post is awesome"

    ReplyDelete