从数据库中导出数据到Excel 

 1 """
 2  1、写一个程序，传入任意一个存在的表名，导出这个表里面所有的数据到excel
 3         字段名是表头
 4         def export_excel(table_name,db_name):
 5             pass
 6             fmz.xls
 7 
 8     #select * from user where username="xx";
 9 """
10 
11 import tools
12 import xlwt
13 import time
14 
15 
16 def get_table_field(table_name):
17     query_table_info_sql = "select COLUMN_NAME from " \
18                            "information_schema.COLUMNS where table_name = '%s';" % table_name
19     table_field = tools.op_mysql(query_table_info_sql)
20     title = [ table[0] for table in table_field]
21     return title
22 
23 def write_excel(data,excel_name):
24     book = xlwt.Workbook()
25     sheet = book.add_sheet("sheet1")
26     for row,row_data in enumerate(data):
27         for col,col_data in enumerate(row_data):
28             sheet.write(row,col,col_data)
29     book.save(excel_name)
30 
31 def main(table_name):
32     title = get_table_field(table_name)
33     if not title:
34         print("表不存在！")
35         return
36     query_data_sql = "select * from %s;" % table_name
37     result = list(tools.op_mysql(query_data_sql))
38     result.insert(0,title)
39     excel_name = "%s_%s.xls" % (time.strftime("%Y%m%d%H%M%S"),table_name)
40     write_excel(result,excel_name)
41 
42 
43 
44 
45 if __name__ == '__main__':
46     main("nhy_user")
47     main("hym")
48     main("user_ljq")

 

posted @ 2021-07-02 16:06  等待iing  阅读(256)  评论(0)    收藏  举报