Flask 11: Putting Everything together
If you work as a Data Analyst, and want to transition to a MLE, this is the most important post on the whole substack for *you*
We’ll be covering the following important topics below:
Connecting flask with SQL
Interactivity
Deployment
In the real world, data-driven applications often rely on SQL databases to store and retrieve structured information. Usually companies will have you either use PowerBI/Tableau/Flask/RShiny to build a dashboard. But, sometimes, if the company wants to train you to basically become a MLE, they’ll prefer you to use flask to make interactive graphs, and more. This project is basically what you’ll be doing non stop, if you work as a data analyst who uses Python, and is being slowly trained to become a MLE.
We’ll build a Flask application that connects to a SQL database, retrieves time-series data based on user input, and displays it as a line graph. Whether you're tracking financial trends, weather patterns, or website analytics, this guide will give you the foundation to build a flexible and interactive web app.
1 - Setting up your Environment
Grab the following libs for Python 3:
flask
pandas
flask-sqlalchemy
flask-mysql
plotly
numpy
For SQL, we’ll use MySQL. You can download the workbench (IDE): https://dev.mysql.com/downloads/workbench/
and, you can download the setup for the local server here: https://dev.mysql.com/downloads/mysql/
Be sure to write down the root (admin) username, and password somewhere, as we’ll need it when we sync Python to the server. I’m going with:
username: username_123
password: password_123
2 - Creating the SQL Database
Once you’ve got all of the tools you need, go ahead, and start up MySQL workbench, and have it point to your current (default) schema. Here, you can go ahead, and run the following SQL code, in order to populate your database with a table, and some data:
CREATE TABLE stocks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
company TEXT NOT NULL,
date DATE NOT NULL,
price REAL NOT NULL
);
INSERT INTO stocks (company, date, price) VALUES
('CompanyA', '2024-12-01', 120.5),
('CompanyA', '2024-12-02', 123.0),
('CompanyA', '2024-12-03', 125.3),
('CompanyA', '2024-12-04', 125.9),
('CompanyB', '2024-12-01', 98.0),
('CompanyB', '2024-12-02', 101.4),
('CompanyB', '2024-12-03', 104.2),
('CompanyB', '2024-12-04', 104.3);Here’s the output when you run a SELECT statement on it:
3 - Setting up the Flask UI
Our Flask app will:
Accept a company name as input.
Query the stocks table for time-series data based on the company name.
Render an interactive line graph displaying stock prices over time.
Whenever I’m building a dashboard/flask app, I always like to start from the UI side first, and this gives me an idea of how many different interactions I’m supposed to keep tabs on (all of the data pipelines that the app is expected to work with)
Here is the code for the templates/index.html file:
<!DOCTYPE html>
<html>
<head>
<title>Stock Prices</title>
<script src="https://cdn.plot.ly/plotly-latest.min.js"></script>
</head>
<body>
<h1>Stock Price Visualization</h1>
<form method="POST" action="/">
<label for="company">Enter Company Name:</label>
<input type="text" id="company" name="company" required>
<button type="submit">Get Data</button>
</form>
{% if error %}
<p style="color: red;">{{ error }}</p>
{% endif %}
{% if graph_html %}
<div>{{ graph_html|safe }}</div>
{% endif %}
</body>
</html>This HTML template creates a web page for visualizing stock prices. Users can input a company name into a form, which sends the data to a server via a POST request. If there's an error, it displays a message; if valid data is received, it renders a Plotly graph of the stock prices on the page.
Here’s a preview of the UI page.
4 - Setting up the server side logic
In the code below, I am using the following assumptions:
database name: database_123
username: username_123
password: password_123
port (for the app): 1234
from flask import Flask, request, render_template
from flask_sqlalchemy import SQLAlchemy
import plotly.express as px
app = Flask(__name__)
# Configure the database URI for MySQL
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username_123:password_123@localhost:3306/database_123'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
# Define the Stocks model
class Stock(db.Model):
id = db.Column(db.Integer, primary_key=True)
company = db.Column(db.String(50), nullable=False)
date = db.Column(db.Date, nullable=False)
price = db.Column(db.Float, nullable=False)
@app.route('/', methods=['GET', 'POST'])
def index():
if request.method == 'POST':
company = request.form.get('company')
# Query the database for the selected company's stock prices
stocks = Stock.query.filter_by(company=company).order_by(Stock.date).all()
if not stocks:
return render_template('index.html', error=f"No data found for {company}")
# Prepare data for the graph
dates = [stock.date for stock in stocks]
prices = [stock.price for stock in stocks]
graph = px.line(x=dates, y=prices, labels={'x': 'Date', 'y': 'Price'}, title=f'{company} Stock Prices')
graph_html = graph.to_html(full_html=False)
return render_template('index.html', graph_html=graph_html)
return render_template('index.html')
if __name__ == '__main__':
app.run(port=1234)
This Python Flask application connects to a MySQL database to manage stock price data. Users can submit a company name via a web form, and the app queries the database for matching stock prices. It then generates an interactive Plotly line chart of the company's stock prices and displays it on the web page. If no data is found, an error message is shown.
Here’s the app in action:
CompanyA:
CompanyB:
Gibberish:
5 - Deployment, and Test Run
The default for flask basically says only 1 user may be connected to this app at a time (if running on something like a windows server). Or, you might have some issues with other people not being able to connect to the app. To solve both of these issues, you’ll need to open up the respective ports in windows firewall, and run deployment.
Windows Firewall:
Open up windows firewall. Navigate to the inbound rules, and then click on new rule. Click Next next next, and then eventually open up the correct port for your app (1234 in our case).
Deployment:
To allow multiple users to connect to the app at the same time, we’ll use the waitress library. In your run app section make the following changes:
FROM:
if __name__ == '__main__':
app.run(port=1234)TO:
if __name__ == '__main__':
from waitress import serve
serve(app, host='0.0.0.0', port=1234)Congrats, you’ve now deployed your app, and made it so multiple users can connect to it at the same time.