Python连接数据库生成Excel

网上翻到的 测试下可用 修改对应数据库的账号密码表名即可

# --*-- coding:utf8 --*--
import pymysql, xlwt

def export_excel(xxx): # 此处自行修改
    # 连接数据库，查询数据
    host, user, passwd, db = 'XXX', 'XXX', 'XXX', 'XXX' # 此处自行修改
    conn = pymysql.connect(user=user, host=host, port=3306, passwd=passwd, db=db, charset='utf8')
    cur = conn.cursor()
    sql = 'select * from %s' % xxx # 此处自行修改
    cur.execute(sql)  # 返回受影响的行数

    fields = [field[0] for field in cur.description]  # 获取所有字段名
    all_data = cur.fetchall()  # 所有数据
    # 写入excel
    book = xlwt.Workbook()
    sheet = book.add_sheet('sheet1')

    for col, field in enumerate(fields):
        sheet.write(0, col, field)

    row = 1
    for data in all_data:
        for col, field in enumerate(data):
            # if col == 4:
            #     sheet.write(row, col, field)
            sheet.write(row, col, field)
        row += 1

    book.save("%s.xls" % xxx) # 此处自行修改


if __name__ == '__main__':
    export_excel('xxx') # 此处自行修改

运行后就会自行生成xls文件