python将数据写入excel_【Python】将数据库中的数据查询出来自动写入excel文档

本文介绍了如何使用Python连接数据库,执行SQL查询并将结果写入Excel文件。通过示例代码展示了如何创建数据库引擎,执行SQL查询,打开Excel工作表,将数据填充到指定区域,并进行了数据汇总和透视表分析,包括云闪付交易和服务费计算。
摘要由CSDN通过智能技术生成
近期每天都要监控一个数据。第一个版本是这样的:

6af90a1b7b242ebf893cd562865448ba.png

每天新增一个文档来汇总这个数据。这样搞了几天之后,过了一个周末,过来突然发现数据变多了很多,这个时候要调整策略,直接一个文档汇总出要的数据就可以了。

27adde0d193b881ce6009b853494765f.png

这样查了两天,我又嫌麻烦了,跟同事说了我的需求,问他能不能帮我用python搞成自动查结果然后把结果写到excel中的。他听完需求说这样是没有问题的。中午的时候他就把代码给我了。大概分这6部分:

d7f549b7a6574d913f0553e201668dfb.png

01

链接数据库

import pandas as pd

import xlwings as xw

from sqlalchemy import create_engine

pd.set_option('display.float_format',lambda x : '%.4f' % x)#到小数点后两位,完整显示

def engine():

    dbs=["yy_wr:DB@&0727Ydb..CN@192.168.1.165:5567/v2"]

    engine=create_engine(

        'mysql+pymysql://'+dbs[0],

        echo=False,

        pool_recycle=7200 

    )

    print("成功链接数据库")

    return engine

35c53ccb1f1151e0ecbd55b10fb23185.png

02

将数据库中的查询语句转译到python中

sql="""SELECT DATE_FORMAT(交易时间,'%d') 日期, 服务费,`商户服务费`,流量费,count(*),sum(交易金额) ,'非云闪付' 类别 from transaction_detail_202008 where `收单英文名称`='KQ_ZQ' 

and `业务产品` like '%%99%%' and `服务类型` not like '%%云闪付%%'

GROUP BY 1,2,3,4 union all

SELECT DATE_FORMAT(交易时间,'%d') 日期,  服务费,`商户服务费`,流量费,count(*),sum(交易金额),'云闪付' 类别 from transaction_detail_202008 where `收单英文名称`='KQ_ZQ' 

and `业务产品` like '%%99%%' and `服务类型` like '%%云闪付%%'

GROUP BY 1,2,3,4;"""

df=pd.read_sql(sql,con=engine())

【他给我的代码,在'%d'中的第一个百分号前还有个%,我这边查出来结果有问题,他那边是正常的,我删掉一个结果才符合预期】

2ab420c20bfc52c032d6ec416f5fdf56.png

03

打开指定的excel工作表新增工作表

app=xw.App()

df_data=app.books.open(r"E:\工作计划\周例会数据分享\2020.8.30\服务费收取数据源.xlsx")

df_data.sheets.add('newsource')

df_sheet=df_data.sheets('newsource')

9d2d3434ba5a6758ead6a0ffe5bae9bd.png

04

将数据库中查询出的结果写入指定的excel区域

df_sheet.range("a1").value="每日交易数据"

df_sheet.range("a1:g1").api.merge

df_sheet.range("a2").value=list(df.columns)

for i in range(3,len(df)+3):

    df_sheet.range("a"+str(i)).value=list(df.loc[i-3])

print("每日交易数据保存完成")

【merge函数后面的括号已删除,在同事那边没问题,在我这边报错NoneType,我删掉这个括号才算正常】

9bb0cf6e2eb84e605c50a65a189a9342.png

05

将查出的数据进行汇总处理【透视表】

pdf=df.pivot_table(values="sum(交易金额)",index="日期",columns="类别",aggfunc="sum")

b = pdf.T.agg('sum').T#右侧合计列

pdf['合计']=list(b)

a = pdf.agg('sum')#底部合计行

pdf.loc["合计"]=list(a)

pdf['云闪付占比']=(pdf['云闪付']/pdf['合计']).apply(lambda x:"%.2f%%" % (x * 100))

sever_amount=[]

sever_fee=[]

for  i in range(len(pdf)):

    sever_amount.append(df[(df['服务费']=='0.060%')&(df['日期']==pdf.index[i])].sum()['sum(交易金额)'])

    sever_fee.append(df[(df['服务费']=='0.060%')&(df['日期']==pdf.index[i])].sum()['sum(交易金额)']*0.0006)

pdf['收取服务费交易金额']=sever_amount

pdf['收取服务费金额']=sever_fee

pdf['收取服务费交易金额占比']=(pdf['收取服务费交易金额']/pdf['合计']).apply(lambda x:"%.2f%%" % (x * 100))

pdf['收取服务费交易金额占非云闪付交易比例']=(pdf['收取服务费交易金额']/pdf['非云闪付']).apply(lambda x:"%.2f%%" % (x * 100))

df_sheet.range("i1").value="云闪付交易"

df_sheet.range("i1:m1").api.merge

df_sheet.range("n1").value="服务费与占比"

df_sheet.range("n1:q1").api.merge

df_sheet.range("i2").value=pdf

print('服务费与云闪付数据保存完成')

【这一部分的处理比较复杂,运用到透视表,还运营到条件求和】

6d9c3aa088975280f0f561d235bb4622.png

06

保存&关闭文件,退出app,打印提示信息

df_data.save()#保存文件#文件如果是打开状态,不会报错   但更改不会起作用

df_data.close()#关闭文件关闭之后其他的才能操作#保存时会先检查是否已经存在,如果已经存在会有弹窗提醒

app.quit()

print("保存并关闭")

348800cae1d87b3b4588dfdde7982c8a.png

昨晚看了网易直播课的一堂课,用python去做量化投资。第一部分老师自我介绍【吹牛B】第二部分讲股票投资的一些指标计算方式第三部分用python和yahoofinance中的数据进行投资收益测算

90514318cc92d070d1c13ed38251c6b9.png

感悟:了解了量化投资是个什么玩意:用算法和指标让程序去帮忙买入卖出。昨天的课程没有太复杂的算法,只需要用工具把一系列的数据算出来。只是根据设置的条件去买进卖出。这种投资有胜有负,昨天的BTC模拟数据是9胜10负,但是从年初到现在的收益率已经到了89%。

0d139454453e7b95b4f4227cfd5849d6.png

投资要克服人性中的贪婪。在投资中,下跌是必然【如同地心引力】,上涨需要集聚很多的能量。如果人在操作股票的时候能够死守规则,应该也不至于被割韭菜割得肉都割掉了。
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值