mapepire-python

Table of Contents

• mapepire-python
  • Overview
  • Setup
    • Install with pip
    • Server Component Setup
• Quick Start
• Other Connection options
  • 1. Using the DaemonServer object
    • 1.1 Authenticating with Kerberos
      • 1.1.1 Windows
      • 1.1.2 Other Platforms
  • 2. Passing the connection details as a dictionary
  • 3. Using a config file (.ini) to store the connection details
  • 4. Using environment variables
  • TLS Configuration
• Usage
  • 1. Using the SQLJob object to run queries synchronously
    • Query and run
  • 2. Using the PoolJob object to run queries asynchronously
  • 3. Using the Pool object to run queries "concurrently"
  • 4. Using PEP 249 Implementation
    • fetchmany() and fetchall() methods
  • PEP 249 Asynchronous Implementation
• Development Setup
  • Setup python virtual environment with pip and venv
    • Create a new virtual environment
      • Unix/macOS
      • Windows
    • Activate the virtual environment:
      • Unix.macOS
      • Windows
      • Unix/macOS
      • Windows
    • Prepare pip
      • Unix/macOS
      • Windows
    • Install Dependencies using requirements-dev.txt
      • Unix/macOS
      • Windows
  • Setup Python virtual environment with Conda
    • Create an environment from an environment-dev.yml file
      • 1. Activate the new environment:
      • 2. Verify the new environment was installed:
  • Run local test suite

Overview

---

mapepire-python is a Python client implementation for Mapepire that provides a simple interface for connecting to an IBM i server and running SQL queries. The client is designed to work with the Mapepire Server Component

Setup

mapepire-python requires Python 3.10 or later.

[!NOTE] New websocket Implementation: As of version 0.2.0, mapepire-python uses the websockets library for websocket connections. If you are upgrading from a previous version, make sure to update your dependecies. The websocket-client library is no longer supported.

• To update run pip install -U mapepire-python

• More info on websockets

Install with pip

mapepire-python is available on PyPi. Just Run

pip install mapepire-python

Server Component Setup

To use mapire-python, you will need to have the Mapepire Server Component running on your IBM i server. Follow these instructions to set up the server component: Mapepire Server Installation

Quick Start

To get started with mapepire-python, you will need to setup a connection credentials for the Mapepire server. You can use a dictionary to store the connection details:

from mapepire_python import connect

creds = {
  "host": "SERVER",
  "port": 8076,
  "user": "USER",
  "password": "PASSWORD",
}

with connect(creds) as conn:
    with conn.execute("select * from sample.employee") as cursor:
        result = cursor.fetchone()
        print(result)

Other Connection options

[!NOTE] TLS support as of version 0.3.0 is now available. Server certificate verification is enabled by default. To disable certificate verification, set the ignoreUnauthorized field to True in the connection details.

• To update run pip install -U mapepire-python

• More info TLS Configuration here

There are four ways to configure mapepire server connection details using mapepire-python:

1. Using the DaemonServer object
2. Passing the connection details as a dictionary
3. Using a config file (.ini) to store the connection details
4. Using environment variables

1. Using the DaemonServer object

DaemonServer is importable directly from mapepire_python:

from mapepire_python import DaemonServer

creds = DaemonServer(
    host="SERVER",
    port="PORT",
    user="USER",
    password="PASSWORD"
)

Once you have created the DaemonServer object, you can pass it to the SQLJob object to connect to the mapepire server:

from mapepire_python import SQLJob, DaemonServer

creds = DaemonServer(
    host="SERVER",
    port="PORT",
    user="USER",
    password="PASSWORD"
)

job = SQLJob(creds)

1.1 Authenticating with Kerberos

If your IBM i is configured to support Kerberos authentication, you can authenticate using Kerberos instead of passing a plain-text password to the DaemonServer Object.

1.1.1 Windows

