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:
2. Connection Setup:
3. Executing Queries:
4. Displaying Data:
5. Error Handling:
Common Issues and Solutions
1. Authentication Error:
2. Library Import Issues:
3. UI Responsiveness:
Advanced Tips
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4