Exporting database data to excel tables can also be a very common feature. After all, not anyone understands the database operation statements.
Here's a first look at the finished result.
data sources
Export results
dependencies
Since it's a Python implementation, it needs to be supported by a Python environment
Python2.7.11
My Python environment is 2.7.11. While you may be using version 3.5, the idea is the same.
xlwt
pip install xlwt
MySQLdb
pip install MySQLdb
If the above way is not successful, you can go to the sourceforge official website to download the windows msi version or use the source code to compile your own.
Database related
The test, database-related is actually how to use Python to operate the database only, the knowledge is very little, the following for us to use some simple statements.
grout
conn = (host='localhost',user='root',passwd='mysql',db='test',charset='utf8')
Here it is worth mentioning the use of the last parameter, otherwise the data from the database will make the garbled code. On the issue of garbled code, if there is still do not understand the place, may wish to see this articleThe problem of encoding, decoding, and garbled codes
Getting field information
fields =
As for cursor, it is the core of our database manipulation. A cursor is characterized by the fact that once it has traversed the data, it cannot be returned. But we can change its position manually.
(0,mode='absolute') to reset the position of the cursor
Getting data
Getting the data is simply easier, but we must understand in our mind that the data items are akin to a two-dimensional array. We should be careful when fetching each cell item.
results = ()
Excel Basics
Similarly, what is explained here is how to manipulate excel data using Python.
workbook
The concept of the workbook must be clear to us, its the basis of our work. Corresponding to the sheet below, the workbook is the vehicle on which the sheet rests.
workbook = ()
sheet
All of our operations, we do on the sheet.
sheet = workbook.add_sheet(‘table_message',cell_overwrite_ok=True)
For workbook and sheet, if this is a bit vague. It is worthwhile to make assumptions like this.
In our daily life when we keep a book, we have a book, which is a workbook. while we keep a book is recorded on a sheet, these tables are what we see as a sheet. a book can have many tables, or just a table. So it's easy to understand. :-)
case (law)
See a small case study below.
# coding:utf8
import sys
reload(sys)
('utf8')
# __author__ = 'Guo Pu'
# __date__ = '2016/8/20'
# __Desc__ = export data from database to excel spreadsheet
import xlwt
import MySQLdb
conn = ('localhost','root','mysql','test',charset='utf8')
cursor = ()
count = ('select * from message')
print count
# Reset the position of the cursor
(0,mode='absolute')
# Search all results
results = ()
# Get the name of the data field in MYSQL.
fields =
workbook = ()
sheet = workbook.add_sheet('table_message',cell_overwrite_ok=True)
# Write field information
for field in range(0,len(fields)):
(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)):
(row,col,u'%s'%results[row-1][col])
(r'./')
seal inside
For ease of use, it is now wrapped into an easy to call function.
after encapsulation
# coding:utf8
import sys
reload(sys)
('utf8')
# __author__ = 'Guo Pu'
# __date__ = '2016/8/20'
# __Desc__ = export data from database to excel spreadsheet
import xlwt
import MySQLdb
def export(host,user,password,dbname,table_name,outputpath):
conn = (host,user,password,dbname,charset='utf8')
cursor = ()
count = ('select * from '+table_name)
print count
# Reset the position of the cursor
(0,mode='absolute')
# Search all results
results = ()
# Get the name of the data field in MYSQL.
fields =
workbook = ()
sheet = workbook.add_sheet('table_'+table_name,cell_overwrite_ok=True)
# Write field information
for field in range(0,len(fields)):
(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)):
(row,col,u'%s'%results[row-1][col])
(outputpath)
# Results testing
if __name__ == "__main__":
export('localhost','root','mysql','test','datetest',r'')
Test results
id name date 1 dlut 2016-07-06 2 Tsing Hua or Qinghua University, Beijing 2016-07-03 3 Peking University 2016-07-28 4 Mark 2016-08-20 5 Tom 2016-08-19 6 Jane 2016-08-21
summarize
Review what knowledge points were used in this experiment.
-Python easy to operate the database
-Python easy to operate Excel
-Database to take out the data to solve the problem of messy code to add charset=utf-8
-Process the fetched result set in terms of a two-dimensional array.
The above example of this Python to realize the database one-click export to Excel table is all I have to share with you, I hope to be able to give you a reference, and I hope you will support me more.