MySQL Streamlit Integration Guide
expense2.ipynb
File
I need to connect this to mysql workbench through vs code
and i need to visualize in streamlit this personal expense analysis project
give steps for this
To connect your personal expense analysis project to MySQL Workbench via VS Code and visualize it
using Streamlit, follow these steps:
1. Prepare the Environment
1. Install Required Software:
Install MySQL Server and MySQL Workbench.
Install Python and VS Code.
Ensure you have `pip` installed for Python package management.
2. Install Necessary Python Libraries: Run the following commands in your terminal to install the
required libraries:
bash
pip install mysql-connector-python
pip install pandas
pip install streamlit
2. Set Up the MySQL Database
1. Create a MySQL Database:
Open MySQL Workbench.
Create a database for your project using a query like:
sql
CREATE DATABASE expense_analysis;
Use this database to store your data.
2. Create Tables:
Printed using Save ChatGPT as PDF, powered by PDFCrowd HTML to PDF API. 1/3
Design and create tables to store your expense data. For example:
sql
CREATE TABLE expenses (
id INT AUTO_INCREMENT PRIMARY KEY,
date DATE,
category VARCHAR(255),
amount DECIMAL(10, 2),
description TEXT
);
3. Insert Data:
Load sample or actual data into the table using `INSERT` statements or Workbench's import
feature.
3. Connect to MySQL in VS Code
1. Write a Python Script to Connect to MySQL:
Use the `mysql-connector-python` library to establish a connection. Here’s an example:
python
import mysql.connector
# Connect to MySQL
connection = mysql.connector.connect(
host="localhost", # Update with your host
user="your_username", # Replace with your MySQL username
password="your_password", # Replace with your MySQL password
database="expense_analysis"
)
if connection.is_connected():
print("Connected to MySQL")
cursor = connection.cursor()
cursor.execute("SELECT * FROM expenses")
for row in cursor.fetchall():
print(row)
connection.close()
Save this script in VS Code and test it to ensure the connection works.
4. Visualize the Data in Streamlit
1. Create a Streamlit App:
Write a Python script for Streamlit. Example:
python
import streamlit as st
import pandas as pd
import mysql.connector
# Connect to MySQL
Printed using Save ChatGPT as PDF, powered by PDFCrowd HTML to PDF API. 2/3
connection = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="expense_analysis"
)
# Query the data
query = "SELECT * FROM expenses"
df = pd.read_sql(query, connection)
# Streamlit App
st.title("Personal Expense Analysis")
st.subheader("Expense Data")
st.dataframe(df)
st.subheader("Expense by Category")
category_data = df.groupby("category")["amount"].sum().reset_index()
st.bar_chart(category_data, x="category", y="amount")
connection.close()
2. Run the Streamlit App:
Save the script as `app.py`.
Run the app using:
bash
streamlit run app.py
3. Interact with the Dashboard:
Open the URL displayed in the terminal to interact with your expense visualization.
5. Iterate and Enhance
Add filters or advanced visualizations to your Streamlit app.
Refactor your database queries to include aggregations or trends.
Let me know if you need help with specific sections!
Printed using Save ChatGPT as PDF, powered by PDFCrowd HTML to PDF API. 3/3
