Python查询mysql返回序列化数据

---

一、前言

1. pymysql 查询返回的数据是 tuple 格式的，取的时候只能用下标取，有点麻烦，下面提供一种牺牲了一些查询速度但是可以直接返回序列化数据的方法
2. 当然 peewee 更好用，只是数据表更新后还需要更新 peewee 的模型
3. MysqlOp 是根据 pymysql 封装的简单查询方法，代码在这里 https://blog.csdn.net/weixin_43721000/article/details/124803077

---

二、实现

from config import Config as cf
from utils.MysqlOp import MySqlOp


def search(table_name):
    msq = MySqlOp(host=cf.MYSQL_HOST, port=cf.MYSQL_PORT, username=cf.MYSQL_USER, password=cf.MYSQL_PWD, db=cf.MYSQL_DB)

    # 执行sql
    data_field_list = msq.select(f'''show full columns from `{table_name}`;''')
    data_field_list = [data_field[0] for data_field in data_field_list]

    data_list = msq.select(f'''select {', '.join([f'`{data_field}`' for data_field in data_field_list])} from `{table_name}`;''')

    formatList = []
    for idx, data in enumerate(data_list):

        item = {}
        for k, field in enumerate(data):
            item[data_field_list[k]] = field

        formatList.append(item)
    return formatList


if __name__ == '__main__':
    table_name = 'data'
    # 序列化返回 data 表的全部数据
    json_dict = search(table_name=table_name)
    print(json_dict)
    # {{'id': 100361, 'detail_page': 'https://manybo...