If your Windows machine is part of a Kerberos realm and supports SSPI authentication, you can authenticate by creating the DaemonServer as shown below:

from mapepire_python import DaemonServer
from mapepire_python.authentication.kerberosTokenProvider import KerberosTokenProvider

creds = DaemonServer(
    host="SERVER",
    password=KerberosTokenProvider(host="SERVER"),
    user="USER",
    port="PORT",
)

job = SQLJob(creds)

1.1.2 Other Platforms

For non-Windows platforms, Kerberos authentication requires a valid Ticket Granting Ticket (TGT) in your credential cache.

Required Parameters:

1. host: The IBM i host you are connecting to
2. realm: Your Kerberos realm
3. realm_user: Your Kerberos username
4. krb5_path: Path to your krb5.conf configuration file

Optional Parameters:

1. ticket_cache: Path to your ticket cache (if not default)
2. krb5_mech: The Kerberos 5 mechanism to use (if not default)

from mapepire_python import DaemonServer
from mapepire_python.authentication.kerberosTokenProvider import KerberosTokenProvider

token_provider = KerberosTokenProvider(
                  realm="REALM",
                  realm_user="REALM_USER",
                  host="SERVER",
                  krb5_path="KRB5_PATH"
                )

creds = DaemonServer(
    host="SERVER",
    password=token_provider,
    user="USER",
    port="PORT",
)

job = SQLJob(creds)

2. Passing the connection details as a dictionary

You can also use a dictionary to configure the connection details:

from mapepire_python import SQLJob

creds = {
  "host": "SERVER",
  "port": "port",
  "user": "USER",
  "password": "PASSWORD",
}

job = SQLJob(creds)

this is a convenient way to pass the connection details to the mapepire server.

3. Using a config file (.ini) to store the connection details

If you use a config file (.ini), you can pass the path to the file as an argument:

First create a mapepire.ini file in the root of your project with the following required fields:

[mapepire]
SERVER="SERVER"
PORT="PORT"
USER="USER"
PASSWORD="PASSWORD"

Then you can create a SQLJob object by passing the path to the .ini file which will handle the connection details

from mapepire_python import SQLJob

job = SQLJob("./mapepire.ini", section="mapepire")

The section argument is optional and allows you to specify a specific section in the .ini file where the connection details are stored. This allows you to store multiple connection details to different systems in the same file. If you do not specify a section, the first section in the file will be used.

4. Using environment variables

You can configure connection credentials through environment variables, avoiding the need to pass them explicitly in code:

Variable	Required	Default	Description
MAPEPIRE_HOST	Yes	—	Server hostname
MAPEPIRE_USER	Yes	—	Database user
MAPEPIRE_PASSWORD	Yes	—	Database password
MAPEPIRE_PORT	No	8076	Server port
MAPEPIRE_CA_PATH	No	—	Path to CA certificate file

Set the variables in your shell:

export MAPEPIRE_HOST=myibmi.example.com
export MAPEPIRE_USER=myuser
export MAPEPIRE_PASSWORD=mypassword

Then connect() and async_connect() can be called with no arguments:

from mapepire_python import connect

with connect() as conn:
    with conn.execute("select * from sample.employee") as cursor:
        print(cursor.fetchone())

You can also create a DaemonServer directly from env vars:

from mapepire_python import DaemonServer, SQLJob

creds = DaemonServer.from_env()
job = SQLJob(creds)

TLS Configuration

Server certificate verification (ssl.CERT_REQUIRED) is enabled by default. To disable certificate verification, set the ignoreUnauthorized field to True in the connection details.

get the server certificate:

from mapepire_python.data_types import DaemonServer
from mapepire_python.ssl import get_certificate

creds = DaemonServer(host=server, port=port, user=user, password=password)
cert = get_certificate(creds)
print(cert)

Logging

mapepire-python emits structured logs through the standard library logging module. Following library best practice, it does not configure any handlers itself, so logging is silent until your application opts in. Each module logs under the mapepire_python namespace, so you can enable and filter logs as needed:

