Python allows you to export a database as an Excel table instance with one click, pythonexcel

Python allows you to export a database as an Excel table instance with one click, pythonexcel

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.

Data Source

Export result

Dependency

Because it is implemented in Python, it requires support from the Python environment.

Python2.7.11

My Python environment is 2.7.11. Although you may use version 3.5, the idea is consistent.

Xlwt
Pip install xlwt

MySQLdb
Pip install MySQLdb

If the above method is not successful, you can go to the sourceforge official website to download the msi version on windows or compile it by yourself using the source code.

Database Problems

In this experiment, the database is actually related to how to operate the database using Python, and there are few knowledge points. The following are some simple statements we use this time.

Connection

Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'mysql', db = 'test', charset = 'utf8 ')

It is worth mentioning that the use of the last parameter, otherwise the data retrieved from the database will cause garbled characters. For more information about Garbled text, see this article.Encoding, decoding, and garbled characters

Obtain Field Information

fields = cursor.description

As for cursor, it is the core of our database operations. A cursor cannot be returned once the data is traversed. But we can also manually change the location.

Cursor. scroll (0, mode = 'absolute ') to reset the cursor position

Get Data

It is even easier to obtain data, but we must understand that a data item is similar to a two-dimensional array. We should pay attention when getting every cell item.

results = cursor.fetchall()

Excel Basics

This article also explains how to use Python to Operate excel Data.

Workbook

We must be clear about the concept of a thin job, which is the basis of our work. In contrast to the sheet below, workbook is the carrier on which sheet depends.

workbook = xlwt.Workbook()

Sheet

All our operations are performed on sheet.

Sheet = workbook. add_sheet ('table _ message', cell_overwrite_ OK = True)

For workbook and sheet, this is a bit fuzzy. Let's make assumptions like this.

In daily life, we all have a ledger, which is workbook. While we keep records on a sheet, which is the sheet we see. A ledger can have multiple tables or only one table. This is easy to understand. :-)

Case

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

Summary

Review the knowledge points used in this experiment.

• Python simple database operations
• Easy-to-use Python Excel
• Add charset = UTF-8 to solve the problem of garbled data retrieval in the database
• Process the obtained result set from the perspective of a two-dimensional array.

The above example of exporting a database as an Excel table with one click in Python is all the content shared by the editor. I hope you can give us a reference and support for the help house.