详细代码篇：python+mysql +h5实现基于的电影数据统计分析系统实战案例（二）

点击阅读原文：: 详细代码篇：python+mysql +h5实现基于的电影数据统计分析系统实战案例（二）！

https://mp.weixin.qq.com/s/h-w875AMJLeQ-NLdrDoEzg?token=910031714&lang=zh_CN

1、先按照目录结构创建对应的文件夹及文件
movies/
├── app.py # Flask应用主文件
├── static/
│ ├── css/
│ │ └── style.css # 样式文件
│ └── js/
│ └── charts.js # 图表相关JavaScript
├── templates/
│ └── index.html # 主页面模板
└── database/
└── database.sql # 数据库文件

2、创建app.py，详细代码：

from flask import Flask, render_template, jsonify
import pymysql
import json
from decimal import Decimal

app = Flask(__name__)

# Database configuration
DB_CONFIG = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': 'root',
    'db': 'movies_db',
    'charset': 'utf8mb4'
}

def get_db_connection():
    return pymysql.connect(**DB_CONFIG)

def decimal_to_float(obj):
    if isinstance(obj, Decimal):
        return float(obj)
    return obj

@app.route('/')
def home():
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT title, rating, type, country, year 
                FROM movies 
                ORDER BY rating DESC
            """)
            movies_list = cursor.fetchall()
            # Get total movies count
            cursor.execute("SELECT COUNT(*) FROM movies")
            total_movies = cursor.fetchone()[0]
            
            # Get average rating
            cursor.execute("SELECT AVG(rating) FROM movies")
            avg_rating = round(decimal_to_float(cursor.fetchone()[0]), 1)
            
            # Get most common country
            cursor.execute("""
                SELECT country, COUNT(*) as count 
                FROM movies 
                GROUP BY country 
                ORDER BY count DESC 
                LIMIT 1
            """)
            result = cursor.fetchone()
            top_country = result[0] if result else "未知"
            
            # Get movie count by type
            cursor.execute("""
                SELECT type, COUNT(*) as count 
                FROM movies 
                GROUP BY type
            """)
            type_stats = [(t[0], decimal_to_float(t[1])) for t in cursor.fetchall()]
            
            # Get ratings distribution
            cursor.execute("""
                SELECT rating, COUNT(*) as count 
                FROM movies 
                GROUP BY rating 
                ORDER BY rating
            """)
            rating_stats = [(decimal_to_float(r[0]), decimal_to_float(r[1])) for r in cursor.fetchall()]
            
    finally:
        conn.close()
    # return render_template('index.html',
    #                      movies_list=movies_list,  # 添加这一行
    #                      total_movies=total_movies,
    #                      avg_rating=avg_rating,
    #                      top_country=top_country,
    #                      type_stats=json.dumps(type_stats),
    #                      rating_stats=json.dumps(rating_stats))

    
    return render_template('index.html',
                         movies_list=movies_list,  # 添加这一行
                         total_movies=total_movies,
                         avg_rating=avg_rating,
                         top_country=top_country,
                         type_stats=json.dumps(type_stats),
                         rating_stats=json.dumps(rating_stats))

if __name__ == '__main__':
    app.run(debug=True, port=5000)

3、在css/ 创建style.css，详细代码：

body {
    margin: 0;
    padding: 0;
    display: flex;
    font-family: Arial, sans-serif;
}

.sidebar {
    width: 280px;
    background: #4169E1;
    color: white;
    padding: 20px;
    min-height: 100vh;
}

.main-content {
    flex: 1;
    padding: 20px;
}

.stats-cards {
    display: flex;
    gap: 20px;
    margin-bottom: 30px;
}

.card {
    background: white;
    padding: 20px;
    border-radius: 8px;
    box-shadow: 0 2px 4px rgba(0,0,0,0.1);
    flex: 1;
}

.chart {
    height: 400px;
    margin-bottom: 20px;
}
.movie-list {
    margin-top: 20px;
    padding: 10px;
    max-height: calc(100vh - 200px);
    overflow-y: auto;
}

.movie-items {
    margin-top: 10px;
}

.movie-item {
    background: rgba(255, 255, 255, 0.1);
    padding: 10px;
    margin-bottom: 10px;
    border-radius: 5px;
}

.movie-item h3 {
    margin: 0 0 5px 0;
    font-size: 16px;
}

.movie-item p {
    margin: 3px 0;
    font-size: 14px;
}

/* 修改侧边栏宽度 */
.sidebar {
    width: 300px;
    background: #4169E1;
    color: white;
    padding: 20px;
    min-height: 100vh;
    overflow-y: auto;
}

/* 修改滚动条样式 */
.movie-list::-webkit-scrollbar {
    width: 6px;
}

.movie-list::-webkit-scrollbar-track {
    background: rgba(255, 255, 255, 0.1);
}

.movie-list::-webkit-scrollbar-thumb {
    background: rgba(255, 255, 255, 0.3);
    border-radius: 3px;
}

/* 表格容器样式 */
.table-container {
    margin: 20px 0;
    padding: 20px;
    background: white;
    border-radius: 10px;
    box-shadow: 0 0 20px rgba(0, 0, 0, 0.1);
    width: calc(100% - 40px);
}

/* 表格基础样式 */
.movie-table {
    width: 100%;
    border-collapse: separate;
    border-spacing: 0;
    background: white;
    font-size: 15px;
}

/* 表头样式 */
.movie-table thead th {
    background: linear-gradient(45deg, #4e54c8, #8f94fb);
    color: white;
    padding: 15px 20px;
    font-weight: 600;
    text-align: left;
    border: 1px solid #4e54c8;
}

.movie-table thead th:first-child {
    border-top-left-radius: 8px;
}

.movie-table thead th:last-child {
    border-top-right-radius: 8px;
}

/* 表格单元格样式 */
.movie-table td {
    padding: 15px 20px;
    border: 1px solid #e0e0e0;
}

/* 表格行样式 */
.movie-table tbody tr {
    transition: all 0.3s ease;
}

.movie-table tbody tr:hover {
    background-color: #f8f9ff;
    transform: translateY(-2px);
    box-shadow: 0 4px 8px rgba(0, 0, 0, 0.1);
}

/* 奇数行背景色 */
.movie-table tbody tr:nth-child(odd) {
    background-color: #f8f9ff;
}

/* 评分列样式 */
.movie-table td:nth-child(2) {
    font-weight: bold;
    color: #4e54c8;
}

/* 类型列样式 */
.movie-table td:nth-child(3) {
    color: #6c5ce7;
}

/* 国家列样式 */
.movie-table td:nth-child(4) {
    color: #00b894;
}

/* 年份列样式 */
.movie-table td:nth-child(5) {
    color: #e17055;
}

/* 电影列表标题样式 */
.movie-list h2 {
    color: #2c3e50;
    margin: 20px 0;
    font-size: 28px;
    font-weight: 600;
    text-align: center;
    padding-bottom: 15px;
    border-bottom: 3px solid #4e54c8;
}

/* 确保表格在小屏幕上可以水平滚动 */
.table-container {
    overflow-x: auto;
    max-width: 100%;
}

/* 表格最后一行的圆角 */
.movie-table tbody tr:last-child td:first-child {
    border-bottom-left-radius: 8px;
}

.movie-table tbody tr:last-child td:last-child {
    border-bottom-right-radius: 8px;
}

/* 电影名称列样式 */
.movie-table td:first-child {
    font-weight: 500;
    color: #2d3436;
}

/* 整体列表容器样式 */
.movie-list {
    margin-left: 300px;
    margin-right: 20px;
    padding: 20px;
    background-color: #f5f6fa;
    min-height: 100vh;
}

4、 在js/创建 charts.js，详细代码：

// Initialize type distribution chart
const typeChart = echarts.init(document.getElementById('typeChart'));
const typeOption = {
    title: {
        text: '电影类型分布图',
        left: 'center'
    },
    tooltip: {
        trigger: 'item',
        formatter: '{b}: {c} 部 ({d}%)'
    },
    legend: {
        orient: 'vertical',
        left: 'left',
        top: 'middle'
    },
    series: [{
        name: '电影类型',
        type: 'pie',
        radius: '50%',
        data: typeStats.map(item => ({
            name: item[0] || '未知',
            value: item[1]
        })),
        emphasis: {
            itemStyle: {
                shadowBlur: 10,
                shadowOffsetX: 0,
                shadowColor: 'rgba(0, 0, 0, 0.5)'
            }
        }
    }]
};
typeChart.setOption(typeOption);

// Initialize rating distribution chart
const ratingChart = echarts.init(document.getElementById('ratingChart'));
const ratingOption = {
    title: {
        text: '电影评分分布图',
        left: 'center'
    },
    tooltip: {
        trigger: 'axis',
        formatter: '{b}分: {c}部电影'
    },
    grid: {
        left: '3%',
        right: '4%',
        bottom: '3%',
        containLabel: true
    },
    xAxis: {
        type: 'category',
        name: '评分',
        data: ratingStats.map(item => item[0] + '分'),
        axisLabel: {
            interval: 0
        }
    },
    yAxis: {
        type: 'value',
        name: '电影数量'
    },
    series: [{
        name: '评分分布',
        data: ratingStats.map(item => item[1]),
        type: 'line',
        smooth: true,
        label: {
            show: true,
            position: 'top',
            formatter: '{c}部'
        },
        areaStyle: {
            opacity: 0.3
        },
        itemStyle: {
            color: '#4169E1'
        }
    }]
};
ratingChart.setOption(ratingOption);

// 响应窗口大小变化
window.addEventListener('resize', function() {
    typeChart.resize();
    ratingChart.resize();
});

5、接着在templates/创建index.html ，详细代码：

<!DOCTYPE html>
<html>
<head>
    <title>电影统计系统</title>
    <meta charset="utf-8">
    <link rel="stylesheet" href="{{ url_for('static', filename='css/style.css') }}">
    <script src="https://cdn.jsdelivr.net/npm/echarts@5.2.2/dist/echarts.min.js"></script>
</head>
<body>
    <div class="sidebar">
        <h1>电影系统</h1>
        <nav>
            <ul>
                <li><a href="#" class="active">首页</a></li>
                <li><a href="#">评分分析</a></li>
                <li><a href="#">类型分析</a></li>
                <li><a href="#">国家分析</a></li>
                <li><a href="#">年份分析</a></li>
                <li><a href="#movie-list">电影列表</a></li>
                <li><a href="#">电影分析</a></li>
                <li><a href="#">数据统计</a></li>
                <li><a href="#">设置</a></li>
                <li><a href="#">帮助</a></li>
            </ul>
        </nav>
    </div>
        <!-- 添加电影列表 -->
        <div id="movie-list" class="movie-list" style="display: none;">
            <h2>电影列表</h2>
            <div class="table-container">
                <table class="movie-table">
                    <thead>
                        <tr>
                            <th>电影名称</th>
                            <th>评分</th>
                            <th>类型</th>
                            <th>国家</th>
                            <th>年份</th>
                        </tr>
                    </thead>
                    <tbody>
                        {% for movie in movies_list|sort(attribute='4', reverse=True) %}
                        <tr>
                            <td>{{ movie[0] }}</td>
                            <td>{{ movie[1] }}</td>
                            <td>{{ movie[2] }}</td>
                            <td>{{ movie[3] }}</td>
                            <td>{{ movie[4] }}</td>
                        </tr>
                        {% endfor %}
                    </tbody>
                </table>
            </div>
        </div>
    
        <div class="main-content">
            <div class="stats-cards">
                <div class="card">
                <h3>电影个数</h3>
                <p>{{ total_movies }}</p>
            </div>
            <div class="card">
                <h3>平均评分</h3>
                <p>{{ avg_rating }}</p>
            </div>
            <div class="card">
                <h3>主要国家/地区</h3>
                <p>{{ top_country }}</p>
            </div>
        </div>
        
        <div class="charts">
            <div id="typeChart" class="chart"></div>
            <div id="ratingChart" class="chart"></div>
        </div>
    </div>
    
    <script>
        const typeStats = {{ type_stats|safe }};
        const ratingStats = {{ rating_stats|safe }};

        // 添加导航链接点击事件处理
        document.addEventListener('DOMContentLoaded', function() {
            const navLinks = document.querySelectorAll('.sidebar nav a');
            const movieList = document.getElementById('movie-list');
            const mainContent = document.querySelector('.main-content');

            navLinks.forEach(link => {
                link.addEventListener('click', function(e) {
                    // 移除所有链接的active类
                    navLinks.forEach(l => l.classList.remove('active'));
                    
                    // 为当前点击的链接添加active类
                    this.classList.add('active');
                    
                    if (this.getAttribute('href') === '#movie-list') {
                        e.preventDefault();
                        movieList.style.display = 'block';
                        mainContent.style.display = 'none';
                    } else if (this.getAttribute('href') === '#') {
                        e.preventDefault();
                        movieList.style.display = 'none';
                        mainContent.style.display = 'block';
                    }
                });
            });
        });
    </script>
    <script src="{{ url_for('static', filename='js/charts.js') }}"></script>
</body>
</html>

6、在database/创建movies.db，详细代码：

首先在自己的本地创建数据库， ‘db’: ‘movies_db’,
‘charset’: ‘utf8mb4’ ，执行database.sql

CREATE DATABASE IF NOT EXISTS movies_db;
USE movies_db;

CREATE TABLE movies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    rating DECIMAL(3,1),
    type VARCHAR(50),
    country VARCHAR(50),
    year INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data with various types and ratings
CREATE DATABASE IF NOT EXISTS movies_db;
USE movies_db;

CREATE TABLE movies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    rating DECIMAL(3,1),
    type VARCHAR(50),
    country VARCHAR(50),
    year INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data with various types and ratings
INSERT INTO `movies` VALUES (1, '哪吒之魔童闹海', 9.7, '剧情', '中国', 2025, '2025-02-09 20:54:13');
INSERT INTO `movies` VALUES (2, '霸王别姬', 9.6, '剧情', '中国', 1993, '2025-02-09 20:54:13');
INSERT INTO `movies` VALUES (3, '阿甘正传', 9.5, '剧情', '美国', 1994, '2025-02-09 20:54:13');
INSERT INTO `movies` VALUES (4, '泰坦尼克号', 9.4, '爱情', '美国', 1997, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (5, '这个杀手不太冷', 9.4, '动作', '法国', 1994, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (6, '千与千寻', 9.3, '动画', '日本', 2001, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (7, '美丽人生', 9.5, '剧情', '意大利', 1997, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (8, '星际穿越', 9.3, '科幻', '美国', 2014, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (9, '盗梦空间', 9.3, '科幻', '美国', 2010, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (10, '忠犬八公的故事', 9.3, '剧情', '美国', 2009, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (11, '楚门的世界', 9.2, '剧情', '美国', 1998, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (12, '海上钢琴师', 9.2, '剧情', '意大利', 1998, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (13, '三傻大闹宝莱坞', 9.2, '喜剧', '印度', 2009, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (14, '机器人总动员', 9.3, '动画', '美国', 2008, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (15, '放牛班的春天', 9.2, '剧情', '法国', 2004, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (16, '大话西游之大圣娶亲', 9.2, '喜剧', '中国', 1995, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (17, '疯狂动物城', 9.1, '动画', '美国', 2016, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (18, '无间道', 9.1, '犯罪', '中国香港', 2002, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (19, '龙猫', 9.1, '动画', '日本', 1988, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (20, '教父', 9.3, '犯罪', '美国', 1972, '2025-02-09 21:16:22');
INSERT INTO `movies` VALUES (21, '唐探1900', 9.3, '喜剧', '中国', 2025, '2025-02-09 22:47:27');
INSERT INTO `movies` VALUES (22, '封神第二部:战火西岐', 9.5, '神话', '中国', 2025, '2025-02-09 22:49:00');