Building InterSystems IRIS Applications with Familiar Python Tools

from sqlalchemy import create_engine

# Replace with your credentials and connection information
username = "_SYSTEM"
password = "SYS"
namespace = "USER"
DATABASE_URL = f"iris//{username}:{password}@localhost:1972/{namespace}"

engine = create_engine(DATABASE_URL, echo=True)	# Set echo=True to see the SQL queries being excuted in the command line

import streamlit as st

st.set_page_config(page_title="IRIS Data Explorer")
st.title("InterSystems IRIS Data Explorer")

st.header("Run SQL Query")

# Create a text area where users can input to input their SQL query
# The second argument is the default query shown in the box
query = st.text_area("Enter SQL query:", "SELECT TOP 10 * FROM Sample.Person")

import pandas as pd

# Create a button labeled "Execute Query"
# When clicked, the code inside the if-block will run
if st.button("Execute Query"):
    try:
        # Open a connection to the InterSystems IRIS database using SQLAlchemy
        with engine.connect() as conn:
            # Use pandas to execute the SQL query and load the result into a pandas DataFrame
            df = pd.read_sql(query, conn)
        
        st.success("Query executed successfully")
        
        # Save df in session_state
        st.session_state['df'] = df
    
    except Exception as e:
        st.error(f"Error: {e}")

if 'df' in st.session_state:
    df = st.session_state['df']
    
    # Display the table in your Streamlit app
    st.dataframe(df)

if 'df' in st.session_state:
    csv = st.session_state['df'].to_csv(index=False).encode("utf-8")
    
    st.download_button(
        label="Download CSV",
        data=csv,
        file_name="results.csv"
        mime="text/csv"
    )

# Initializing Streamlit app
import streamlit as st

import pandas as pd

# Import Plotly Express for interactive charting
import plotly.express as px

# SQLAlchemy Connection to InterSystems IRIS
from sqlalchemy import create_engine

# Connect to IRIS
username = "superuser"
password = "SYS"
namespace = "USER"
DATABASE_URL = f"iris://{username}:{password}@localhost:1972/{namespace}"

engine = create_engine(DATABASE_URL, echo=True)

st.set_page_config(page_title="IRIS Data Explorer")
st.title("InterSystems IRIS Data Explorer")

st.header("Run SQL Query")

# Create a text area where users can input their SQL query
# The second argument is the default query shown in the box
query = st.text_area("Enter SQL query:", "SELECT TOP 10 * FROM Sample.Person")

# Create a button labeled "Execute Query"
# When clicked, the code inside the if-block will run
if st.button("Execute Query"):
    try:
        # Open a connection to the IRIS database using SQLAlchemy
        with engine.connect() as conn:
            # Use pandas to execute the SQL query and load the result into a DataFrame
            df = pd.read_sql(query, conn)

        st.success("Query executed successfully!")
        
        # Display the resulting DataFrame as an interactive table in the app
        st.dataframe(df)

        # Save df in session_state
        st.session_state['df'] = df

    except Exception as e:
        st.error(f"Error: {e}")

if 'df' in st.session_state:
    csv = st.session_state['df'].to_csv(index=False).encode("utf-8")

    st.download_button(
        label="Download CSV",
        data=csv,
        file_name="results.csv",
        mime="text/csv"
    )

# Check if a DataFrame from previous query exists
if 'df' in st.session_state:
    df = st.session_state['df']  # Get the stored DataFrame

    # Identify numeric columns for plotting
    numeric_cols = df.select_dtypes(include='number').columns.tolist()

    # Warn the user if there's nothing numeric to chart
    if df.empty or not numeric_cols:
        st.info("No numeric data available for visualization.")

# Check if df is stored in session_state and visualize
if 'df' in st.session_state:
    df = st.session_state['df']

# Check if the query results DataFrame ('df') exists in Streamlit's session_state
if 'df' in st.session_state:
    df = st.session_state['df']  # Retrieve the DataFrame from session state

    # Extract only numeric columns from the DataFrame for plotting
    numeric_cols = df.select_dtypes(include='number').columns.tolist()

    # If no data is available or there are no numeric columns, notify the user
    if df.empty or not numeric_cols:
        st.info("No numeric data available for visualization.")
    else:
        # Display a subheader for the chart section
        st.subheader("Interactive Chart")

        # Allow users to select one or more numeric columns to plot
        # The default is the first numeric column
        cols = st.multiselect(
            "Select numeric columns to plot",
            options=numeric_cols,
            default=[numeric_cols[0]]
        )

        # Let users choose the type of chart to generate
        chart_type = st.selectbox(
            "Select chart type",
            ["Line", "Bar", "Area", "Scatter"]
        )

        # Only proceed if at least one column is selected
        if cols:
            fig = None  # Initialize the figure object

            # Generate the appropriate chart based on the selected type
            if chart_type == "Line":
                # Plot a line chart with the selected columns on the y-axis
                fig = px.line(df, y=cols, title="Line Chart of Selected Columns")

            elif chart_type == "Bar":
                # Plot a bar chart with the selected columns on the y-axis
                fig = px.bar(df, y=cols, title="Bar Chart of Selected Columns")

            elif chart_type == "Area":
                # Plot an area chart with the selected columns on the y-axis
                fig = px.area(df, y=cols, title="Area Chart of Selected Columns")

            elif chart_type == "Scatter":
                # Scatter plot requires at least 2 numeric columns
                if len(cols) >= 2:
                    # Use first column as x-axis and second as y-axis
                    fig = px.scatter(
                        df,
                        x=cols[0],
                        y=cols[1],
                        title=f"Scatter Plot: {cols[0]} vs {cols[1]}"
                    )
                else:
                    st.warning("Select at least 2 columns for Scatter plot")

            # If a valid figure was created, display it using Streamlit
            if fig:
                st.plotly_chart(fig, use_container_width=True)
                # use_container_width=True makes the chart responsive to app layout

