Can read but not write to as/400 database with python

Question

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.

Answer 1

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/