'''
Author: liukai 2810248865@qq.com
Date: 2022-08-18 04:28:52
LastEditors: liukai 2810248865@qq.com
LastEditTime: 2023-06-29 09:35:25
FilePath: \PythonProject01\Python操作MySQL数据库及excel将数据库表中的数据导出到excel中.py
Description: 这是默认设置,请设置`customMade`, 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
'''
#2023-01-28 21:10:04.953
#已通过
import pymysql, xlwtdef conn_db(sql,host='192.168.56.210',user='root',passwd='1qaz@0987654321',db='StudentDB',port=3306,charset='utf8'):conn = pymysql.connect(user=user,host=host,passwd=passwd,db=db,port=port,charset=charset)cur = conn.cursor()cur.execute(sql)res = cur.fetchall()cur.close()conn.close()return resall_stu = conn_db('select * from Student') #获取数据库表数据
book = xlwt.Workbook()
sheet = book.add_sheet('用户信息表')
sheet.write(0, 0, '编号')
sheet.write(0, 1, '姓名')
sheet.write(0, 2, '年龄')
sheet.write(0, 3, '地址')
row = 1 #记录中编号有重复,用all_stu.index[i]+1会报错
for i in all_stu:for j in i:column = i.index(j)sheet.write(row, column, j)row += 1
book.save('用户信息表.xls')