Comprehensive Guide to using Streamlit with PostgreSQL

Using Streamlit with PostgreSQL: Integration and Example

Streamlit is an open-source framework for creating web apps with Python, particularly for data science and machine learning applications. Integrating Streamlit with PostgreSQL enables dynamic, interactive web applications that leverage a robust relational database for data storage and management. This guide explains how to connect Streamlit to PostgreSQL, execute queries, and display results.

Setup and Installation

Before starting, ensure the following prerequisites are met:

1. Python Installed: Ensure Python 3.7 or higher is installed.

2. Streamlit Installed: Install Streamlit using pip.

 
pip install streamlit

3. PostgreSQL Driver: Install the PostgreSQL driver for Python, psycopg2.

pip install psycopg2  

Syntax for Connecting Streamlit to PostgreSQL

Below is the basic syntax to connect Streamlit to a PostgreSQL database:

import psycopg2  
import streamlit as st  

# Establish a connection to PostgreSQL
conn = psycopg2.connect(
    dbname="your_db",
    user="your_user",
    password="your_password",
    host="localhost",
    port="5432"
)

# Create a cursor object
cur = conn.cursor()

# Execute a query
cur.execute("SELECT * FROM your_table;")

# Fetch results
results = cur.fetchall()

# Display results using Streamlit
st.write(results)

# Close the connection
cur.close()
conn.close()

Step-by-Step Example

1. Sample Database Setup

Create a table in PostgreSQL for demonstration:

Code:

CREATE TABLE sales_data (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    sales_amount NUMERIC,
    sale_date DATE
);

INSERT INTO sales_data (product_name, sales_amount, sale_date)
VALUES 
('Laptop', 1500, '2024-01-01'),
('Mouse', 25, '2024-01-02'),
('Keyboard', 45, '2024-01-03');

2. Streamlit Code Example

The following example demonstrates querying and displaying the sales_data table in a Streamlit app.

Code:

import streamlit as st  # Import Streamlit
import psycopg2  # Import psycopg2 for PostgreSQL connection

# Streamlit app title
st.title("Sales Data Dashboard")

# Sidebar inputs for PostgreSQL credentials
st.sidebar.header("Database Connection")
db_name = st.sidebar.text_input("Database Name", "your_db")
db_user = st.sidebar.text_input("Username", "your_user")
db_password = st.sidebar.text_input("Password", type="password")
db_host = st.sidebar.text_input("Host", "localhost")
db_port = st.sidebar.text_input("Port", "5432")

# Connect to PostgreSQL
try:
    conn = psycopg2.connect(
        dbname=db_name,
        user=db_user,
        password=db_password,
        host=db_host,
        port=db_port
    )
    st.success("Connected to the database!")

    # Query the database
    query = "SELECT * FROM sales_data;"
    cur = conn.cursor()
    cur.execute(query)
    rows = cur.fetchall()

    # Display data in Streamlit
    st.header("Sales Data")
    st.write("Displaying data from the sales_data table:")
    st.dataframe(rows)

    # Close the connection
    cur.close()
    conn.close()
except Exception as e:
    st.error(f"Error: {e}")

Explanation:

1. User Input for Credentials:

• Streamlit's st.sidebar is used to gather database credentials dynamically.

2. Connection Setup:

• psycopg2.connect establishes a connection to PostgreSQL using user-provided details.

3. Executing Queries:

• cur.execute runs the SQL query to fetch data from sales_data.

4. Displaying Data:

• st.dataframe renders the table data in a neat, scrollable format.

5. Error Handling:

• Exceptions are caught and displayed as error messages.

Common Issues and Solutions

1. Authentication Error:

• Verify that PostgreSQL allows connections from the host using pg_hba.conf.

2. Library Import Issues:

• Ensure streamlit and psycopg2 are installed in your Python environment.

3. UI Responsiveness:

• Use Streamlit's caching mechanism (@st.cache_data) to improve performance for repeated queries.

Advanced Tips

• Parameterized Queries: Always use placeholders (%s) to prevent SQL injection.
• Real-Time Updates: Use st.experimental_rerun for real-time data refresh.
• Charts Integration: Use st.bar_chart or st.line_chart for graphical data representation.

All PostgreSQL Questions, Answers, and Code Snippets Collection.