Python learning note-day7-2-"Python leads data from MySQL database to Excel, read Excel, modify Excel"

This section is how to use Python to export data in the database into Excel, and how to read Excel, repair Excel and other operations.

First, using Python to export the data in the database into Excel

1, import the following modules, no need to install

Import Pymysql #mysql连接模块
Import XLWT #写excel的第三方库

Guide data from database, write to Excel file

Import PYMYSQL,XLWT

DEF export_excel (table_name):
Import Pymysql
Host, user, passwd, db = ' 127.0.0.1 ', ' xxx ', ' 123456 ', ' xxxx '
conn = pymysql.connect (user=user,host=host,port=3306,passwd=passwd,db=db,charset= ' UTF8 ')
cur = conn.cursor () # Creating Cursors
sql = ' select * from%s; '%table_name
Cur.execute (SQL) # execute MySQL
fileds = [filed[0] for filed in Cur.description] # list-generated, all fields
all_data = Cur.fetchall () #所有数据
#写excel
Book = XLWT. Workbook () #先创建一个book
Sheet = book.add_sheet (' Sheet1 ') #创建一个sheet表
# col = 0
# for field in Fileds: #写表头的
# sheet.write (0, col, field)
# col + + 1
#enumerate自动计算下标
for Col, field in Enumerate (fileds): #跟上面的代码功能一样
sheet.write (0, col, field)

#从第一行开始写
row = 1 #行数
For data in All_data: #二维数据, how many data, control the number of rows
For Col, field in enumerate (data): #控制列数
Sheet.write (Row, col, field)
Row + = 1 #每次写完一行, number of rows plus 1
Book.save ('%s.xls '%table_name) #保存excel文件

Export_excel (' app_student ')

As a result, a App_student.xls file was generated

Second, built-in function enumerate
# Enumerate #自动计算下标
# fileds = [' id ', ' name ', ' sex ', ' addr ', ' Gold ', ' score ')
# for index, filed in Enumerate (fileds): #同时打印下标
# print (index, filed)

Third, read Excel

Iv. modifying Excel

Import xlrd
From xlutils import copy  #这个模块需要这样导入

Five, Operation database, Excel operation summary

cur = coon.cursor (cursor=pymysql.cursors.dictcursor)
when a cursor is created, a cursor type is specified, and a dictionary is returned. 
Fetchall () #获取到这个sql执行的全部结果, which puts every row of data in a database table inside a list
[ [' 1 ', ' 2 ', ' 3 ']] [{},{},{}]
Fetchone () #获取到这个sql执行的一条结果, it returns just one piece of data

If the result of the SQL statement execution is multiple data, then use Fetchall ()
If you can be sure that there is only one result of SQL execution, then use Fetchone ()

requirements: As long as you pass in a table name, you can import all the data, the field name is Excel's table header
1, to dynamically get to the table field cur.description can get to the table field
fileds = [filed[0] for filed in Cur.description]
2. Get the Data select * from "%s"% table_name
3. Loop write to Excel

enumerate ([List,list2]) #循环的时候, get directly to subscript, and value
For Index,value in Enumerate ([List,list2]):
print (Index,vlaue)

Python learning note-day7-2-"Python leads data from MySQL database to Excel, read Excel, modify Excel"