Try to poke DB on IBM i with python + JDBC using JayDeBeApi

Can Python retrieve data on IBM i?

By chance, I had to retrieve data from IBM i (also known as AS400).

Thing you want to do

I want to retrieve data on IBM i and use it on Linux or Windows Sometimes you want to use the data in your system for machine learning or analysis. If you simply use Python, you can also process it on IBM i.

However, this time I want to use GPU for DL or ML, so I need to bring the data on Linux. For that, I'm going to pull the data with JDBC + Python.

Of course, you can use Java to fetch the data. This time I decided to use JDBC from Python because I am better at Python. (DL and ML are often written in Python, so it may be easier to include in that process)

Try using JayDeBeApi

This time, we will use JayDeBeApi as a Python library for hitting the JDBC API. https://github.com/baztian/jaydebeapi

It supports quite a lot of types of DB. There is also DB2!

Supported databases

In theory every database with a suitable JDBC driver should work. It is confirmed to work with the following databases:

SQLite Hypersonic SQL (HSQLDB) IBM DB2 IBM DB2 for mainframes Oracle Teradata DB Netezza Mimer DB Microsoft SQL Server MySQL PostgreSQL many more...

Preparation

This time, it runs on Python 3.6.

The version of each software is as follows

Installation

You can install it with pip.

pip install JayDeBeApi

If the version of JPype is the latest, an error occurred at the time of execution, so I reinstalled it with 0.6.3.

pip install JPype1==0.6.3 --force-reinstall

JDBC driver preparation

This time, we will use the Jar file provided as standard on IBM i. Since jt400.jar is stored in the following directory, copy it from IBM i to the Linux environment to be used.

/QIBM/ProdData/HTTP/Public/jt400/lib/jt400.jar

Easy data acquisition

Set <hostname>, <user>, and <password> according to your environment.

import jaydebeapi

conn = jaydebeapi.connect("com.ibm.as400.access.AS400JDBCDriver", "jdbc:as400://<hostname>", ["<user>", "<password>"], "/home/IBMi/jt400.jar",)

cur = conn.cursor()
cur.execute("select * from TSLIB.WORKDAY")
cur.fetchall()

curs.close()
conn.close()

I was able to get the data from IBM i by running this Python script. By changing the SQL statement of ʻexecute`, you can also change the operation on the DB.

Recommended Posts

Try to poke DB on IBM i with python + JDBC using JayDeBeApi
[IBM Cloud] I tried to access the Db2 on Cloud table from Cloud Funtions (python)
I tried to implement Minesweeper on terminal with python
Try to operate DB with Python and visualize with d3
I want to AWS Lambda with Python on Mac!
I get an error when I try to raise Python to 3 series using pyenv on Catalina
Try to operate Facebook with Python
Notes on using rstrip with python.
Try to log in to Netflix automatically using python on your PC
I tried to summarize everyone's remarks on slack with wordcloud (Python)
I tried to make a todo application using bottle with python
Try logging in to qiita with Python
Try mathematical formulas using Σ with python
I wanted to solve ABC160 with Python
I want to analyze logs with Python
I want to play with aws with python
Try to operate Excel using Python (Xlwings)
I wanted to solve ABC172 with Python
[Python] I tried to visualize the night on the Galactic Railroad with WordCloud!
Put Cabocha 0.68 on Windows and try to analyze the dependency with Python
I want to tweet on Twitter with Python, but I'm addicted to it
I tried with the top 100 PyPI packages> I tried to graph the packages installed on Python
I want to use MATLAB feval with python
Strategy on how to monetize with Python Java
I wanted to solve NOMURA Contest 2020 with Python
I want to email from Gmail using Python.
Try to solve the man-machine chart with Python
Try to extract Azure document DB document with pydocumentdb
Try to draw a life curve with python
I want to make a game with Python
Python: Try using the UI on Pythonista 3 on iPad
I wanted to install Python 3.4.3 with Homebrew + pyenv
I tried to get CloudWatch data with Python
I tried using mecab with python2.7, ruby2.3, php7
Try to make a "cryptanalysis" cipher with Python
Three things I was addicted to when using Python and MySQL with Docker
Try to automatically generate Python documents with Sphinx
I want to use Temporary Directory with Python2
Try working with Mongo in Python on Mac
Introduction to Python with Atom (on the way)
#Unresolved I want to compile gobject-introspection with Python3
I want to solve APG4b with Python (Chapter 2)
Try to make a dihedral group with Python
When I try to use Jupiter notebook on Mac, I can only select python2
I'm an amateur on the 14th day of python, but I want to try machine learning with scikit-learn
I want to write to a file with Python
Create a Python execution environment on IBM i
Connect to MySQL with Python on Raspberry Pi
Try to detect fish with python + OpenCV2.4 (unfinished)
When I tried to create a project using Python on Docker with PyCharm, it didn't work, but it worked with Docker Compose.
The first artificial intelligence. I wanted to try natural language processing, so I will try morphological analysis using MeCab with python3.
Virtualize (isolate) IBM i python development environment with chroot
Try to solve the programming challenge book with python3
[First API] Try to get Qiita articles with Python
Try to make a command standby tool with python
I want to handle optimization with python and cplex
I tried to get started with blender python script_Part 01
(Python) Try to develop a web application using Django
I tried to touch the CSV file with Python
Install selenium on Mac and try it with python
I tried to draw a route map with Python