前言
前几天接到一个业务的需求,让我把当前数据库里面的结果数据导出到excel中,然后供业务查看。问题是当前结果数据都是列式表,所以需要把数据做一个行列转换,但是业务还有一个需求,要求不同分类的数据展示不同的列并且需要导出到不同的sheet中。没办法,只能去思考怎么实现。
#博学谷IT学习技术支持#
数据大概长这样:
id |
category |
key |
value |
id1 |
分类1 |
属性1 |
值 |
id2 |
分类1 |
属性5 |
值 |
id3 |
分类2 |
属性2 |
值 |
id4 |
分类2 |
属性3 |
值 |
id5 |
分类3 |
属性1 |
值 |
id6 |
分类4 |
属性4 |
值 |
1.实现分析
方案一 建立一张大宽表
首先第一想到的是建立一张大宽表,把所有的属性都建上去,然后一把行列转换,把值都转换上去。最后把这个表的数据导出到excel上面。跟业务说了这个方法,他们表示不爽,这么宽的excel(大概600列)我怎么看呢,况且不给我分类,而且列里面肯定有很多的空值,因为不同的分类的属性都不一样。所以这个方法pass。
方案二 建立多张表
没办法,第二想到的是按照不同分类建不同的表,不同表里的字段不一样,跟分类的属性一致。然后写一个存储过程,循环不同的分类查出数据行列转换插入数据到不同的表中,然后把表的数据导出到excel中。但是我一数大概几百个分类,也就是说我要建几百张表,然后导出excel,算了还是作为备选方案。
方案三 使用脚本实现
这种情况我感觉只能写脚本,由于本人python只会helloworld,于是就去查资料,终于找到两个包:pandas、psycopg2。
了解到pandas是python数据分析的应用最广泛的包,然后psycopg2可以用来连接pg数据库。有了这两个包,然后业务逻辑写一下不就是我要的功能么。
2.实现过程
2.1安装环境
1.下载Anaconda,直接搜索官网:https://www.anaconda.com/,下载安装包,然后直接点击下一步,所有勾选项全部勾上,直到结束。这样之后,anaconda就会帮我们装好了python环境,conda包管理工具,最重要的是它帮我们装好了一大堆数据分析工具包,包括pandas。
2.然后打开电脑命令行cmd,输入pip install psycopg2,装好pg连接驱动包
备注:如果想新建一个环境,可以使用:conda create -n python36 python=3.6
命令新建一个python环境,然后activate python36
命令切换到这个环境下,安装包。
2.3功能逻辑
1.psycopg2连接pg数据库
class ADBPGClient(object):
def __init__(self, url, db, usr, pwd):
self._client = psycopg2.connect(
database=db
, user=usr
, password=pwd
, host=url
, port="5432"
)
self._cursor = self._client.cursor()
def query(self, sql):
self._cursor.execute(sql)
re_query = self._cursor.fetchall()
self._cursor.close()
return re_query
def close(self):
self._client.close()
2.pandas行列转换
conn = psycopg2.connect(conn_string)
df = pd.read_sql(pg_sql, conn)
dt = pd.pivot(df,index="id",columns="key",values="value")
3.pandas生成excel
dt.to_excel(writer, sheet_name=unicode(sheetname, "utf-8"))
2.4完整代码
有了上面的功能代码,只需要一个循环即可实现需求,循环分类,然后查询出table_result的不同分类的结果,不同的查询结果做一个行列转换然后写入到excel的不同sheet页中即可。
# coding=utf-8
import pandas as pd
import psycopg2
class ADBPGClient(object):
def __init__(self, url, db, usr, pwd):
self._client = psycopg2.connect(
database=db
, user=usr
, password=pwd
, host=url
, port="5432"
)
self._cursor = self._client.cursor()
def query(self, sql):
self._cursor.execute(sql)
re_query = self._cursor.fetchall()
self._cursor.close()
return re_query
def close(self):
self._client.close()
if __name__ == "__main__":
"""链接ADBPG使用的参数"""
pg_url = "主机名称"
pg_usr = "用户"
pg_pwd = "密码"
pg_db = "数据库"
postgres_port = "5432"
pg_cli = ADBPGClient(pg_url, pg_db, pg_usr, pg_pwd)
pg_sql = "select distinct category from table_result;"
pg_re = pg_cli.query(pg_sql)
pg_cli.close()
i = 0
writer = pd.ExcelWriter(u"汇总数据.xlsx")
for category in pg_re:
categoryStr = category[0] + ""
pg_cli = ADBPGClient(pg_url, pg_db, pg_usr, pg_pwd)
pg_sql = "select id,category ,key,value from table_result where category ='{0}' order by id;".format(
categoryStr)
conn_string = "host=" + pg_url + " port=" + postgres_port + " dbname=" + pg_db + " user=" + pg_usr + " password=" + pg_pwd
conn = psycopg2.connect(conn_string)
df = pd.read_sql(pg_sql, conn)
dt = pd.pivot(df,index="id",columns="key",values="value")
sheetname = str.replace(categoryStr,"/","-")
dt.to_excel(writer, sheet_name=unicode(sheetname, "utf-8"))
i = i + 1
print i
writer.save()
3.总结
有时候使用sql去做一些循环、迭代等逻辑或者动态列查询的需求不太好实现,这时候使用python的数据分析包去做就会简单很多,所以数据开发还是得学习下python。