How to make connection in python to connect as400 and call any as400 programs with parameter

Anyone knows How to make connection in python to connect as400 iseries system and call any as400 programs with parameter.

For example how to create library by connecting as400 through python. I want to call " CRTLIB LIB(TEST) " from python script.

I am able to connect to DB2 database through pyodbc package.

Here is my code to connect DB2 database.

import pyodbc

connection = pyodbc.connect(
    driver='{iSeries Access ODBC Driver}',
    system='ip/hostname',
    uid='username',
    pwd='password')
c1 = connection.cursor()

c1.execute('select * from libname.filename')
for row in c1:
    print (row)

Solution

If your IBM i is set up to allow it, you can call the QCMDEXC stored procedure using CALL in your SQL. For example,

c1.execute("call qcmdexc('crtlib lib(test)')")

The QCMDEXC stored procedure lives in QSYS2 (the actual program object is QSYS2/QCMDEXC1) and does much the same as the familiar program of the same name that lives in QSYS, but the stored procedure is specifically meant to be called via SQL.

Of course, for this example to work, your connection profile has to have the proper authority to create libraries.

It's also possible that your IBM i isn't set up to allow this. I don't know exactly what goes into enabling this functionality, but where I work, we have one partition where the example shown above completes normally, and another partition where I get this instead:

pyodbc.Error: ('HY000', '[HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0901 - SQL system error. (-901) (SQLExecDirectW)')