data_analysis/GetItem/get_items_distribution_from_database.py

import sys
sys.path.append("..")
from Database.database_ import Database, DatabaseParam
import config
import os
import csv
from utils.tools import fmt_date


def add_stat_info():
    # 读取字段文件
    file_path = os.path.join(config.ALL_ITEMS_FILE_DIR, config.ALL_ITEMS_FILE_NAME)
    if not os.path.exists(file_path):
        raise RuntimeError('文件不存在 ', file_path)
    # 格式化起始结束日期
    start_date, end_date = fmt_date(
        start_year=config.CHECK_YEAR_START, end_year=config.CHECK_YEAR_END,start_month=config.CHECK_MONTH_START,end_month=config.CHECK_MONTH_END,start_day=config.CHECK_DAY_START,end_day=config.CHECK_DAY_END,
        start_hour=config.CHECK_HOUR_START, end_hour=config.CHECK_HOUR_END, start_minute=config.CHECK_MINUTE_START, end_minute=config.CHECK_MINUTE_END, start_second=config.CHECK_SECONDS_START, end_second=config.CHECK_SECONDS_END)
    print(f'统计起始日期：{start_date}；结束日期：{end_date}')
    # 连接数据库
    db_param = DatabaseParam(
        db_host=config.DB_HOST,
        db_user=config.DB_USER,
        db_password=config.DB_PASSWORD,
        db_name=config.DB_NAME,
        db_port=config.DB_PORT)
    # 数据库操作应在内部
    with Database(db_param) as db:   # 连接数据库
        with open(file_path, 'r', encoding='utf-8') as file_handler:
            csv_reader = csv.reader(file_handler)  # all_items文件读取器
            # 先读一行头
            if config.INCLUDE_HEAD:
                try:
                    head = next(csv_reader)
                except StopIteration:
                    print(f'{file_path} 文件为空')
            # 读标签
            try:
                label = next(csv_reader)
            except StopIteration:
                print(f'{file_path} 文件为空')
            # 添加字段
            label += ['记录数', '最小时间', '最大时间']
            stat_file_path = os.path.join(config.STATISTICS_FILE_DIR,config.STATISTICS_FILE_NAME)
            if os.path.exists(stat_file_path):
                print('清理历史文件：',stat_file_path)
                os.remove(stat_file_path)
            with open(stat_file_path, 'w', encoding='utf-8', newline='') as file_handler_stat:
                # 写入头
                csv.writer(file_handler_stat).writerow(label)
                # 获取数据库所有的字段
                sql = f"""SELECT DISTINCT item_name FROM {config.DB_SHEET_NAME} WHERE project_id = {config.PROJECT_ID}"""
                db.db_cursor.execute(sql)
                db_items = [item[0].strip() for item in db.db_cursor.fetchall()]
                # 逐行查询数据库并写入文件
                print('正在统计... ', end='')
                for num, row in enumerate(csv_reader):  # all_items文件读取器
                    data_code = row[1].strip()  # 表格中的数据编码
                    if not data_code in db_items: continue  # 如果字段不在数据库，那么就直接跳过
                    # 统计数量
                    sql = f"""SELECT COUNT(*) FROM {config.DB_SHEET_NAME} WHERE item_name = '{data_code}' AND project_id = '{config.PROJECT_ID}' AND h_time >= '{start_date}' AND h_time <= '{end_date}'"""
                    db.db_cursor.execute(sql)
                    query_count = db.db_cursor.fetchone()[0]
                    row.append(query_count)
                    # 统计最小时间
                    sql = f"""SELECT MIN(h_time) FROM {config.DB_SHEET_NAME} WHERE item_name = '{data_code}' AND project_id = '{config.PROJECT_ID}' AND h_time >= '{start_date}' AND h_time <= '{end_date}'"""
                    db.db_cursor.execute(sql)
                    query_min_date = db.db_cursor.fetchone()[0]
                    row.append(query_min_date)
                    # 统计最大时间
                    sql = f"""SELECT MAX(h_time) FROM {config.DB_SHEET_NAME} WHERE item_name = '{data_code}' AND project_id = '{config.PROJECT_ID}' AND h_time >= '{start_date}' AND h_time <= '{end_date}'"""
                    db.db_cursor.execute(sql)
                    query_max_date = db.db_cursor.fetchone()[0]
                    row.append(query_max_date)
                    csv.writer(file_handler_stat).writerow(row)
                    print('.', end='')
                print('\n统计完成，文件保存至:',stat_file_path)



if __name__ == '__main__':
    # 从数据库中统计各字段数据量
    add_stat_info()