Introduction

In this article I will show how we can use python on the IBM i to retrieve records from a SQLite database and output the results in a nicely formatted table.

I will be using Python 3, which can be found in Open-Source Package Manager from ACS.

Now we have python, we need the database. For this example, I will be using a SQLite sample database.

Firstly, what is SQLite?

SQLite is a library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

It’s a popular choice for embedded databases in applications like web browsers and web services.

You can utilise the sqlite3 API, which provides a lightweight, simple and reliable database engine. 

One of its main benefits is how easy it is to get started—just create a new file on your IFS and connect to it using the sqlite3 API using Python.

Here are some reasons why you might choose SQLite for your project:

1. Ease of Use: No setup or configuration required; simply include the library and start using it

2. Embeddability: Designed to be embedded into other applications without a separate database server

3. Lightweight: Small library size (typically less than 1MB), ideal for any type of application

4. Serverless: Runs entirely within the application, storing data in a single disk file

Sample Database

For this example, we will be using a sample SQLite database.

The Chinook database provides a realistic dataset related to a music store. Here are some key points about it:

Tables included in the database are as follows:

1. Employees: Stores employee data, including IDs, names, and reporting relationships

2. Customers: Contains customer information

3. Invoices & Invoice Items: These two tables handle invoice data, with the former storing header information and the latter storing line items

4. Artists: Stores artist data (IDs and names)

5. Albums: Contains information about music albums, each associated with an artist

6. Media Types: Stores details about media types (e.g., MPEG audio, AAC audio)

7. Genres: Represents music genres (rock, jazz, metal, etc.)

8. Tracks: Contains song data, with each track belonging to an album

9. Playlists & Playlist Tracks: Playlists store track lists, and the relationship between playlists and tracks is many-to-many

This sample database can be downloaded here https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip

Our example will be using the customer table, which has the following layout;

The highlighted columns will be used as part of our SQL query statement.

Here I have used my favourite database application, the beaver! DBeaver community edition, which can be found here https://dbeaver.io/download/ to show me the column layout of the customer table.

Python Script

And finally, onto the python script I have wrote for this example.

It will have the following functionality

• Connect to the sample database

• Perform a SQL selection statement using parameter markers

• If records have been found, display the result set in a neat table

• The database side of the script will use a TRY block to capture any errors using the database

So, that has got the records we require we now need to display them in a fancy table.

And now onto the piece that glues it all together.

And that is it. A quick test and see what it outputs.

All looking good!

For completeness, the whole script is shown below.

I’m sure you will find this example useful, with many tips included within the code. I even input a few comments, you are honoured!

import sqlite3
from prettytable import *


def read_records_from_database(database_file, city):
    """
    Fetch all records from the customer table in the SQLite database.

    :param table_name: Name of the table to read records from
    :param city: Name of the city to read records from
    :return: List of records (each record represented as a tuple)

    """
    try:

        # convert city argument to uppercase
        city = city.upper()

        # Connect to the Chinook SQLite database
        conn = sqlite3.connect(database_file)
        cursor = conn.cursor()

        # Execute a SELECT query to retrieve all records from the 'track' table
        sql = "SELECT CustomerId, FirstName, LastName, Address, City FROM customer WHERE Upper(city) = ?"
        cursor.execute(sql, (city,))

        records = cursor.fetchall()

        # check if we've have records
        if not records:
            records = 0

        # Close the connection & return all records
        conn.close()
        return records

    except sqlite3.Error as e:
        print(f"An error occurred: {e}")

def display_records(records):
    """
    Output all records found in a pretty table.
    It expects records at this stage, the record check is performed prior to this function.

    :param records: Records found from database query
    :return: None

    """

    # Create a PrettyTable to display the records
    table = PrettyTable()
    table.field_names = ["Customer ID",
                         "First Name", "Last Name", "Address", "City"]

    # add each record to the table
    for record in records:
        table.add_row(record)

    # Print the formatted table
    print(table)


# mainline
if __name__ == "__main__":
    """
    Example usage:
    - Connects to the database.
    - Fetches records for the city 'London'.
    - Displays the records in a PrettyTable.
    """

    records = read_records_from_database("chinook.db", "London")

    if records == 0:
        print("*** No records found in database ***")  # nuffin found
    else:
        # got records, now display them
        display_records(records)