How to export data from Mysql to excel file
This article introduces how to export data from Mysql to excel file, the content is very detailed, interested friends can refer to, hope to be helpful to you.
Every time a data file is exported through intooufile and downloaded locally in excel table format, it will become garbled.
Baidu found that it still didn't work by transcoding, it could only be imported into txt format and then saved into excel format, which was quite troublesome, so I wrote a script to achieve my goal.
Source code
#! / usr/bin/python
#-*-coding: utf-8-*-
Import MySQLdb
Import xlsxwriter
Import os
Import sys
Os.remove ('demo3.xlsx')
# default_encoding = 'utf8'
# if sys.getdefaultencoding ()! = default_encoding:
# reload (sys)
# sys.setdefaultencoding (default_encoding)
Conn=MySQLdb.connect (user= "root", passwd= "NuyBc6jdYwU9oQ", db= "vip", port=3306,host= "127.0.0.1", charset= "utf8")
Cursor = conn.cursor ()
Sql= "*"
N=cursor.execute (sql)
Print "row count is:", n
Row=cursor.fetchall ()
Col=len (row [1])
Print "col count is:", col
Workbook = xlsxwriter.Workbook ('demo3.xlsx') # create an Excel file
Worksheet = workbook.add_worksheet () # create a worksheet object
Colname= ['?', 'Achilles pommel', 'Achilles','',',
# write header (i.e. column name)
Kenz1
For field_desc in cursor.description:
Name= colname [k] + "s" 1
M=field_desc [0] .decode ('utf-8') # Chinese aliases need to add utf-8
Print m
Worksheet.write (name,u'%s' m)
K=k+1
# write data
ITunes 2
For data in row:
Rowname= "A% s"% I
Print "now is:", rowname
Jroom1
For m in data:
Name= colnamej] + "% s"% I
Print m
Worksheet.write (name,u'%s' m)
J=j+1
I=i+1
Workbook.close ()
Cursor.close ()
Conn.close ()
Execute: download demo3.xlsx and open it locally without garbled code.
[root@host-192-1686254 soft] # ls
Demo3.xlsx xls.py
On how to export data from Mysql to excel file to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.
