Using Python to export a database as an Excel table with one click

Exporting database data to an excel table is also a common function. After all, not anyone knows database operation statements. Let's take a look at the effect. The export result of the data source depends on Python. Therefore, you must have Python Environment Support for Python2.7.11. my Python environment is 2.7.11. Although you may use version 3.5, the idea is consistent. Exporting data from the xlwt database to an excel table is also a common function. After all, not anyone knows database operation statements.
Let's take a look at the effect.
Data source
The following is a small case.

# Coding: utf8import sysreload (sys) sys. setdefaultencoding ('utf8 ') # _ author _ = 'Guo Pu' # _ date _ = '2017/20' # _ Desc _ = export data from the database to the excel data table import xlwtimport MySQLdbconn = MySQLdb. connect ('localhost', 'root', 'mysql', 'test', charset = 'utf8') cursor = conn. cursor () count = cursor.exe cute ('select * from message') print count # reset the cursor position cursor. scroll (0, mode = 'absolute ') # Search All results = cursor. fetchall () # obtain the data field name fields = cursor in MYSQL. descriptionworkbook = xlwt. workbook () sheet = workbook. add_sheet ('Table _ message', cell_overwrite_ OK = True) # write the field information for field in range (0, len (fields): sheet. write (0, field, fields [field] [0]) # obtain and write data segment information row = 1col = 0for row in range (1, len (results) + 1 ): for col in range (0, len (fields): sheet. write (row, col, u '% s' % results [row-1] [col]) workbook. save (r '. /readout.xlsx ')

Encapsulation
For ease of use, it is encapsulated into a function that is easy to call.
After encapsulation

# Coding: utf8import sysreload (sys) sys. setdefaultencoding ('utf8 ') # _ author _ = 'Guo Pu' # _ date _ = '2017/20' # _ Desc _ = export data from the database to the excel data table import xlwtimport MySQLdbdef export (host, user, password, dbname, table_name, outputpath): conn = MySQLdb. connect (host, user, password, dbname, charset = 'utf8') cursor = conn. cursor () count = cursor.exe cute ('select * from' + table_name) print count # reset the cursor position cursor. scroll (0, mode = 'absolute ') # Search All results = cursor. fetchall () # obtain the data field name fields = cursor in MYSQL. description workbook = xlwt. workbook () sheet = workbook. add_sheet ('Table _ '+ table_name, cell_overwrite_ OK = True) # write the field information for field in range (0, len (fields): sheet. write (0, field, fields [field] [0]) # obtain and write data segment information row = 1 col = 0 for row in range (1, len (results) + 1): for col in range (0, len (fields): sheet. write (row, col, u '% s' % results [row-1] [col]) workbook. save (outputpath) # Test result if _ name _ = "_ main _": export('localhost', 'root', 'mysql', 'test', 'datetest', r'datetest.xlsx ')

Test results

Id name date1 dlut 2016-07-062 Tsinghua University 2016-07-033 Peking University 2016-07-284 Mark 2016-08-205 Tom 2016-08-196 Jane

The above is a detailed description of how to use Python to export a database as an Excel table with one click. For more information, see other related articles in the first PHP community!