python连接MySQL数据库并生成excel

一、使用到的包

ConfigParser：配置文件读取

datetime：时间

xlwt：excel工具

PyMysql：MySQL数据库连接工具

二、目录结构

|----my2excel

      |----src

            |---my2excel.py

      |----config

            |---config.ini

三、上代码

from configparser import ConfigParser
import datetime,xlwt,pymysql
def export_excel(user, host, port, passwd, dbname, table_name, xltime):
    #创建数据库连接
    conn = pymysql.connect(user=user,host=host,port=port,passwd=passwd,db=dbname,charset='utf8')
    cur = conn.cursor()

    sql = 'select * from %s;' %table_name

    #读取数据
    cur.execute(sql)
    fileds = [filed[0] for filed in cur.description]
    all_date = cur.fetchall() #所有数据
    for result in all_date:
        print(result)
    
    #写excel

    book = xlwt.Workbook() #创建一个book

    sheet = book.add_sheet('result') #创建一个sheet表

    for col,filed in enumerate(fileds):
        sheet.write(0,col,filed)

    #从第一行开始写

    row = 1
    for data in all_date:
        for col,filed in enumerate(data):
            sheet.write(row,col,filed)
        row += 1

    book.save('%s-%s.xls' %(table_name,xltime))
    #关闭数据库
    conn.close()

if __name__ == '__main__':
    #读取“config.ini”配置文件内容
    config = ConfigParser()
    config.read('./config/config.ini',encoding='utf-8')
    host = config.get('dbinfo','host')
    user = config.get('dbinfo','user')
    passwd = config.get('dbinfo','passwd')
    port = config.getint('dbinfo','port')
    dbname = config.get('table_name','dbname')
    #创建当前时间戳
    xltime = datetime.datetime.now().strftime('%Y-%m-%d')
    print(host,user,passwd,port,dbname,xltime)
    #export_excel(user, host, port, passwd, dbname,'stocks', xltime)

四、生成“requirement.txt”

在根目录执行“pipreqs --use-local --encoding=utf8”，注意，需要安装“pipreqs”模块！

配置python国内镜像地址：pip config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple