Python导出数据库自动生成excl

# __Desc__ = 从数据库中导出数据到excel数据表中
import xlsxwriter
import pymysql
class MYSQL:
    def __init__(self):
        pass

    def connectDB(self):
        try:
            self._connect = pymysql.Connect(
                host='172.18.21.241',
                port=3306,
                user='songyao',
                passwd='#',
                db='songkylin',
                charset='utf8'
            )

            return 0
        except:
            return -1

    def export(self, table_name, sql, output_path):
        self._cursor = self._connect.cursor()
        count = self._cursor.execute(sql)
        # print(self._cursor.lastrowid)
        # 重置游标的位置
        self._cursor.scroll(0, mode='absolute')
        # 搜取所有结果
        results = self._cursor.fetchall()

        # 获取MYSQL里面的数据字段名称
        fields = self._cursor.description
        #workbook = xlwt.Workbook()
        workbook = xlsxwriter.Workbook(output_path,{'constant_memory': True})

        sheet = workbook.add_worksheet(table_name)

        # 写上字段信息
        # ls = []
        # ls.append(fields)
        for field in range(0,len(fields)):
            sheet.write(0, field, fields[field][0])
#            _ = sheet.cell(1, 1, fields[field][0])

        # 获取并写入数据段信息
        row = 1
        col = 0
        for row in range(0,len(results)+1):
            for col in range(0, len(fields)):
                sheet.write(row, col, u'%s' % results[row-1][col])

        workbook.close()

        self._cursor.close()
        self._connect.close()


def read_sql():
    import re

    with open('test.txt','rt',encoding='utf8') as f:
        result = f.read()

    lst = re.split(r'\[.*?\]',result)

    for line in lst:
        if not line:continue
        filename = re.findall(r'filename=(.*?)\n',line)[0]
        sheet = re.findall(r'sheet01=(.*?)\n',line)[0]
        sql = re.findall(r"sql='''(.*?)'''",line,re.S)[0]
        print(filename,sheet,sql)
        # break






if __name__ == '__main__':

    while True:
        mysql = MYSQL()
        flag = mysql.connectDB()
        read_sql()
        table_name = 'sheet'
        sql = "sql"
        output_path="filename"
        if flag == -1:
            print('数据库连接失败')
        else:
            print('数据库连接成功')
        try:
            mysql.export(table_name,sql,output_path)
        except Exception as msg:
            print(msg)
            break
        print('sql执行完成')

　　

前端开原件项目

https://panjiachen.github.io/vue-element-admin/#/dashboard

 

解析域名 nslookup www.baidu.com 223.5.5.5