import logging

# Enable INFO-level logs from mapepire-python
logging.basicConfig(level=logging.INFO)
logging.getLogger("mapepire_python").setLevel(logging.DEBUG)

Usage

Depending on your setup and use case, you can choose the most convenient way to configure the connection details. The following usage examples are compatible with all three connection options detailed above. For simplicity, we assume there is a mapepire.ini file in the root of the project with the connection details.

There are four main ways to run queries using mapepire-python:

1. Using the SQLJob object to run queries synchronously
2. Using the PoolJob object to run queries asynchronously
3. Using the Pool object to run queries "concurrently"
4. Using PEP 249 Implementation

1. Using the SQLJob object to run queries synchronously

from mapepire_python import SQLJob

with SQLJob("./mapepire.ini") as sql_job:
    with sql_job.query("select * from sample.employee") as query:
        result = query.run(rows_to_fetch=1)
        print(result)

Here is the output from the script above:

{
  "id":"query3",
  "has_results":true,
  "update_count":-1,
  "metadata":{
    "column_count":14,
    "job":"330955/QUSER/QZDASOINIT",
    "columns":[
      {
        "name":"EMPNO",
        "type":"CHAR",
        "display_size":6,
        "label":"EMPNO"
      },
      {
        "name":"FIRSTNME",
        "type":"VARCHAR",
        "display_size":12,
        "label":"FIRSTNME"
      },
      {
        "name":"MIDINIT",
        "type":"CHAR",
        "display_size":1,
        "label":"MIDINIT"
      },
      {
        "name":"LASTNAME",
        "type":"VARCHAR",
        "display_size":15,
        "label":"LASTNAME"
      },
      {
        "name":"WORKDEPT",
        "type":"CHAR",
        "display_size":3,
        "label":"WORKDEPT"
      },
      {
        "name":"PHONENO",
        "type":"CHAR",
        "display_size":4,
        "label":"PHONENO"
      },
      {
        "name":"HIREDATE",
        "type":"DATE",
        "display_size":10,
        "label":"HIREDATE"
      },
      {
        "name":"JOB",
        "type":"CHAR",
        "display_size":8,
        "label":"JOB"
      },
      {
        "name":"EDLEVEL",
        "type":"SMALLINT",
        "display_size":6,
        "label":"EDLEVEL"
      },
      {
        "name":"SEX",
        "type":"CHAR",
        "display_size":1,
        "label":"SEX"
      },
      {
        "name":"BIRTHDATE",
        "type":"DATE",
        "display_size":10,
        "label":"BIRTHDATE"
      },
      {
        "name":"SALARY",
        "type":"DECIMAL",
        "display_size":11,
        "label":"SALARY"
      },
      {
        "name":"BONUS",
        "type":"DECIMAL",
        "display_size":11,
        "label":"BONUS"
      },
      {
        "name":"COMM",
        "type":"DECIMAL",
        "display_size":11,
        "label":"COMM"
      }
    ]
  },
  "data":[
    {
      "EMPNO":"000010",
      "FIRSTNME":"CHRISTINE",
      "MIDINIT":"I",
      "LASTNAME":"HAAS",
      "WORKDEPT":"A00",
      "PHONENO":"3978",
      "HIREDATE":"01/01/65",
      "JOB":"PRES",
      "EDLEVEL":18,
      "SEX":"F",
      "BIRTHDATE":"None",
      "SALARY":52750.0,
      "BONUS":1000.0,
      "COMM":4220.0
    }
  ],
  "is_done":false,
  "success":true
}

The results object is a JSON object that contains the metadata and data from the query. Here are the different fields returned:

• id field contains the query ID
• has_results field indicates whether the query returned any results
• update_count field indicates the number of rows updated by the query (-1 if the query did not update any rows)
• metadata field contains information about the columns returned by the query
• data field contains the results of the query
• is_done field indicates whether the query has finished executing
• success field indicates whether the query was successful.

