Simple script for connecting to AS/400 and retrieving table data

ibm_i.py

import pyodbc
import json
import os

# A script by John Tsioumpris 

CREDENTIALS_FILE = "as400_credentials.json"

# Function to save credentials
def save_credentials(credentials):
    with open(CREDENTIALS_FILE, "w") as file:
        json.dump(credentials, file)

# Function to load credentials
def load_credentials():
    if os.path.exists(CREDENTIALS_FILE):
        with open(CREDENTIALS_FILE, "r") as file:
            return json.load(file)
    return None

# Function to create a connection
def connect_to_as400(host, library, username, password, naming):
    conn_str = (
        f'DRIVER={{iSeries Access ODBC Driver}};'
        f'SYSTEM={host};'
        f'UID={username};'
        f'PWD={password};'
        f'DATABASE={library};'  # Ensure this is the correct library name
        f'TRANSLATE=1;'
    )
    try:
        conn = pyodbc.connect(conn_str)
        print("✅ Connection Successful!")
        return conn
    except pyodbc.Error as e:
        print(f"❌ Connection Failed: {e}")
        return None

# Get user input
def get_user_input():
    credentials = load_credentials()
    if credentials:
        use_saved = input("Use saved credentials? (yes/no): ").strip().lower()
        if use_saved == "yes":
            return credentials["host"], credentials["library"], credentials["username"], credentials["password"], \
                   credentials["naming"]

    print("\n==============================")
    print("  IBM AS/400 ODBC Connector  ")
    print("==============================\n")

    host = input("Enter AS/400 IP or Hostname: ").strip()
    library = input("Enter Library (Schema): ").strip()  # Enter MASTERIPP1 here
    username = input("Enter Username: ").strip()
    password = input("Enter Password: ").strip()
    naming = input("Enter Naming Convention (system/sql): ").strip().lower()

    save_credentials({"host": host, "library": library, "username": username, "password": password, "naming": naming})

    return host, library, username, password, naming

# Fetch data from table
def fetch_data(conn):
    fetch_data = input("Would you like to pull data from a table? (yes/no): ").strip().lower()
    if fetch_data == "yes":
        library = input("Enter Library Name: ").strip().upper()  # Convert to uppercase
        table_name = input("Enter Table Name: ").strip().upper()  # Convert to uppercase
        query = f'SELECT * FROM "{library}"."{table_name}" FETCH FIRST 10 ROWS ONLY'
        print(f"Executing Query: {query}")
        try:
            cursor = conn.cursor()
            cursor.execute(query)
            columns = [column[0] for column in cursor.description]
            print("\n" + " | ".join(columns))
            print("-" * (len(columns) * 20))
            for row in cursor.fetchall():
                print(" | ".join(str(value) for value in row))
        except pyodbc.Error as e:
            print(f"❌ Error fetching data: {e}")

    # Query to list all tables in the specified library
    list_tables = input("Would you like to list all tables in the library? (yes/no): ").strip().lower()
    if list_tables == "yes":
        library = input("Enter Library Name: ").strip().upper()  # Convert to uppercase
        query = f"SELECT TABLE_NAME FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = '{library}'"
        try:
            cursor = conn.cursor()
            cursor.execute(query)
            tables = cursor.fetchall()
            print("\nAvailable Tables in Library:", library)
            for table in tables:
                print(table[0])  # Print the table name
        except pyodbc.Error as e:
            print(f"❌ Error fetching tables: {e}")


# Main program
def main():
    host, library, username, password, naming = get_user_input()
    conn = connect_to_as400(host, library, username, password, naming)
    if conn:
        fetch_data(conn)
        conn.close()

if __name__ == "__main__":
    main()