Python writes a database query result to a table experience

Python writes a database query result to a table experience

Description

Operation there are some requirements, in the development of the page before the implementation of this feature, every morning we have to query statements into forms, and then sent to them, it is very annoying ah, in Python wrote a script to achieve this function

First attempt:

#!/usr/bin/env python#encoding=utf-8import mysqldbimport xlwt     #excel  writehostIp =  ' 10.10.94.157 ' user =  ' xxxx ' passwd =  ' xxxx ' database =   ' xxxx ' Def chongzhiweitouzi ():           #充值未投资用户     sql =  ' selectctci.acc_no as  ' account ',ctci.cn_name as  ' name ', ctci.job_phone as  ' phone ',ctci.create_date as  ' registration time ',         tta.amount AS  ' Recharge amount ',         tta.create_time  AS  ' Recharge time ' from        tpp_t_account_deal tta,crm_t_ customer_info ctciwhere        tta.customer_id =  Ctci.id and         tta.type = 1and tta.order _status in  (1, 9) AND&Nbsp;tta.customer_id not in  (Select distinct customer_id from j_p2p_order  where order_type = 1 and product_id !=1) "     columnname = [' account ', ' name ', ' phone ', ' Registration time ', ' Recharge amount ', ' Recharge time ']   #定义所有的列名, total 6 columns      STYLE1= XLWT. Xfstyle ()                      #设置单元格格式     style1.num_format_str=  ' yyyy/m/d h:mm:ss '      WB=XLWT. Workbook (encoding= ' utf-8 ')         #创建一个excel工作簿, encoding utf-8, table support Chinese      sheet=wb.add_sheet (' sheet 1 ')               #创建一个sheet     for i in range (len (columnName)):           #将列名插入表格, total 6 columns         sheeT.write (0,i,columnname[i])     db = mysqldb.connect (HostIp,user,passwd,database, charset= "UTF8")           #连接数据库, coded utf-8     Cursor = db.cursor ()                        #创建一个指针对象     cursor.execute (SQL)                          #执行sql语句     results = cursor.fetchall ()      rows = len (Results)                         #获取行数     for i in  range (rows):         for j in range (3):        &nBsp;    sheet.write (I+1,j,results[i][j])          Sheet.write (i+1,3,results[i][3],style1)           #设置时间列的单元格格式         sheet.write (I+1,4,results[i][4])          sheet.write (i+1,5,results[i][5],style1)     wb.save (' Employee information sheet. xls ')                  #保存表格, and named   Employee information sheet. xls     cursor.close ()     db.close () Def yonghudenglu ():               #用户登录信息     sql =   ' select        login_type as  ' source ',         cn_name AS  ' login name ',         login _addr as  ' Login IP ',        mobile AS  ' mobile phone number ',         area AS  ' login area ',        mome as  ' mobile operator ' ,        login_time as  ' Login time ' from         sys_t_login_logger order by login_time desc '      columnname = [' source ', ' Login name ', ' Login IP ', ' Mobile number ', ' Login area ', ' mobile operator ', ' login time ']   #定义所有的列名      STYLE1= XLWT. Xfstyle ()                      #设置单元格格式     style1.num_format_str=  ' yyyy/m/d h:mm:ss '      WB=XLWT. Workbook (encoding= ' utf-8 ')         #创建一个excel工作簿, encoding utf-8, table support Chinese      sheet=wb.add_sheet (' sheet 1 ')              #创建一个sheet     for i in range (Len ( columnName)):          #将列名插入表格          sheet.write (0,i,columnname[i])     db = mysqldb.connect (HostIp, user,passwd,database,charset= "UTF8")     cursor = db.cursor ()                       # Create a pointer object     cursor.execute (' use j_p2p ')     cursor.execute (SQL)                         #执行sql语句     results = cursor.fetchall ()      rows = len (Results)                         #获取行数     for i in range (rows):         for j in range (6):             sheet.write (I+1,j,results[i][j])          sheet.write (I+1,6,results[i][6],style1)          # Set the cell format for the time column     wb.save (' User login information. xls ')                  #保存表格     cursor.close ()      Db.close () Chongzhiweitouzi () Yonghudenglu ()

