用pandas和sqlalchemy查询数据库数据并分析数据

1、导入第三方库

import pandas as pd
from sqlalchemy import create_engine

2、使用sqlalchemy创建数据库连接

engine = create_engine('mysql+pymysql://admin:423B8BT7E@dev.csy2ig5j91.rds.cn-north-1.com.cn:3306/dev')

3、使用pandas执行sql获取

sql1 = f"""SELECT group_id,group_name FROM report_group WHERE user_id ={phone} AND deleted_at IS NULL"""
df1 = pd.read_sql_query(sql1, engine)
# 将bytes转换为str字符串
group_id = df1['group_id'][0].decode('utf-8')
sql2 = f"""SELECT ugm.note,up.nickname,up.gender,up.birthday,hr.created_at,hr.physical FROM 略"""
df2 = pd.read_sql_query(sql2, engine)

4、使用pandas分析和修改数据

# 将note列的空值转换为NA
df2['note'] = df2['note'].replace('', pd.NA)
# 去nickname列的值填充note列的NA的值
df2['note'] = df2['note'].fillna(df2['nickname'])
# 将df1的group_name添加到df2的第一列
df2.insert(0, 'group_name', df1['group_name'][0])

# 将字符串时间转换为datetime时间
df2['created_at'] = pd.to_datetime(df2['created_at'])
# 分割日期和时间列
df2['date'] = df2['created_at'].dt.strftime('%Y/%m/%d')
df2['time'] = df2['created_at'].dt.time
# 将出生日期列转换为日期时间类型
df2['birthday'] = pd.to_datetime(df2['birthday'])
# 计算年龄并添加到出生日期后面
current_year = datetime.now().year
df2['age'] = current_year - df2['birthday'].dt.year

# 修改数据
type_mapping = {'T001': '平和', 'T002': '气虚', 'T003': '气郁', 'T004': '湿热'}
gender_mapping = {1: '未知', 2: '男', 3: '女'}
# 匹配gender_mapping映射修改
df2['gender'] = df2['gender'].map(lambda x: gender_mapping.get(x, x))
# 将physical的数据通过逗号分割后逐个匹配type_mapping映射修改，之后再使用顿号合并
df2['physical'] = df2['physical'].map(lambda x: '、'.join([type_mapping.get(val, val) for val in x.split(',')]))
 # 将体质列分割成3个体质列
df2[['physical1', 'physical2', 'physical3']] = df2['physical'].str.split(',', expand=True)
df2['physical2'] = df2['physical2'].str.strip()
df2['physical3'] = df2['physical3'].str.strip()
# 删除三列
del df2['nickname'], df2['created_at'], df2['physical']
# 将列表重新排列
df2 = df2[['phone', 'note', 'gender', 'birthday', 'age', 'created_at', 'physical']]
# 修改列名
df2.columns = ["账号", "昵称", "性别", "生日", "年龄", "日期", "体质"]