mzinfo/V2/downdata.py

#!/usr/bin/python
# -*- coding=utf-8 -*-
# @Create Time:		2024-01-12 13:25:10
# @Last Modified time: 2024-02-02 13:32:43
import pymysql
import pandas as pd

class MYDATABASE:
    def __init__(self, db_name=None):
        '''连接数据库，创建游标'''
        config = dict(zip(['host', 'user', 'port', 'password'],
                          ['192.168.1.202', 'root', 13388, 'zh123456']))
        config.update(database=db_name)
        self.connection = pymysql.connect(**config)
        self.cursor = self.connection.cursor()

    def __del__(self):
        self.connection.close()

    def create_database(self, db_name: str):
        '''新建数据库'''
        sql = f'CREATE DATABASE IF NOT EXISTS {db_name};'
        self.cursor.execute(sql)

    def create_table(self, tbl_name: str):
        '''新建数据库'''
        sql = f'CREATE TABLE IF NOT EXISTS {tbl_name};'
        self.cursor.execute(sql)

    def drop_database(self, db_name: str):
        '''删除数据库'''
        sql = f'DROP DATABASE IF EXISTS {db_name};'
        self.cursor.execute(sql)

    def drop_table(self, tbl_name: str):
        '''删除数据库'''
        sql = f'DROP TABLE IF EXISTS {tbl_name};'
        self.cursor.execute(sql)

    def query(self, sql: str):
        '''以数据框形式返回查询据结果'''
        self.cursor.execute(sql)
        data = self.cursor.fetchall()  # 以元组形式返回查询数据
        header = [t[0] for t in self.cursor.description]
        df = pd.DataFrame(list(data), columns=header)  # pd.DataFrame 对列表具有更好的兼容性
        return df

    def show_databases(self):
        '''查看服务器上的所有数据库'''
        sql = 'SHOW DATABASE;'
        return self.query(sql)

    def select_database(self):
        '''查看当前数据库'''
        sql = 'SELECT DATABASE();'
        return self.query(sql)

    def show_tables(self):
        '''查看当前数据库中所有的表'''
        sql = 'SHOW TABLES;'
        return self.query(sql)

    def insert_table(self, sql):
        try:
            # 执行sql语句
            cursor.execute(sql)
            self.connection.commit()
        except Exception as e:
            # 发生错误时回滚
            self.connection.rollback()


# mydb = MYDATABASE(db_name="mzinfo")

# df = mydb.query("SELECT * FROM recoder;")

# print(df)

# from sqlalchemy import create_engine
# connect_info = 'mysql+pymysql://{}:{}@{}:{}?charset=utf8'
# engine = create_engine(connect_info)

user_list = [
# 'liguiwen@mz.gd.csg.cn',
# 'houhuiteng@mz.gd.csg.cn',
# 'lijie1013@mz.gd.csg.cn',
# 'zengjiyi@mz.gd.csg.cn',
# 'fuli@mz.gd.csg.cn',
# 'zengjunhao@mz.gd.csg.cn',
# 'houyue@mz.gd.csg.cn',
# 'longruifan@mz.gd.csg.cn',
# 'wangyongqiang@mz.gd.csg.cn',
# 'liuhuan0828@mz.gd.csg.cn',
# 'heweiting@mz.gd.csg.cn',
# 'ganwenfeng@mz.gd.csg.cn',
# 'chenweichang0607@mz.gd.csg.cn',
# 'caiwujiancha1@mz.gd.csg.cn',
# 'wenyongxian@mz.gd.csg.cn'
]


con = pymysql.connect(host='192.168.1.202', user='root', port=13388,  password='zh123456', database='mzinfo', charset='utf8', use_unicode=True)

for user in user_list:
    sql_cmd = f"SELECT record_Time, current_Url, user_Id, request_Method, event_Type, form_Data, target_Url, iframe_Url, button_Text FROM recoder WHERE user_Id = '{user}' ORDER BY record_Time LIMIT 1000;"

    # df = pd.read_sql(sql_cmd, con=engine, index_col="record_Time")

    df = pd.read_sql(sql_cmd, con)
    df['record_Time'] = pd.to_datetime(df['record_Time'])
    print(df.head)
    df.to_json(f"{user}_data.json", orient='records', lines=True, force_ascii=False)