python pandas写入excel

lanjianhua / 2024-10-21 / 原文

# -- coding: utf-8 --
import datetime

import pandas as pd
from common_tool import get_ip_area
from db.mysqlConnection import MyPymysqlPool
from db_config import data_report_db


sex_dict = {
    "-1": "未知",
    "0": "女",
    "1": "男",
}

type_dict = {
    "1": "web",
    "2": "android",
    "3": "ios",
    "6": "微博",
    "7": "微信",
    "8": "知乎",
    "9": "新浪博客",
    "10": "百度小程序",
    "11": "微信小程序",
    "12": "抖音",
}


def get_nwe_user():
    mysql_db = MyPymysqlPool(data_report_db)
    try:
        # 读取数据库数据
        _sql = """
                SELECT id, mobile as '手机号', email as '邮箱', nick_name as '名称', sex as '性别', ip, type as '注册方式', 
                deviceInfo as '设备信息', create_time as '注册时间' FROM biu.`user`
                WHERE STATUS=1 AND create_time>='2024-10-15 20:00:00'"""

        # print(test_sql)
        _datas = mysql_db.getAll(_sql)
        mysql_db.end()  # 提交事务，避免阻塞
        
        # 数据处理
        for _ in _datas:
            area = get_ip_area.get_ip_area_name(_['ip'])
            _["省区"] = area.get("province_name_cn", "未知") if area else "未知"
            _["性别"] = sex_dict[str(_["性别"])]
            _["注册方式"] = type_dict[str(_["注册方式"])]
            _["注册时间"] = datetime.datetime.strftime(_["注册时间"], "%Y-%m-%d %H:%M:%S")

        # pandas将数据写入excel
        file_path = r'G:\ljh\info\app_nwe_users_202410152000.xlsx'
        # df = pd.read_excel(file_path, sheet_name="Sheet1")
        df = pd.DataFrame(_datas)  # 将数据通过pandas格式化成数据表
        df.to_excel(file_path)  # 写入excel

    except Exception as e:
        print(e)
        _ = e
    finally:
        mysql_db.dispose()


if __name__ == '__main__':
    get_nwe_user()