st.subheader("Upload CSV to IRIS")

# File uploader for CSV files
uploaded_file = st.file_uploader("Choose a CSV file", type="csv")

if uploaded_file:
    # Read and preview the uploaded CSV
    csv_df = pd.read_csv(uploaded_file)
    st.dataframe(csv_df.head())

    # Input: full table name in format "Namespace.Table"
    full_table_name = st.text_input("Enter target IRIS table (e.g. Bank.Account)")

    if st.button("Insert Data"):
        try:
            # Split schema (namespace) and table name if dot notation is used
            if '.' in full_table_name:
                schema, table_name = full_table_name.split('.', 1)
            else:
                schema = None
                table_name = full_table_name

            # Attempt to insert data into existing table
            with engine.begin() as conn:
                csv_df.to_sql(
                    name=table_name,
                    con=conn,
                    if_exists='append',
                    index=False,
                    schema=schema,
                    method='multi'  # batch insert for IRIS
                )

            st.success(f"Successfully inserted data into '{full_table_name}'")

        except Exception as e:
            st.error(f"Insertion failed: {e}")

from flask import Flask, render_template

app = Flask(__name__)

@app.route("/")
def hello_world():
    return "<p>Hello World!</p>"

if __name__ == '__main__':
    app.run(debug=True)

from sqlalchemy import create_engine, text

# Replace with your credentials and connection information
username = "_SYSTEM"
password = "SYS"
namespace = "USER"
DATABASE_URL = f"iris://{username}:{password}@localhost:1972/{namespace}"

engine = create_engine(DATABASE_URL, echo=True)	# Set echo=True to see the SQL queries being excuted in the terminal

#app.py

from flask import Flask, render_template
from sqlalchemy import create_engine, text

# Replace with your credentials and connection information
username = "_SYSTEM"
password = "SYS"
namespace = "USER"
DATABASE_URL = f"iris://{username}:{password}@localhost:1972/{namespace}"

engine = create_engine(DATABASE_URL, echo=True)	# Set echo=True to see the SQL queries being excuted in the terminal

app = Flask(__name__)

# ---------- ROUTES ----------
@app.route("/")
def hello_world():
    # main index page
    return render_template("index.html")

@app.route("/table/<path:table_name>")
def show_table(table_name):
    # table_name could be "Schema.Table" or just "Table"
    try:
        # Validate and split schema/table if schema is provided
        if '.' in table_name:
            schema, table = table_name.split('.')
            full_table = f"{schema}.{table}"
        else:
            schema = None
            table = table_name
            full_table = table

        # Query to get data from the specified table
        query = f"SELECT * FROM {full_table} ORDER BY 1"

        with engine.connect() as conn:
            result = conn.execute(text(query))
            keys = result.keys()		# get column names
            rows = [dict(zip(keys, row)) for row in result]

        if not rows:
            return f"No data found in table {full_table}", 404
        
        # Extract columns dynamically from first row keys
        columns = [{"data": col, "title": col.capitalize()} for col in rows[0].keys()]

        return render_template("tables.html", my_data=rows, my_cols=columns)

    except Exception as e:
        return f"Error: {str(e)}", 500

<!-- index.html -->

<!DOCTYPE html>
<html>

<body>
    <h1>My First Heading</h1>
    <p>My First Paragraph</p>
</body>

<html>

<!-- tables.html -->

<!DOCTYPE html>
<html>

<head>
    <title>Display IRIS Data with DataTables</title>
    
    <!-- Bootstrap 5 for styling -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    
    <!-- jQuery (required by DataTables) -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>	

    <!-- DataTables + Bootstrap 5 integration -->
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.5/css/dataTables.bootstrap5.min.css">
    <script src="https://cdn.datatables.net/1.13.5/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.5/js/dataTables.bootstrap5.min.js"></script>
</head>

<body class="container py-4">

    <h2 class="mb-4">IRIS Data Table</h2>
    
    <!-- HTML table to be populated dynamically -->
    <table id="myTable" class="table table-bordered table-striped table-hover"></table>

    <script>
        $(document).ready(function () {
            // Convert server-passed JSON strings into JavaScript objects
            let my_data = JSON.parse('{{ my_data | tojson | safe }}');
            let my_cols = JSON.parse('{{ my_cols | tojson | safe }}');
            
            // Initialize DataTable with server data and column definitions
            $('#myTable').DataTable({
                data: my_data,
                columns: my_cols,
                responsive: true,
                lengthMenu: [5, 10, 25, 50],
                pageLength: 10,
                language: {
                    search: "_INPUT_",
                    searchPlaceholder: "Search records"
                }
            });
        });
    </script>

</body>

</html>