A detailed way to export a database key to an Excel table using Python

The database data is exported as an Excel table, and it can be used as a common function. After all, not everyone understands database operation statements.
Let's take a look at the finished effect.
Data Source

Export Results

Dependent
Because Python is implemented, it requires support from the Python environment
Python2.7.11
My Python environment is 2.7.11. Although you may be using the 3.5 version, the idea is consistent.
XLWTPip Install XLWT
MySQLdbPip Install MySQLdb
If the above method is unsuccessful, you can go to the SourceForge website to download the MSI version on Windows or use the source code to compile it yourself.
Database Related
In this experiment, the database is related to how to use Python to manipulate the database only, the knowledge point is very few, the following are some of the simple statements we used this time.
Connection
conn = MySQLdb.connect (host= ' localhost ', user= ' root ', passwd= ' MySQL ', db= ' test ', charset= ' UTF8 ')
It is worth mentioning here that the last parameter is used, otherwise the data extracted from the database will be garbled. About the garbled problem, if there is no understanding of the place, may wish to look at this article on the problem of coding, decoding and garbled characters
Get field information

Fields = Cursor.description

As for the cursor, it is the core of our operational database. The cursor is characterized by the fact that once the data has been traversed, it cannot be returned. But we can also manually change their position.
Cursor.scroll (0,mode= ' absolute ') to reset the cursor position
Get Data
Getting the data is simply a breeze, but we have to understand in our hearts that the data item is a similar to the existence of a two-dimensional array. We should be careful when we get every cell item.

Results = Cursor.fetchall ()

Excel Basics
Also, here's how to use Python to manipulate Excel data.
Workbook
The concept of a workbook we must be clear, it is the basis of our work. In contrast to the sheet below, workbook is the carrier on which sheet relies for survival.

Workbook = xlwt. Workbook ()

sheet
All of our operations are done on the sheet.
Sheet = workbook.add_sheet (' table_message ', cell_overwrite_ok=true)
for workbook and sheet, if this is a bit blurry. It may be assumed.
in our daily life, we all have a ledger, which is workbook. And our bookkeeping is recorded in a sheet of tables, which are the sheet we see. A ledger can have a number of tables, or just a table. So it's easy to understand. :-)
Case
see a small case.

# Coding:utf8import sysreload (SYS) sys.setdefaultencoding (' UTF8 ') # __author__ = ' Guo pu ' # __date__ = ' 2016/8/20 ' # __Desc__ = from Export data in database to Excel data sheet import xlwtimport mysqldbconn = MySQLdb.connect (' localhost ', ' root ', ' MySQL ', ' Test ', charset= ' UTF8 ' cursor = conn.cursor () Count = Cursor.execute (' SELECT * from message ') print count# reset cursor position cursor.scroll (0,mode= ' Absolute ') # Search All results results = Cursor.fetchall () # Gets the data field name inside MySQL fields = Cursor.descriptionworkbook = XLWT. Workbook () sheet = workbook.add_sheet (' table_message ', cell_overwrite_ok=true) # Write field information for field in range (0,len (fields) ): Sheet.write (0,field,fields[field][0]) # Gets and writes data segment information row = 1col = 0for row in range (1,len (results) +1): For col in range (0,l En (Fields)):  sheet.write (row,col,u '%s '%results[row-1][col]) Workbook.save (R './readout.xlsx ')

Packaging
For ease of use, it is now packaged as an easy-to-call function.
After encapsulation

# Coding:utf8import sysreload (SYS) sys.setdefaultencoding (' UTF8 ') # __author__ = ' Guo pu ' # __date__ = ' 2016/8/20 ' # __Desc__ = from Export data in database to Excel data sheet 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.execute (' SELECT * FROM ' +table_name) print count # reset cursor position cursor.scroll (0,mode= ' absolute ') # Search All results results = Cursor.fetchall () # Get data fields inside MySQL Name fields = Cursor.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]) # Gets and writes data segment information row = 1 col = 0 for row in range (1,len (results) +1): for Col In range (0,len): Sheet.write (row,col,u '%s '%results[row-1][col]) Workbook.save (outputpath) # results Test 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 2016-08-21