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

Question

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)

Answer 1

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)')

Answer 2

This gist shows how to connect to an AS/400 via pyodbc:

https://gist.github.com/BietteMaxime/6cfd5b2dc2624c094575

A few notes; in this example, SYSTEM is the DSN you're set up for the AS/400 in the with pyodbc.connect statement. You could also switch this to be SERVER and PORT with these modifications:

import pyodbc

class CommitMode:
    NONE = 0  # Commit immediate (*NONE)  --> QSQCLIPKGN
    CS = 1  # Read committed (*CS)        --> QSQCLIPKGS
    CHG = 2  # Read uncommitted (*CHG)    --> QSQCLIPKGC
    ALL = 3  # Repeatable read (*ALL)     --> QSQCLIPKGA
    RR = 4  # Serializable (*RR)          --> QSQCLIPKGL

class ConnectionType:
    READ_WRITE = 0 # Read/Write (all SQL statements allowed)
    READ_CALL = 1 # Read/Call (SELECT and CALL statements allowed)
    READ_ONLY = 2 # Read-only (SELECT statements only)

def connstr(server, port, commit_mode=None, connection_type=None):
    _connstr = 'DRIVER=iSeries Access ODBC Driver;SERVER={server};PORT={port};SIGNON=4;CCSID=1208;TRANSLATE=1;'.format(
        server=server,
        port=port,
    )
    if commit_mode is not None:
        _connstr = _connstr + 'CommitMode=' + str(commit_mode) + ';'
    if connection_type is not None:
        _connstr = _connstr + 'ConnectionType=' + str(connection_type) + ';'

    return _connstr

def main():
    with pyodbc.connect(connstr('myas400.server.com', '8471', CommitMode.CHG, ConnectionType.READ_ONLY)) as db:
        cursor = db.cursor()
        cursor.execute(
            """
            SELECT * FROM IASP.LIB.FILE
            """
        )
        for row in cursor:
            print(' '.join(map(str, row)))

if __name__ == '__main__':
    main()

I cleaned up some PEP-8 as well. Good luck!