python获取mysql数据为excel中的sheet_Python读取Mysql数据并写入Excel

'''

1、读取mysql数据

2、数据写入Excel

'''

import pymysql

import os

import xlwt

class Test(object):

def __init__(self):

self.host = "127.0.0.1"

self.port = 3306

self.db_name = 'blog'

self.username = 'root'

self.password = '123456'

# 连接数据库

def get_data(self, sql):

conn = pymysql.connect(

host=self.host,

user=self.username,

password=self.password,

port=self.port,

database=self.db_name,

charset='utf8',

cursorclass=pymysql.cursors.DictCursor

)

#游标

curson = conn.cursor()

curson.execute(sql)

data = curson.fetchall()

curson.close()

conn.close()

return data

def wirte_excel(self):

sql = "SELECT a.id,a.title,a.desc,a.data,a.image FROM article a WHERE id < 10000"

file_name = '提取数据.xls'

data = self.get_data(sql)

if not data:

print("数据为空")

return False

if os.path.exists(file_name):

os.remove(file_name)

# 创建工作簿

file = xlwt.Workbook()

sheet1 = file.add_sheet("Sheet1", cell_overwrite_ok=True)

# 写入表头

column_name = ['文章ID', '文章标题', '文章描述', '发表时间', '封面路径']

for i in range(len(column_name)):

sheet1.write(0, i, column_name[i])

# 写入数据

num = 1

for item in data:

sheet1.write(num, 0, item['id'])

sheet1.write(num, 1, item["title"])

sheet1.write(num, 2, item["desc"])

date = item['data'].strftime('%Y-%m-%d')

sheet1.write(num, 3, date)

sheet1.write(num, 4, item["image"])

num += 1

# 保存工作簿

file.save(file_name)

if not os.path.exists(file_name):

print("文件写入失败")

return False

else:

print("文件写入成功")

return True

if __name__ == '__main__':

test = Test()

test.wirte_excel()