Analysis:

Because there are multiple SQL query statements, made into multiple tables, so write a number of functions, the SQL statements are written into each function, and the table column name is also required to manually input, feel good trouble, and good fool

Second attempt:

#!/usr/bin/env python#encoding=utf-8import mysqldbimport xlwt     #excel  writehostIp =  ' 10.10.94.157 ' user =  ' xxx ' passwd =  ' xxxx ' database =   ' xxxxx ' F = open (' Select.sql ', ' R ') Resql = f.read (). Split (';')              #将所有的sql语句赋值给reSql;  The SQL statement in the Select.sql file needs to be '; ' End F.close () Def chongzhiweitouzi ():           #充值未投资用户     STYLE1= XLWT. Xfstyle ()                      #设置单元格格式     style1.num_format_str=  ' yyyy/m/d h:mm:ss '      WB=XLWT. Workbook (encoding= ' utf-8 ')         #创建一个excel工作簿, encoding utf-8, table support Chinese      sheet=wb.add_sheet (' sheet 1 ')              #创建一个sheet     db = mysqldb.connect (HostIp,user, passwd,database,charset= "UTF8")           #连接数据库, coded utf-8     cursor = db.cursor (cursorclass = mysqldb.cursors.dictcursor)                         #创建一个指针对象     cursor.execute (' use j_p2p ')     cursor.execute ( Resql[0])                         #执行sql语句     results = cursor.fetchall ()     columnname = []    for i in results[0]. Keys ():         columnname.append (i)     columnlen  = len (ColumnName) &NBSp;   for i in range (Columnlen):           #将列名插入表格, total 6 columns         sheet.write (0,i,columnname[i])      rows = len (Results)                         #获取行数     for i  in range (rows):         for j in range ( Columnlen):             sheet.write (i+1,j,results[i ][COLUMNNAME[J])     wb.save (' Employee information sheet. xls ')                  #保存表格, and named   Employee information sheet. Xls    cursor.close ()     db.close () Def yonghudenglu ():            #用户登录信息      style1= xlwt. Xfstyle ()                      #设置单元格格式     style1.num_format_str=  ' yyyy/m/d h:mm:ss '      WB=XLWT. Workbook (encoding= ' utf-8 ')         #创建一个excel工作簿, encoding utf-8, table support Chinese      sheet=wb.add_sheet (' sheet 1 ')               #创建一个sheet     db = mysqldb.connect (Hostip,user,passwd,database, charset= "UTF8")           #连接数据库, coded utf-8     Cursor = db.cursor (cursorclass = mysqldb.cursors.dictcursor)                        #创建一个指针对象     cursor.execute (resql[1])                        #执行sql语句      results = cursor.fetchall ()     columnName = []     for i in results[0].keys ():         Columnname.append (i)     columnlen = len (columnName)     for  i in range (Columnlen):          #将列名插入表格, total 6 columns          sheet.write (0,i,columnname[i])     rows =  len (Results)                         #获取行数     for i in range (rows):         for j in range (ColumnLen):             sheet.write (I+1,j,results[i][columnname[j]])     wb.save (' User login information. xls ')                 #保存表格, and named   Employee Information sheet. Xls    cursor.close ()     db.close () Chongzhiweitouzi () Yonghudenglu ()

Analysis:

This time, all the SQL statements that need to be queried are written in a file, note: to end with '; '

