pd | mysql 简单操作

# 当前解释器 Anaconda3-Python 3.9(D:\soft\Anaconda3\python.exe)
import numpy as np
import pandas as pd
# SQLAlchemy是Python编程语言下的一款开源软件 提供了SQL工具包及对象关系映射 (ORM) 工具
from sqlalchemy import create_engine


def print_data(o):
    print(o.shape, o.size, tuple(o.index), tuple(o.columns))


'''
CREATE DATABASE `test` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci';
'''

conf = dict(host='', user='root', password='', database='test', charset='utf8mb4')

conn = create_engine('mysql+pymysql://{user}:{password}@{host}/{database}?charset={charset}'.format(**conf))

print(conn)  # 如果数据库不存在会报错, 如果不能连接数据库也会报错

# 查看所有数据库
database_items = pd.read_sql('SHOW DATABASES', conn)  # <class 'pandas.core.frame.DataFrame'>
print_data(database_items)

# [print(_) for _ in database_items]  # _ 列字段
# [print(type(_)) for _ in database_items.iloc]  # _: <class 'pandas.core.series.Series'> 每行数据集
# [print(_[0], _['Database'], _.Database) for _ in database_items.iloc]  # 三种遍历方式均可

# 查看 MYSQL 编码
# 注意SQL: SHOW VARIABLES LIKE '%char%'; | 由于SQL内具有% 需要使用 params设置 | %(like)s 括号内是参数名 对应键 使用字符串s
# https://blog.csdn.net/qq_35318838/article/details/101056057
char_items = pd.read_sql("SHOW VARIABLES LIKE %(like)s", conn, params=dict(like='%char%'))
print_data(char_items)
char_items = {_.Variable_name: _.Value for _ in char_items.iloc}  # 将每行数据转为字典
[print(_) for _ in char_items.items()]

# 多条数据传入到MYSQL
name_items = ['刘一', '陈二', '张三', '李四', '王五', '赵六', '孙七', '周八', '吴九', '郑十']
score_items = {90: 'A', 75: 'B', 60: 'C'}  # 一般百分制：90以上为A，75到89为B，60到74为C，60以下为D


def apply_func(score):
    for _ in score_items:
        if score >= _:
            return score_items.get(_)

    return 'D'


df = pd.DataFrame(dict(sex=np.random.randint(0, 2, 10), name=name_items, score=np.random.randint(0, 100 + 1, 10)), )
df.sex = df.sex.map({0: '男', 1: '女'})
df['score_qcut'] = pd.qcut(df.score, q=4, labels=tuple('DCBA'))  # 按成绩高低分 DCBA个等级 这是根据实际数值而非评分标准
df['score_level'] = df.score.apply(apply_func)

# 数据可以直接通过文件保存到mysql 无需手动建表
# index字段 使用 bigint
# 字符串字段 使用 text
# 整型字段 使用 int
# 无主键
# 重新执行 没有设置 if_exists 报错: Table 't0001' already exists.
# 重新执行设置 if_exists=replace(删除原数据并重新写入)/append(向后追加)
table = 't0001'

# df.to_sql(table, conn, if_exists='replace')  # 无返回 | 直接创建数据表, 并写入数据 | index为默认索引字段 0开始
df.to_sql(table, conn, if_exists='append')


df_read = pd.read_sql(f'SELECT * FROM `{table}`', conn, index_col='index')
# [print(dict(_)) for _ in df_read.iloc]  # 读取数据并输出

# 执行sql
df_exec = conn.execute(f'DELETE FROM `{table}`')  # 删除数据

count_items = pd.read_sql(F'SELECT COUNT(*) FROM `{table}`', conn)
count_items = count_items['COUNT(*)'][0]  # 先列后行

print(count_items)  # 一般返回为空 pd 好像不能返回执行了多少条数据