Python小练习_数据库表数据导出到excel
需求：只要传入一个表名，就能把所有的数据导入出来，字段名是excel的表头
1.要动态获取到标的字段 cur.descrption能获取到表的字段
fileds = [filed[0] for filed in cur.description]
下图可以获取表结构，并取出表头
2.获取数据 select * from %s;%table_name
3.导出到excel
1 import pymysql,xlwt 2 def export_excel(table_name): 3 coon =pymysql.connect( 4 user='xxx',passwd='123456',host='xxx.xxx.xxx.xxx',port=3306, 5 db='xxx',charset='utf8' 6 ) 7 cur = coon.cursor() 8 sql = 'select * from %s;'%table_name 9 cur.execute(sql) #执行sql 10 fileds = [filed[0] for filed in cur.description] #所有的字段 11 all_data = cur.fetchall() 12 13 book = xlwt.Workbook() 14 sheet = book.add_sheet('sheet1') 15 # col = 0 16 # for filed in fileds: 17 # sheet.write(0,col,filed) 18 # col+=1 19 for col,filed in enumerate(fileds): #写表头的， 20 sheet.write(0,col,filed) #这两行代码等同于上面四行代码 21 22 # print(all_data) 23 row = 1 #第一行 24 for data in all_data: #行 25 for col,filed in enumerate(data): #控制列 26 sheet.write(row,col,filed) 27 row+=1 #每次写完一行，行数加一 28 29 book.save('%s.xls'%table_name) 30 31 export_excel('app_student')
