python数据库导出为excel（python处理json字符串）

最新推荐文章于 2024-07-21 22:27:18 发布

划水小将军

最新推荐文章于 2024-07-21 22:27:18 发布

阅读量72

点赞数 2

文章标签： python 数据库 excel

本文链接：https://blog.csdn.net/qq_44309969/article/details/138109917

版权

'''
@Author: Jeff.zheng
@Date : 2024/4/22
@Desc :  python数据库导出为excel
'''

# 导入PyMySQL库
import json
import time

import pandas as pd
import pymysql.cursors

if __name__ == '__main__':

    list = []

    # 创建连接
    connection = pymysql.connect(
        host='localhost',  # 数据库主机地址
        user='root',  # 数据库用户名
        password='123456',  # 数据库密码
        db='softide_cloud3',  # 数据库名
        cursorclass=pymysql.cursors.DictCursor  # 使用DictCursor以字典形式返回查询结果
    )

    try:
        with connection.cursor() as cursor:
            # 编写SQL查询语句
            sql_query = "SELECT DISTINCT a.id,a.col_user_id,a.col_date,a.col_device_id,a.col_phone ,b.col_diary,b.create_date FROM day_idea  a LEFT JOIN tb_sleep_diary b on a.id = b.id "

            # 执行查询
            cursor.execute(sql_query)

            # 获取查询结果
            result = cursor.fetchall()

            for row in result:
                try:
                    temp = {}
                    temp = json.loads(row['col_diary'])

                    temp['before_sleeping_habits'] = str(temp['before_sleeping_habits']).replace("sport", "运动") \
                        .replace(
                        "drink_wine", "饮酒").replace("drink_coffee", "喝咖啡").replace("take_medicine", "吃药").replace(
                        "midnight_snack", "宵夜").replace("game", "游戏").replace("other", "其他")

                    temp['sleeping_status'] = str(temp['sleeping_status']).replace("best", "非常好").replace("better",
                                                                                                          "良好").replace(
                        "good", "一般").replace("bad", "很差").replace("worse", "非常差")

                    temp['body_status'] = str(temp['body_status']).replace("fitness", "正常").replace("sleeplessness",
                                                                                                    "失眠").replace(
                        "catch_a_code", "感冒").replace("ffever", "发烧").replace("chest_distress", "胸闷").replace(
                        "flustered", "(心慌").replace("fatigue", "疲惫").replace("menstrual_period", "经期").replace(
                        "climacterium", "更年期").replace("other", "其他").replace("stress_big", "压力大")
                    # 移除多余元素
                    del row['col_diary']
                    merged_dict = {**row, **temp}  # 扩展运算
                    list.append(merged_dict)
                except Exception as e:
                    print("===============>" + str(e))
                    pass

            df = pd.DataFrame(list)
            # 设置表头
            header_rename_dict = {'id': '编号', 'col_user_id': '用户编号', 'col_date': '日期', 'col_device_id': '设备号',
                                  'col_phone': '电话', 'before_sleeping_habits': '睡前行为',
                                  'before_sleeping_habits_other': '其他睡前行为', 'body_status': '身体状态',
                                  'body_status_other': '其他身体状态', 'sleeping_status': '睡眠状态',
                                  'create_date': '创建时间'}
            df = df.rename(columns=header_rename_dict)
            fileName = '睡眠日记' + str(int(time.time())) + '.xlsx'
            df.to_excel(fileName, index=False)
    finally:
        # 关闭连接
        connection.close()