Python3 querying the database and generating Excel reports

#!/usr/bin/Env python3#encoding=utf-8import osimport timeimport xlwthostip=' xxx.xxx.xxx.xx'User='user0001'passwd='xxxx'DB='DB01'SQLSTR1='SELECT Timeout_day as number of days overdue, COUNT (1) as Count from T_order_info where status in (7,9) GROUP by Timeout_day have Timeou T_day <> 0;'def createtable (selectsql,tablename): #连接数据库, execute SQL results= Os.popen ('mysql-h'+hostip+'- u'+user+'- P'+passwd+'- D'+db+'- E "'+selectsql+'"'). Read (). strip (). Split ('\ n') #获取列名 ColumnName= results[0].split ('\ t') #创建一个excel工作簿, encode UTF-8, the form supports the Chinese WB=XLWT. Workbook (encoding='Utf-8') #创建一个sheet Sheet=wb.add_sheet ('Sheet 1') #获取行数 rows=len (results) #获取列数 columns=Len (columnName) #创建格式style style=XLWT. Xfstyle () #创建font, setting font fonts=XLWT. Font () #字体格式 font.name='Times New Roman'#将字体font, applying to format style Style.font=font #创建alignment, centered alignment=XLWT. Alignment () #居中 Alignment.horz=XLWT. Alignment.horz_center #应用到格式style style.alignment=Alignment Style1=XLWT. Xfstyle () Font1=XLWT. Font () Font1.name='Times New Roman'#字体颜色 (green) #font1. Colour_index=3#字体加粗 Font1.bold=True Style1.font=font1 style1.alignment=Alignment forIinchRange (columns): #设置列的宽度 sheet.col (i). Width= the#插入列名 forIinchRange (columns): Sheet.write (0, I,columnname[i],style1) #将数据插入表格 forIinchRange1, rows): forJinchRange (columns): Sheet.write (I,j,results[i].split ('\ t') [J],style] #保存表格, and named ' xxxx ' Wb.save (tableName) #获取当前时间excelTime= Time.strftime ("%y%m%d") createtable (Sqlstr1,exceltime+'overdue statistics. xls')

Python3 querying the database and generating Excel reports