Building a Sales Forecasting Application with Python, Flask, and MySQL
This article demonstrates how to use Python's data‑analysis ecosystem (NumPy, pandas, statsmodels, etc.) together with Flask, MySQL, and ECharts to create a web‑based sales forecasting tool that automates data extraction, modeling, prediction, and visualisation.
Python is highlighted as a comprehensive data‑analysis platform offering libraries such as NumPy, pandas, SciPy, scikit‑learn, and statsmodels, which together can handle data extraction, cleaning, modeling, and visualization without switching languages.
The tutorial walks through a concrete sales‑forecasting example that aims to replace manual SKU‑by‑SKU predictions with an automated model.
Tools used include pandas for data manipulation, NumPy for scientific computing, MySQLdb for database connectivity, statsmodels for statistical modeling, pylab for plotting, and Flask as the web framework.
Flask installation involves creating forecasting.py and forecasting.html files in the Flask app directory. The Python file contains:
# -*- coding: utf-8 -*-
from app import app
from flask import render_template
@app.route('/forecasting/')
def forecasting(item=None):
return render_template("forecasting.html")The HTML template is a minimal page:
<!doctype html>
<title>Hello World</title>
Hello WorldRunning the app with python d:\pyflaskrun.py and visiting http://127.0.0.1:5000/ displays the template.
Database connection is established via:
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='123456',db='bi',charset='utf8')
str_sql = "select SaleMonth as Month,Sale from sale order by SaleMonth"
sale = sql.read_sql(str_sql, conn)Data is then processed: converting the month column to datetime, setting it as the index, creating a full date range, merging with the sales data, and converting the result to HTML for template rendering.
Forecasting uses a log‑transformed series and an autoregressive (AR) model from statsmodels:
rs = np.log(rs)
r = sm.tsa.AR(rs).fit(maxlag=lag, method='mle', disp=-1)
fcst_lg = r.predict(start, pre_end)
fcst = np.exp(fcst_lg)
fcst = DataFrame(fcst)
fcst.columns = ['fcst']
fcst.index.names = ['Month']
rs_out = pd.merge(sale, fcst, left_index=True, right_index=True, how='outer')
rs_fcst = rs_out[-4:-1].to_html()Results are visualized with ECharts by constructing a JSON‑like chart configuration string ( tmp ) that embeds categories, actual sales, and forecast values.
A dynamic formula image is generated from the AR model parameters using matplotlib/pylab, saved as a PNG, and referenced in the template.
Finally, the Flask template renders the chart, model statistics, and the forecast table using Jinja2 placeholders such as {{r.k_ar}} , {{r.nobs}} , and {{rs_fcst | safe}} .
The article concludes that while the example covers a single product and model, the approach can be scaled to batch‑process many products, automatically select optimal models, and schedule regular predictions.
Python Programming Learning Circle
A global community of Chinese Python developers offering technical articles, columns, original video tutorials, and problem sets. Topics include web full‑stack development, web scraping, data analysis, natural language processing, image processing, machine learning, automated testing, DevOps automation, and big data.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.