How to make connection in python to connect as400 and call any as400 programs with parameter
Solution 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)')
Solution 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!
Admin
Updated on June 30, 2022Comments
-
Admin almost 2 years
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)
-
John Y over 6 yearsThis seems like an overly complicated way to do what OP already provided code to do, and does nothing to answer the question.
-
FlipperPA over 6 years
Anyone knows How to make connection in python to connect as400 iseries system
seems like a query to me. I haven't connected to an AS/400 in ages, but it isn't as straightforward as connecting to, say, SQL Server. Notice that the question was edited - I didn't see the "can connect" from the oversized font originally. -
John Y over 6 yearsThe only edit was adding a couple of tags. The question body is still in its original form, complete with code that already successfully connects and does a simple query. It is as straightforward as connecting to SQL Server, or anything else. Just look at OP's code.