Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.