Python 读取数据库生成excel

发布于 2021-05-13 21:40 ，所属分类：数据库和大数据技术学习资料

来首外国洗脑神曲提提神。干IT是最最无趣的，生活需要多点色彩。

刚洗完澡哈，每次洗完澡后文思泉涌，码代码那叫一个水到渠成，根本停不下来。
读圣贤书，所学何事?而今而后，庶几无愧。
写代码是为了啥？当然是为了偷懒。
哈哈，不好意思说漏嘴，当然是为了解决实际痛点。
最近有点痛，不对，有痛点。简单表达一下。领导让每月从门禁系统(最近‘海康威视’盘的有点多，我也觉得烦)拉个报表：每月（上月26日-本月25日）所有人员，每日的最早、最晚刷卡时间。本来很简单个事，可是我们没有使用海康威视的考勤模块。系统只能将每月所有人员的刷卡记录导成excel。如果能一次性导出来，也不太麻烦，还可以忍。但是海康TMD限定一次只能导6000条记录。这样假设一个月按50万条记录算，我得导83.33333333...次。导完还要对83张表格各种骚操作。是可忍，踏马还有什么不能忍。
那就写个SQL导出来吧。
一、表分析。先随便找了张表-2019年11月的，需要的字段很简单，人员姓名，事件产生时间（刷卡时间），部门（可选）。SQL很简单，这里提一下，event_time::date是将datetime取日期部分，然后按人名、日期进行分组。

SELECT person_name as 姓名,event_time::date as "日期",min(event_time)as"到达",max(event_time)as"离开"FROM"ac_event_201911"whereperson_id>0groupbyperson_name,event_time::date
之前说过，海康的门禁刷卡记录是按月分割的，我们需要的数据是跨月的，那就需要将前后两个月的表合并(union)一下，再加一个日期条件，修改如下：
SELECT person_name as "姓名",event_time::date as "日期",min(event_time) as "到达",max(event_time) as "离开" FROM "ac_event_201911" where person_id>0 and event_time<'2019-11-26'  group by person_name,event_time::date unionSELECT person_name as "姓名",event_time::date as "日期",min(event_time) as "到达",max(event_time) as "离开" FROM "ac_event_201910" where person_id>0 and event_time>'2019-10-25' group by person_name,event_time::date order by "姓名","日期" asc
查询结果如下：

查询时间0.45秒，手动搞怎么也得半天吧。

当然这还不算完，本来想用pgsql的copy导出csv。但是中文全是乱码，查了一下，没搞定。还是用python连接数据库，pandas导出excel。
使用示例：gg("前面那段Sql(注意表名取当月","11.xlsx)
import pandas as pdimportpsycopg2
def gg(query,file):conn=psycopg2.connect(database="cmsdb",user="",password="",host="",port="")#用户名，密码根据实际填    df = pd.read_sql_query(query, conn)    df.to_excel(file, index=False)
最终结果如下，还不错。当然一个问题解决，新的问题来了。如果上的夜班（凌晨下班），这个表就有问题了。太晚了，下回研究。


相关资源