数据上传分析系统

import os
import pandas as pd
import mysql.connector
from flask import Flask, request, redirect, url_for, render_template
from datetime import datetime

app = Flask(__name__)
UPLOAD_FOLDER = 'uploads'
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

# 创建上传文件夹
if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER)


def process_excel(file_path):
    # 读取excel文件
    df = pd.read_excel(file_path)

    # 统计数据
    summary_row = df[df.iloc[:, 0].str.contains('汇总', na=False)]
    print("汇总行的数据:")
    print(summary_row)
    #获取指定数据
    C = summary_row.iloc[0, 18] 

    return C

def write_to_mysql(C):
    # 连接MySQL数据库
    conn = mysql.connector.connect(
        host='change-to-your-host',
        user='your-username',
        password='your-password',
        database='your-table'
    )
    cursor = conn.cursor()

    # 查询当前最大id
    cursor.execute('SELECT MAX(id) FROM `all-mt`')
    max_id = cursor.fetchone()[0]

    # 计算新记录的id值
    new_id = (max_id + 1) if max_id is not None else 1

    #获取当前时间
    current_date = datetime.now().date()

    #数据类型转换
    C=int(C)

    # 插入统计结果到数据库
    sql = f"INSERT INTO `1` (id, update_date, C) VALUES (%s, %s, %s)"
    values = (new_id, current_date, C)

    print("Executing SQL:", sql)
    print("With values:", values)
    cursor.execute(sql, values)

    # 提交事务
    conn.commit()

    # 关闭连接
    cursor.close()
    conn.close()


@app.route('/', methods=['GET', 'POST'])
def upload_file():
    if request.method == 'POST':
        # 检查是否选择了文件
        if 'file' not in request.files:
            return 'No file part'
        file = request.files['file']
        # 检查是否上传了文件
        if file.filename == '':
            return 'No selected file'
        
        if file:
            file_path = os.path.join(app.config['UPLOAD_FOLDER'], file.filename)
            file.save(file_path)

            # 处理Excel文件
            C = process_excel(file_path)

        # 写入MySQL数据库
        write_to_mysql(C)

        return f"数据已成功写入MySQL数据库"

    return '''
    <!doctype html>
    <title>数据分析系统</title>
    <h1>数据上传分析系统</h1>
    <form method=post enctype=multipart/form-data>
      选择文件: <input type=file name=file><br><br>
      <input type=submit value=Upload>
    </form>
    '''


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=6001)