使用python的pandas包查询数据库数据导出到excel

前言

前几天接到一个业务的需求，让我把当前数据库里面的结果数据导出到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。