mysql&python 动态 统计年/月/日数据

根据某字段动态统计年/月/日数据

依赖的包

Click==7.0 PyMySQL==0.9.3

数据示例

config.json

{
  "test": {
    "mysql": {
      "host": "192.168.7.2",
      "user": "root",
      "password": "password",
      "db": "tests",
      "port": 3310,
      "charset": "utf8"
    }
  },
  "prd": {
    "mysql": {
      "host": "localhost",
      "user": "root",
      "password": "password",
      "db": "tests",
      "port": 3306,
      "charset": "utf8"
    }
  }
}

代码

import json,pymysql,click

class Count(object):

    base_sql = """
select {select},sum(value) as sum_value
from tests
group by {select}
"""
    def __init__(self,config):
        self.mysql_config = config
        self.conn = pymysql.connect(**self.mysql_config)
        self.cursor = self.conn.cursor()

    def _execute(self,sql,*args):
        self.cursor.execute(sql,*args)
        datas  = self.cursor.fetchall()
        self.cursor.close()
        self.conn.close()
        return datas


    @classmethod
    def from_settings(cls,config):
        return cls(config)


    def get_year(self):
        select = 'substr(kprq,1,4)'
        sql = self.base_sql.format(select=select)
        return self._execute(sql)

    def get_month(self):
        select = 'substr(kprq,1,7)'
        sql = self.base_sql.format(select=select)
        return self._execute(sql)

    def get_day(self):
        select = 'substr(kprq,1,10)'
        sql = self.base_sql.format(select=select)
        return self._execute(sql)


    @classmethod
    def run(cls,config):
        for arg in ('year','month','day'):
            attr = 'get_' + arg
            data = getattr(cls.from_settings(config),attr)()
            print('attr:{}---data:{}'.format(attr,data))

@click.command()
@click.option('--mode', help='local/test/prd')
def main(mode):
    with open('config.json','r',encoding='utf-8') as fr:
        config = json.load(fr)[mode]['mysql']
        Count.run(config)
if __name__ == '__main__':
    main()