Python implementation database One-click Export to Excel table

• • Depend on
    • Python2711
    • Xlwt
    • MySQLdb
  • Database related
    • Connection
    • Get field information
    • Get Data
  • Excel Basics
    • Workbook
    • Sheet
  • Case
  • Packaging
    • After encapsulation
    • Test results
  • Summarize

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
  

Depend on

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.

Xlwt

Pip Install XLWT

MySQLdb

Pip 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. On the problem of garbled, if there is not understand the place, may wish to look at this article http://blog.csdn.net/marksinoberg/article/details/52254401.

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 carried out 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.

We all have a ledger in our daily life, and that's 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

Let's look at a small case.

# Coding:utf8Import sysreload (SYS) sys.setdefaultencoding (' UTF8 ')# __author__ = ' Guo Pu '# __date__ = ' 2016/8/20 '# __desc__ = Export data from database to Excel data tableImport XLWTImport Mysqldbconn = MySQLdb.connect (' localhost ',' Root ',' MySQL ',' Test ', charset=' UTF8 ') cursor = conn.cursor () Count = Cursor.execute (' SELECT * from message ')Print Count# resets the position of the cursor cursor.scroll (0,mode=' Absolute ')# Search All results results = Cursor.fetchall ()# Get 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]) # Get 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 ')               

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__ = Export data from database to Excel data tableImport XLWTImport MySQLdbDefExport(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# resets the position of the cursor cursor.scroll (0,mode=' Absolute ')# Search All results results = Cursor.fetchall ()# Get the data field name inside MySQL fields = cursor.description Workbook = xlwt. Workbook () sheet = Workbook.add_sheet (' Table_ ' +table_name,cell_overwrite_ok=True)# Write the field informationFor fieldIn range (0,len (Fields)): Sheet.write (0,field,fields[field][0])# Get 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)# Results Test if __name__ = = "__main__": Export (' localhost ',' root ',' MySQL ',' test ',' datetest ', R ' datetest.xlsx ')                 

Test results

Idname Date1 dlut 2016-07-2 Tsinghua University 2016-07-3 Peking University 2016-07-< c13>284 Mark 2016-08-5 Tom 2016-08-6 Jane 2016-08- 21  

Summarize

Review what points of knowledge are used in this experiment.

• Python easy-to-operate database
• Python simple operation Excel
• Add Charset=utf-8 to solve the problem of data garbled in database extraction
• Processes the obtained result set in the perspective of a two-dimensional array.

Python implementation database One-click Export to Excel table