In the ouput above, the query was successful and returned one row of data.

Query and run

To create and run a query in a single step, use the query_and_run method:

from mapepire_python import SQLJob

with SQLJob("./mapepire.ini") as sql_job:
    # query automatically closed after running
    results = sql_job.query_and_run("select * from sample.employee", rows_to_fetch=1)
    print(result)

2. Using the PoolJob object to run queries asynchronously

The PoolJob object can be used to create and run queries asynchronously:

import asyncio
from mapepire_python import PoolJob

async def main():
    async with PoolJob("./mapepire.ini") as pool_job:
        async with pool_job.query('select * from sample.employee') as query:
          res = await query.run(rows_to_fetch=1)

if __name__ == '__main__':
    asyncio.run(main())

To run a create and run a query asynchronously in a single step, use the query_and_run method:

import asyncio
from mapepire_python import PoolJob

async def main():
    async with PoolJob("./mapepire.ini") as pool_job:
        res = await pool_job.query_and_run("select * from sample.employee", rows_to_fetch=1)
        print(res)

if __name__ == '__main__':
    asyncio.run(main())

3. Using the Pool object to run queries "concurrently"

The Pool object can be used to create a pool of PoolJob objects to run queries concurrently.

import asyncio
from mapepire_python import Pool, PoolOptions

async def main():
    async with Pool(
        options=PoolOptions(
            creds="./mapepire.ini",
            opts=None,
            max_size=5,
            starting_size=3
        )
    ) as pool:
      job_names = []
      resultsA = await asyncio.gather(
          pool.execute('values (job_name)'),
          pool.execute('values (job_name)'),
          pool.execute('values (job_name)')
      )
      job_names = [res['data'][0]['00001'] for res in resultsA]

      print(job_names)


if __name__ == '__main__':
    asyncio.run(main())

This script will create a pool of 3 PoolJob objects and run the query values (job_name) concurrently. The results will be printed to the console.

['004460/QUSER/QZDASOINIT', '005096/QUSER/QZDASOINIT', '005319/QUSER/QZDASOINIT']

4. Using PEP 249 Implementation

PEP 249 is the Python Database API Specification v2.0. The mapepire-python client provides a PEP 249 implementation that allows you to use the Connection and Cursor objects to interact with the Mapepire server. Like the examples above, we can pass the mapepire.ini file to the connect function to create a connection to the server:

from mapepire_python import connect

with connect("./mapepire.ini") as conn:
    with conn.execute("select * from sample.employee") as cursor:
        result = cursor.fetchone()
        print(result)

fetchmany() and fetchall() methods

The Cursor object provides the fetchmany() and fetchall() methods to fetch multiple rows from the result set:

with connect("./mapepire.ini") as conn:
    with conn.execute("select * from sample.employee") as cursor:
        results = cursor.fetchmany(size=2)
        print(results)

---

with connect("./mapepire.ini") as conn:
    with conn.execute("select * from sample.employee") as cursor:
        results = cursor.fetchall()
        print(results)

PEP 249 Asynchronous Implementation

The PEP 249 implementation provides a native async interface backed by non-blocking WebSocket I/O — no thread delegation. Use async_connect from the top-level package, or connect from mapepire_python.asyncio:

import asyncio
from mapepire_python import async_connect

async def main():
    async with async_connect("./mapepire.ini") as conn:
        async with await conn.execute("select * from sample.employee") as cursor:
            result = await cursor.fetchone()
            print(result)

if __name__ == '__main__':
    asyncio.run(main())

Fetch multiple rows at once with fetchmany or drain the full result set with fetchall:

async def main():
    async with async_connect("./mapepire.ini") as conn:
        async with await conn.execute("select * from sample.employee") as cursor:
            page = await cursor.fetchmany(10)   # first 10 rows
            rest = await cursor.fetchall()       # remaining rows

