0

I have a problem setting which involves data manipulation on an IBMi as/400 database. I'm trying to solve the problem with the help of python and pandas.

For the last days I'm was trying to set up a proper connection to the as/400 db which every combination of package, driver, dialect whatsoever that I could find on SO or Google. Neither of the solutions is fully working for me. Some are better, while others are not working at all.

Here's the current situation:

I'm able to read and write data through pyodbc. The connection string I'm using is the following:

cstring = urllib.parse.quote("DRIVER={IBM i Access ODBC Driver};SYSTEM=IP;UID=XXX;PWD=YYY;PORT=21;CommitMode=0;SIGNON=4;CCSID=1208;TRANSLATE=1;")

Then I establish the connection like so:

connection = pypyodbc.connect(cstring)

With connection I can read and write data from/to the as400 db through raw SQL statements:

connection.execute("""CREATE TABLE WWNMOD5.temp(
    store_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    store_name VARCHAR(150),
    PRIMARY KEY (store_id)
)""")

This is, of course, a meaningless example. My goal would be to write a pandas DataFrame to the as400 by using

df.to_sql()

But when trying to do something like this:

df.to_sql('temp', connection, schema='WWNMOD5', chunksize=1000, if_exists='append', index=False)

I get this error:

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42000', '[42000] [IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token ; ungültig. Gültige Token: <ENDE DER ANWEISUNG>.')

Meaning that an invalid token was used which in this case I believe is the ';' at the end of the SQL statement.

I believe that pandas isn't compatible with the pyodbc package. Therefore, I was also trying to work with the db over sqlalchemy. With sqlalchemy, I establish the connection like so:

engine= sa.create_engine("iaccess+pyodbc:///?odbc_connect=%s"%cstring)

I also tried to use ibm_db_sa instead of iaccess but the result is always the same. If I do the same from above with sqlalchemy, that is:

df.to_sql('temp', engine, schema='WWNMOD5', chunksize=1000, if_exists='append', index=False)

I don't get any error message but the table is not created either and I don't know why.

Is there a way how to get this working? All the SO threads are only suggesting solutions for establishing a connection and reading data from as400 databases but don't cover writing data back to the as400 db via python.

TheDude
  • 1,205
  • 2
  • 13
  • 21
  • You write you have no error but the table is not created in the last part. How did you check ? Did you try `select * from WWNMOD5.temp` AND `select * from WWNMOD5."temp"` ? – nfgl Feb 03 '22 at 11:17
  • I'm checking this with DBeaver which doesn't display the table. But also, when trying to get the table through raw SQL statement, I don't get a result back. I don't get an error in this case as well. But cannot fetch the data entries from the table neither. So `fetchall()` is printing nothing. – TheDude Feb 03 '22 at 11:24
  • 1
    your error says `FROM sqlite_master` ... which isn't a table I'd expect to see on on IBM i – Charles Feb 03 '22 at 14:30
  • Can you add the result of `select ordinal_position, message_id,message_text from table(JOBLOG_INFO(qsys2.job_name)) order by ordinal_position desc` executed in the same connection as the create table tha does not return an error, after it has been executed ? – nfgl Feb 03 '22 at 16:09
  • Still no output for this statement. However, with the connection with which I get some output, I get the following (in german): `[(5, 'SQL799C', 'Die folgenden Sonderregister wurden festgelegt: CLIENT_APPLNAME: PYTHON'), (4, 'CPF1301', 'Abrechnungsdaten für 272442/QUSER/QZDASOINIT nicht protokolliert; Ursache: 1.'), (3, 'CPIAD02', 'Benutzer UID an Client IP ist mit dem Server verbunden.'), (2, 'CPF1301', 'Abrechnungsdaten für 272442/QUSER/QZDASOINIT nicht protokolliert; Ursache: 1.'), (1, 'CPF1124', 'Job 272442/QUSER/QZDASOINIT im Subsystem QUSRWRK in QSYS am 04.02.22 um 10:20:13 gestartet.` – TheDude Feb 04 '22 at 09:24
  • + `Job im System am 04.02.22 um 10:20:13. angekommen.')]` – TheDude Feb 04 '22 at 11:16
  • Did you read my response to your comment on [my answer to a different question](https://stackoverflow.com/a/35494302/95852)? – John Y Feb 06 '22 at 04:26
  • Yes, I have read your answer. I don't know about SQLAlchemy dropping the error msg somewhere on the way. Could be. Regarding Commitment Control, I feel that I have covered that problem. Earlier I had the problem that I couldn't write at all to our as/400 with any connection due to some journaling issues, I believe. That's why I set the option `CommitMode=0` for the `pyodbc` connection. After that I can write to the db using plain SQL statements. Is it that what you had in mind? If so, I believe I have sorted out this issue, otherwise I still couldn't write. – TheDude Feb 06 '22 at 16:42

1 Answers1

0

It looks like you are using the wrong driver. Pandas claims support for any DB supported by SQLAlchemy. But in order for SQLAlchemy to use DB2, it needs a third party extension.

This is the one recommended by SQLAlchemy: https://pypi.org/project/ibm-db-sa/

jmarkmurphy
  • 11,030
  • 31
  • 59
  • You mean changing `engine= sa.create_engine("iaccess+pyodbc:///?odbc_connect=%s"%cstring)` to `engine= sa.create_engine("ibm_db_sa://UID:PWD@HOST:PORT)` ? With this I get the following error: `ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "IP". Communication function detecting the error: "connect". Protocol specific error code(s): "10060", "*", "*". SQLSTATE=08001\r SQLCODE=-30081` – TheDude Feb 04 '22 at 09:09
  • did you install all the parts? – jmarkmurphy Feb 04 '22 at 23:06
  • yes, I believe so. – TheDude Feb 07 '22 at 12:47