python 提取mysql数据库数据到excel（xlwings-pymysql）

python 提取mysql数据库数据到excel

import pymysql
import xlwings as xw


class Mysql(object):
    def __init__(self, database, user, passwrod, host="127.0.0.1", port=3306, charset="utf8"):
        self.conn = pymysql.connect(host=host, port=port, database=database, user=user, password=passwrod,
                                    charset=charset,autocommit = True)
        self.cs1 = self.conn.cursor()

    def get_table(self):
        self.cs1.execute("show tables")
        tables_name = self.cs1.fetchall()
        return [i[0] for i in tables_name]

    def get_data(self, table_name):
        self.cs1.execute("select * from {}".format(table_name))
        data = self.cs1.fetchall()
        # 获取表格的字段
        table_field = [i[0] for i in self.cs1.description]
        # 拼接表格字段和数据
        data = list(data)
        data.insert(0, tuple(table_field))
        return data

    def __del__(self):
        print("删除mysql链接")


class Excel(object):
    def __init__(self, excel_name):
        self.excel_name = excel_name
        self.app = xw.App(visible=True, add_book=False)
        self.wb = self.app.books.add()

    def write_excel(self, data, sheet_name):
        sht = self.wb.sheets.add(name=sheet_name)
        sht.range("A1").expand("table").value = data

    def save_excel(self):
        self.wb.save(self.excel_name)
        self.wb.close()
        self.app.quit()


if __name__ == '__main__':
    mysql = Mysql(database="t_03", user="root", passwrod="123456")
    table_names = mysql.get_table()
    m_excel = Excel(excel_name="t_03")
    for table_name in table_names:
        table_data = mysql.get_data(table_name=table_name)
        m_excel.write_excel(data=table_data, sheet_name=table_name)
    m_excel.save_excel()