After Python imports database data into an Excel article, this time, a push version is written as a function, followed by class. Ha ha..
Asked the group of friends, they mostly use tools to import data into Excel, I used to select ... into outfile exported to CSV format, but such data is not column headers, each time manually added. Later used SQLyog The tool also has no column headings. Later to switch to Navicat, feel that the future can be a safe day, there is a new project on the line, operations to "all" data (really do not know how they think), with Navicat tried, slow to death. It's so disgusting that I have to stop for no reason.
Simple and efficient for exporting "all" data. I decided to parse it in Python once. The central idea is to use the Python mysqldb module to derive the column headings of the data content from the INFORMATION_SCHEMA, create the Excel file using the XLWT module, and write the data. Below the code, welcome you to shoot Bricks:
#!/usr/bin/env python import sys import mysqldb import XLWT db=sys.argv[1] table=sys.argv[2] def xls (sql= ' select * FROM% s.%s '): #xls info WBK=XLWT. Workbook () Sheet=wbk.add_sheet (' Sheet 1 ') #connect Info try:conn=mysqldb.connect (host= ' 127.0.0.1 '), use R= ' root ', passwd= ' XXXXX ') cursor=conn.cursor () cursor.execute (' Use Information_schema ') cursor.exec Ute (' Select column_name from COLUMNS where table_name=%s and table_schema=%s ', (table,db)) Columnname=cursor.fetch All () Columnlen=len (columnName) columnnum=0 to data in range (Columnlen): SHEET.WR ITE (0,columnnum,columnname[columnnum][0]) columnnum +=1 cursor.execute (sql% (db,table)) d Atainfo=cursor.fetchall () rowline=0 for line in range (len (datainfo)): Lineinfo=datainfo[li
NE] rowline+=1 columnline=0 for row in Lineinfo: Sheet.write (Rowline,columnline,row) columnline+=1 wbk.save ('%s.xls '% table) Cusor.close () Conn.commit () Conn.close () Finally:conn.close () sys.exit (1) if __ name__== ' __main__ ': xls ()