Detailed explanation of how to use Python to export a database into an Excel table with one click

Exporting database data into excel tables can also be said to be a very commonly used function. After all, not everyone understands database operation statements.
Let’s take a look at the finished effect first.
Data source

Export results

Dependencies
Since it is implemented in Python, it needs Python environment support
Python2.7.11
My Python environment is 2.7.11. Although you may be using version 3.5, the idea is the same.
xlwtpip install xlwt
MySQLdbpip install MySQLdb
If the above method fails, you can go to the sourceforge official website to download the msi version on windows or use the source code Compile it yourself.
Database related
In this experiment, the database related thing is actually how to use Python to operate the database, and there are very few knowledge points. The following are some simple statements we used this time .
Connection
conn = MySQLdb.connect(host='localhost',user='root',passwd='mysql',db='test',charset='utf8')
What is worth mentioning here is the use of the last parameter, otherwise the data taken out from the database will be garbled. Regarding the problem of garbled codes, if you still don’t understand anything, you might as well read this articleA brief discussion on encoding, decoding, and garbled codes
Getting field information

fields = cursor.description

As for Cursor is the core of our database operations. The characteristic of a cursor is that once the piece of data has been traversed, it cannot be returned. But we can also change its position manually.
cursor.scroll(0,mode='absolute') to reset the cursor position
Getting data
Getting data is even easier, but we must understand in our hearts that the data item is a similar existence of dimensional arrays. We should pay attention when getting each cell item.

results = cursor.fetchall()

Excel Basics
Similarly, here we also explain how to use Python to operate excel data.
workbook
We must be clear about the concept of workbook, which is the basis of our work. Corresponding to the sheet below, the workbook is the carrier on which the sheet depends.

workbook = xlwt.Workbook()

sheet
All our operations are performed on the sheet.
sheet = workbook.add_sheet('table_message',cell_overwrite_ok=True)
For workbook and sheet, if this is a bit vague. Let's assume this.
When we keep accounts in our daily lives, we all have an account book, which is the workbook. Our accounting is recorded in tables one after another, and these tables are the sheets we see. There can be many tables in a ledger, or just one table. This will be easy to understand. :-)
Case
Let’s look at a small case.

# coding:utf8
import sys
reload(sys)
sys.setdefaultencoding('utf8')
# __author__ = '郭 璞'
# __date__ = '2016/8/20'
# __Desc__ = 从数据库中导出数据到excel数据表中
import xlwt
import MySQLdb
conn = MySQLdb.connect('localhost','root','mysql','test',charset='utf8')
cursor = conn.cursor()
count = cursor.execute('select * from message')
print count
# 重置游标的位置
cursor.scroll(0,mode='absolute')
# 搜取所有结果results = cursor.fetchall()# 获取MYSQL里面的数据字段名称fields = cursor.description
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('table_message',cell_overwrite_ok=True)
# 写上字段信息
for field in range(0,len(fields)):
 sheet.write(0,field,fields[field][0])
# 获取并写入数据段信息
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(r'./readout.xlsx')

Encapsulation
For the convenience of use, it is now encapsulated into an easy-to-call function.
After encapsulation

# coding:utf8
import sys
reload(sys)
sys.setdefaultencoding('utf8')
# __author__ = '郭 璞'
# __date__ = '2016/8/20'
# __Desc__ = 从数据库中导出数据到excel数据表中
import xlwt
import MySQLdb
def 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
 # 重置游标的位置
 cursor.scroll(0,mode='absolute')
 # 搜取所有结果
 results = cursor.fetchall()
 # 获取MYSQL里面的数据字段名称
 fields = cursor.description
 workbook = xlwt.Workbook()
 sheet = workbook.add_sheet('table_'+table_name,cell_overwrite_ok=True)
 # 写上字段信息
 for field in range(0,len(fields)):
  sheet.write(0,field,fields[field][0])
 # 获取并写入数据段信息
 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)
# 结果测试
if __name__ == "__main__":
 export('localhost','root','mysql','test','datetest',r'datetest.xlsx')

Test result

id name date
1 dlut 2016-07-06
2 清华大学 2016-07-03
3 北京大学 2016-07-28
4 Mark 2016-08-20
5 Tom 2016-08-19
6 Jane 2016-08-21

The above is the detailed content of Detailed explanation of how to use Python to export a database into an Excel table with one click. For more information, please follow other related articles on the PHP Chinese website!