把读取sql的结果写⼊到excel⽂件
1.利⽤pandas模块
# encoding: utf-8
import time
import pandas as pd
import pymysql
def getrel(sql):
'''
连接mysql数据库，根据条件查询出来我们所需要数据
:return: 根据条件从sql查询出来的数据
'''
conn = t(host='localhost', user='root', password='123',
db='db_test', charset='utf8mb4')
cur = conn.cursor()
rel = cur.fetchall()
cur.close()
conn.close()
return relexcel连接sql数据库教程
def getxlsx(rel):
'''
把从数据库中查询出来的数据写⼊excel⽂件
:
param rel:
:return:
'''
file_name = time.strftime('%Y-%m-%d') + '.xlsx'
dret = pd.DataFrame.from_records(list(rel))  # mysql查询的结果为元组，需要转换为列表
<_excel(file_name, index=False, header=("平台货号", "商品名称", "售价"))  # header 指定列名，index 默认为True，写⾏名
if__name__ == '__main__':
sql = 'select goods_commonid,goods_name,goods_price from mall_goods_common where store_id=110 and set_new_time>1560182400;'    rel = getrel(sql)
getxlsx(rel)
2.使⽤xlwt模块
import pymysql
import xlwt
def get_sel_excel(sql):
'''
连接mysql并把查询出来的数据写⼊excel表格
:param sql:
:return:
'''
conn = t(
host="localhost",
port=3306,
user="root",
passwd="123",
db="db_test",
charset="utf8mb4"
)
# 建⽴游标
cursor = conn.cursor()
print("开始查询表！")
# 执⾏sql语句
# 获取查询到结果
res = cursor.fetchall()
w_excel(res)
def w_excel(res):
'''
把数据写⼊excel表格中
:param res:
:return:
'''
book = xlwt.Workbook()  # 新建⼀个excel
sheet = book.add_sheet('vehicle_land')  # 新建⼀个sheet页
title = ['平台货号', '商品名称', '售价']
# 写表头
i = 0
for header in title:
sheet.write(0, i, header)
i += 1
# 写⼊数据
for row in range(1, len(res)):
for col in range(0, len(res[row])):
sheet.write(row, col, res[row][col])
row += 1
col += 1
book.save('vehicle_land.xls')
print("导出成功！")
if__name__ == "__main__":
sql = 'select goods_commonid,goods_name,goods_price from mall_goods_common where store_id=110 and set_new_time>1560182400;'
get_sel_excel(sql)
3.pandas
import pandas as pd
from sqlalchemy import create_engine
# 初始化数据库连接，使⽤pymysql模块
# MySQL的⽤户：root, 密码：你的密码, 端⼝：3306，数据库：trustengine = create_engine("mysql+pymysql://root:password@localhost:3306/trust",encoding='utf-8') # 查询语句，
选出testexcel表中的所有数据
sql = """select goods_commonid,goods_name,goods_price from mall_goods_common where store_id=110 and set_new_time>1560182400;"""
# read_sql_query的两个参数: sql语句，数据库连接
df = pd.read_sql_query(sql,con=engine)
# 输出testexcel表的查询结果
print(df)
# 创建⼀个writer对象, ⾥⾯的参数是⼀个新的表格⽂件名
writer = pd.ExcelWriter('mydf.xlsx')
# 利⽤to_excel()⽅法将不同的数据框及其对应的sheet名称写⼊该writer对象中
<_excel(writer,sheet_name='test1',index=False)
# 数据写出到excel⽂件中，最后保存
writer.save()
4.pandas
# coding=utf-8
import pandas as pd
from pandas import DataFrame
from sqlalchemy import create_engine
import time
# 开始时间
start = time.time()
# 建⽴链接
engine = create_engine('mysql+pymysql://root:123@192.168.148.61:3306/ppx_mall_dev_db')
sql = '''select goods_commonid,goods_name,goods_price from mall_goods_common where store_id=110 and set_new_time>1560182400;''' # 读取mysql
df = pd.read_sql_query(sql, engine)
print("从mysql中读取数据成功！开始将数据导⼊到excel表格中...")
# 将读取的数据格式化成DataFrame类型
test_data = DataFrame.from_records(df)
# 将数据写⼊excle中
_excel("E:\\testdata.xlsx", index=False)
print("导出成功！")
# 程序结束时间
end = time.time()
# 打印出程序的运⾏时间
print('Running time: {} Seconds'.format(end - start))