if __name__ == '__main__':
    asyncio.run(main())

Stream rows one at a time using async for:

async def main():
    async with async_connect("./mapepire.ini") as conn:
        async for row in await conn.execute("select * from sample.employee"):
            print(row)

if __name__ == '__main__':
    asyncio.run(main())

Use AsyncSQLJob directly for lower-level access without a pool:

from mapepire_python.client.async_sql_job import AsyncSQLJob

async def main():
    async with AsyncSQLJob("./mapepire.ini") as job:
        result = await job.query_and_run("select * from sample.employee")
        print(result.data)

if __name__ == '__main__':
    asyncio.run(main())

Development Setup

This guide provides instructions for setting up a Python virtual environment using either venv or conda.

Setup python virtual environment with pip and venv

• Create and activate virtual environment
• Prepare pip
• Install packages from requirements-dev.txt

Create a new virtual environment

Note: This applies to supported versions of Python 3.10 and higher

navigate to the project's directory and run the following command. This will create a new virtual environment in a local folder named .venv

cd mapepire-python/

Unix/macOS

python3 -m venv .venv

Windows

py -m venv .venv

The second argument is the location of the virtual environment, which will create a the virtual environment in the mapepire-python project root directory: mapepire-python/.venv

Activate the virtual environment:

before installing the project dependencies, activate the virtual environment to put the environment-specific python and pip executables into your shell's PATH

Unix.macOS

source .venv/bin/activate

Windows

.venv\Scripts\activate

Confirm the virtual environment is activated, check the location of the Python interpreter:

Unix/macOS

which python

Windows

where python

Expected output should be:

.venv/bin/python     # Unix/macOS
.venv\Scripts\python # Windows

To deactivate the virtual environment, run:

deactivate

from the mapepire-python project directory

Prepare pip

Make sure pip is up to date:

Unix/macOS

python3 -m pip install --upgrade pip
python3 -m pip --version

Windows

py -m pip install --upgrade pip
py -m pip --version

Install Dependencies using requirements-dev.txt

Run the following to install the project dependencies:

Unix/macOS

python3 -m pip install -r requirements-dev.txt

Windows

py -m pip install -r requirements-dev.txt

Setup Python virtual environment with Conda

First, install Conda if you haven't already by following the instructions in this guide. There are installers for macOS/Windows and Linux. I recommend the following installers for this project:

• Miniconda
  • Miniconda is a minimal installer provided by Anaconda.
• Anaconda
  • Anaconda Distribution is a full featured installer that comes with a suite of packages for data science, as well as Anaconda Navigator, a GUI application for working with conda environments.

Create an environment from an environment-dev.yml file

In a terminal, navigate to the mapepire-python project directory and run the following command:

cd mapepire-python/

conda env create -f environment-dev.yml

The conda env create command will create a python environment called mapepire-dev.

1. Activate the new environment:

conda activate mapepire-dev

2. Verify the new environment was installed:

conda env list

You can also use conda info --envs.

To deactivate, call:

conda deactivate

Run local test suite

The suite is split in two:

• tests/unit/ — offline unit tests. They mock the WebSocket transport and need no IBM i server or credentials, so they run anywhere (including CI on forks).
• tests/integration/ — live-server tests. They connect to a real Mapepire server and require credentials.

Unit tests (no server required)

# activate python development environment first

pytest tests/unit/

Integration tests (server required)

First, create a pytest.ini file in the tests/integration directory:

tests/integration/pytest.ini

[pytest]
env =
    VITE_SERVER=IBMI_SERVER
    VITE_DB_USER=USER
    VITE_DB_PASS=PASS

Then run the integration suite from the mapepire-python directory:

# activate python development environment first

pytest tests/integration/

To run everything (unit + integration), just point pytest at tests/:

pytest tests/