Presenting IRIS Tool and Data Manager: Integrating InterSystems IRIS with Python, Pandas, and Streamlit

Hi everyone! 👋
I’m excited to share the project I’ve submitted to the current InterSystems .Net, Java, Python, and JavaScript Contest — it’s called IRIStool and Data Manager, and you can find it on the InterSystems Open Exchange and on my GitHub page.

Overview

The project involves a comprehensive Python toolkit, IRIStool, that seamlessly integrates pandas DataFrames with InterSystems IRIS databases. It also includes a Streamlit-based web interface, the Data Manager, that lets you explore, visualize, and manage your IRIS data all without writing a single line of SQL.

This proof of concept showcases how InterSystems IRIS can easily connect with modern Python tools such as pandas and plotly, thanks to the IRIS Native Python SDK, enabling advanced data manipulation and visualization

The project consists of two main components:

• 🧠 IRIStool — A Python module that provides a pandas-like interface to interact with IRIS databases
• 📊 IRIS Data Manager — A Streamlit web app for intuitive data exploration, visualization, and management

IRIStool (Python Module)

The IRIStool class brings the flexibility of pandas to IRIS. It allows Python developers to interact with IRIS tables just as easily as with DataFrames.

Key Features

• DataFrame ↔ IRIS Table Conversion with automatic type inference
• CRUD operations and SQL view creation
• Schema exploration and metadata inspection
• Support for standard and HNSW vector indexes for semantic search
• Context manager support for safe, automatic connection handling

With IRIStool, you can move effortlessly between Python and IRIS.

IRIS Data Manager (Streamlit UI)

The IRIS Data Manager is a visual, no-code interface built with Streamlit, designed to make data management in IRIS accessible to everyone, from developers to analysts.

What You Can Do

• Configure and test database connections directly from the sidebar
• Upload and import CSV, Excel, or JSON files
• Explore tables and schemas visually
• Filter data, group results, and apply aggregations
• Generate interactive visualizations with plotly
• Query your data using AI-powered Text-to-SQL, backed by Ollama running locally — meaning your data never leaves your environment

Quickstart Example with IRIStool

Here’s a complete example showing how to go from a pandas DataFrame to a live InterSystems IRIS table, perform inserts and updates, run queries, and create a view for analytics, all using IRIStool.

import pandas as pd
from datetime import datetime
from utils.iristool import IRIStool

# Connect to InterSystems IRIS
with IRIStool(
    host="localhost",
    port=1972,
    namespace="USER",
    username="_SYSTEM",
    password="SYS"
) as iris:

    # Create initial data as a pandas DataFrame
    employees = pd.DataFrame([
        {"ID": 1, "Name": "Alice", "Age": 29, "Department": "IT", "HireDate": datetime(2021, 5, 10)},
        {"ID": 2, "Name": "Bob", "Age": 34, "Department": "HR", "HireDate": datetime(2019, 11, 3)},
        {"ID": 3, "Name": "Carol", "Age": 25, "Department": "Finance", "HireDate": datetime(2023, 2, 15)}
    ])

    # Convert the DataFrame into an IRIS table
    iris.df_to_table(
        df=employees,
        table_name="Employee",
        primary_key="ID",
        exist_ok=True,
        drop_if_exists=True
    )
    print("DataFrame successfully uploaded to IRIS")

    # Insert additional records
    iris.insert_many("Employee", [
        {"ID": 4, "Name": "Dave", "Age": 31, "Department": "R&D", "HireDate": datetime(2020, 7, 18)},
        {"ID": 5, "Name": "Eve", "Age": 28, "Department": "Marketing", "HireDate": datetime(2022, 3, 5)}
    ])
    print("New rows inserted")

    # Fetch and display all employees older than 28
    df = iris.fetch("""
        SELECT ID, Name, Department, Age, HireDate
        FROM Employee
        WHERE Age > ?
    """, [28])
    print("\nEmployees older than 28:\n", df)

    # Update a record (Bob moves from HR to Operations)
    iris.update(
        table_name="Employee",
        new_values={"Department": "Operations"},
        filters={"Name": "Bob"}
    )
    print("Updated Bob’s department to Operations")

    # Create a view showing the average age per department
    iris.create_view(
        view_name="AvgAgeByDept",
        sql="""
            SELECT Department, AVG(Age) AS AvgAge
            FROM Employee
            GROUP BY Department
        """
    )

    avg_age = iris.fetch("SELECT * FROM AvgAgeByDept")
    print("\n Average age by department:\n", avg_age)

Examples workflows of IRIS Data Manager UI

The IRIS Data Manager is built on top of IRIStool and provides a no-code interface for all stages of data handling, from ingestion to visualization and analysis. It’s the fastest way to explore and interact with your InterSystems IRIS data directly from the browser.

To start the app, run the following command from your project root:

streamlit run app.py

1. Import data

• Open the Upload Data tab
• Drag and drop a file (supported formats include .json, .csv, .xlsx, and .xls) to upload data
• Configure indices (standard or vector indexes are supported)
• Select table schema and name — by default, data will be stored in the SQLUser schema
• Optionally define a primary key
• Click “Import to IRIS” to upload and persist your dataset

The IRIS Data Manager automatically detects data types, creates the corresponding table in IRIS, and loads the data seamlessly.

In this example I'm importing the sleep disorders database, available on Kaggle:

2. Visualize data

• Navigate to Explore & Analyze tab
• Choose your table and schema
• View analytics of your data both for numerical and categorical columns

• Explore data interactively using Plotly charts

• Group and aggregate data directly from the interface — no SQL required

• In the Text to SQL tab, you can query your data just by asking questions in plain English.
  • Type questions like:

  • “Show the average sleep duration grouped by occupation and gender”

  • “What is the occupation with the best sleep quality?"

• The model (running locally via Ollama) translates it into SQL
• The query executes live on your IRIS instance, and the results are displayed

---

🔗 Try It Out

👉 GitHub Repository: IRIS Tool and Data Manager
👉 Vote for it in the InterSystems Developer Community Contest!