循环导出所有行和列
def export_excel(table_name): host,user,passwd,db='192.168.0.12','root','myjcyf','us_sys' coon=pymysql.connect(host=host,user=user,passwd=passwd,db=db,port=3306,charset='utf8') cur=coon.cursor() #建立游标 sql='select * from %s;'%table_name cur.execute(sql)#执行sql fileds=[filed[0] for filed in cur.description]#所有字段 all_data=cur.fetchall() book=xlwt.Workbook() sheet=book.add_sheet('sheet1') for col,filed in enumerate(fileds): sheet.write(0,col,filed) row= data in all_data: #控制行1 for for index,datacol in enumerate(data):#控制列 sheet.write(row,index,datacol) row+=1 book.save('%s.xls'%table_name) export_excel('us_sys.stu')#导出excel
自动查询下标
fileds=['id','name','sex'] for index,filed in enumerate(fileds):#enumerate,可以查询列表每个字段的下标 print(index,filed) # 结果: # 0 id # 1 name # 2 sex
通用导出excel-只导出字段
import pymysql,xlwt # # def export_excel(table_name): # host,user,passwd,db='192.168.0.12','root','myjcyf','us_sys' # coon=pymysql.connect(host=host,user=user,passwd=passwd,db=db,port=3306,charset='utf8') # cur=coon.cursor() #建立游标 # sql='select * from %s;'%table_name # cur.execute(sql)#执行sql # fileds=[filed[0] for filed in cur.description]#所有字段 # all_date=cur.fetchall() # book=xlwt.Workbook() # sheet=book.add_sheet('sheet1') # col=0 # for filed in fileds: # sheet.write(0,col,filed) # col+=1 # book.save('%s.xls'%table_name) # export_excel('us_sys.stu')#导出excel