vim select.sqlselectctci.acc_no as  ' account ',ctci.cn_name as  ' name ', Ctci.job_phone AS   ' phone ',ctci.create_date as  ' registration time ',         tta.amount  AS  ' Recharge amount ',        tta.create_time as  ' recharge time ' from         tpp_t_account_deal tta,crm_t_customer_info ctciwhere         tta.customer_id = ctci.id AND         tta.type = 1AND tta.order_status IN  (1, 9) and  tta.customer_id NOT IN  (Select distinct customer_id from j_p2p_order  where order_type = 1 and product_id !=1); Select a.acc_no as   ' username ',a.cn_name as  ' name ',a.mobile as  ' bank reserved phone ',a.job_phone as  ' login phone ', B.order _amount as  ' Investment amount ', C.name as  ' products '  ,b.lucky_numbers as  ' lucky ' from activity_lucky_number b  left join crm_t_customer_info a on b.customer_id = a.idleft join  J_p2p_product c on b.product_id = c.idwhere  b.period= ' 4 ';

And the column names are retrieved from the query results themselves.

However, the feeling is still very messy, very complex, and using a number of functions, and the final table format is not good-looking, as follows:

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/79/42/wKioL1aM2JKBuUfTAAAmuoX_Fs8171.png "title=" Qq20160106170337.png "alt=" Wkiol1am2jkbuuftaaamuox_fs8171.png "/>

Third attempt:

No more using the MySQLdb module this time.

#!/usr/bin/env python#encoding=utf-8import osimport xlwt     #excel   writehostip =  ' 10.10.94.157 ' user =  ' xxxx ' passwd =  ' xxxx ' db =  ' xxxx ' F  = open (' Select.sql ', ' R ') Resql = f.read (). Split (';')              #将所有的sql语句赋值给reSql;  The SQL statement in the Select.sql file needs to be '; ' End F.close () def createtable (selectsql,tablename):     results =  Os.popen ' mysql -h ' +hostip+ '  -u ' +user+ '  -p ' +passwd+ '  -d ' +db+ '  -e  ' +selectsql+ ' '). Read (). strip (). Split (' \ n ')     columnname = results[0].split (' \ t ')      WB=XLWT. Workbook (encoding= ' utf-8 ')         #创建一个excel工作簿, encoding utf-8, table support Chinese      sheet=wb.add_sheet (' sheet 1 ')               #创建一个sheet     rows = len (Results)                         #获取行数     columns =  len (columnName)     for i in range (rows):         for j in range (columns):             sheet.write (I,j,results[i].split (' t ') [j])     wb.save ( TableName)                 #保存表格     createtable (resql[0], ' recharge non-investment users. xls ') createtable (resql[1], ' active investment list. xls ')

Analysis:

This is a good feeling, only a function is written.

All SQL statements are also written in the Select.sql file and end with '; '

This table style is as follows:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/79/44/wKiom1aM3LTyDtrAAAAktjWkuIg052.png "title=" Qq20160106172059.png "alt=" Wkiom1am3ltydtraaaaktjwkuig052.png "/>

Tabular format It's still not good.

Final optimization:

This time, the output format of the table is optimized

Optimized the Select.sql file: a note that can be written in #

vim select.sql# top-up non-investment user #selectctci.acc_no as  ' account ',ctci.cn_name as  ' name ', Ctci.job_phone  AS  ' phone ',ctci.create_date as  ' registration time ',         tta.amount as  ' Recharge amount ',        tta.create_time as  ' Recharge Time ' from        tpp_t_account_deal tta,crm_t_customer_info  ctciwhere        tta.customer_id = ctci.id and         tta.type = 1AND tta.order_status IN  (1,  9) and tta.customer_id not in  (select distinct customer_id from j _p2p_order where order_type = 1 and product_id !=1); #活动投资名单select   a.acc_no as  ' username ',a.cn_name as  ' name ',a.mobile as  ' bank reserved Cell phone ', a.job_phone as   ' Login phone ',b.order_amount as  ' investment amount ',c.name as  ' products '  ,b.lucky_numbers as  ' lucky ' from activity_lucky_ number b left join crm_t_customer_info a on b.customer_id =  a.idleft join j_p2p_product c on b.product_id = c.idwhere   B.period= ' 4 ';

This article is from the "See" blog, please be sure to keep this source http://732233048.blog.51cto.com/9323668/1732136

Python writes a database query result to a table experience