import pymysql
import redis
 
 
class loadRedis():
    def __init__(self):
        self.host = '10.157.122.26'
        self.port = 8113
 
    def writeRedis(self,key,value):
        pool = redis.ConnectionPool(host=self.host, port=self.port, decode_responses=True)
        r = redis.Redis(connection_pool=pool)
        r.set(key,value,20)
        print(r[key])
 
 
class loadMysql():
    def __init__(self, conn):
        self.conn = conn
 
    def readMysql(self):
        # 打開數(shù)據(jù)庫連接
 
        # 使用cursor()方法獲取操作游標(biāo)
        cursor = self.conn.cursor()
 
        # 使用execute方法執(zhí)行SQL語句
        sql='''select count( DISTINCT user_id) from insur_policy_info where  policy_state in (1,2) and date_format(modify_time,"%y-%m-%d") <= date_sub(date_sub(date_format(now(),"%y-%m-%d"),interval extract(
    day from now()) day),interval 0 month)'''
        cursor.execute(sql)
 
        # 使用 fetchone() 方法獲取一條數(shù)據(jù)
        data = cursor.fetchone()
        print("user count : %s " % data)
 
        # 關(guān)閉數(shù)據(jù)庫連接
        cursor.close()
        return data
 
if __name__ == "__main__":
    conn_sql = pymysql.connect(
        host='10.157.122.26',
        port=8085,
        user='root',
        passwd='MiraCle',
        db='insur_core',
        charset='utf8'
    )
    get_data = loadMysql(conn_sql);
 
    data = get_data.readMysql()[0]
    get_redis = loadRedis();
    get_redis.writeRedis("month_report", data);
    print('finished')

import pymysql
import redis
if __name__ == "__main__":
    host = '10.157.122.26'
    port = 8113
    pool = redis.ConnectionPool(host=host, port=port, decode_responses=True)
    r = redis.Redis(connection_pool=pool)
    conn_sql = pymysql.connect(
        host='10.157.122.26',
        port=8085,
        user='root',
        passwd='MiraCle',
        db='insur_core',
        charset='utf8'
    )
    cursor = conn_sql.cursor();
    sql = ''' select user_id,count(1) from insur_policy_info where policy_state in (1,2) and  date_format(modify_time,"%y-%m-%d") <= date_sub(date_sub(date_format(now(),"%y-%m-%d"),interval extract(
    day from now()) day),interval 0 month) group by user_id;'''
    cursor.execute(sql)
    data = cursor.fetchmany(10);
    for d in data:
        #print("month_report_"+str(d[0]))
        #用戶的有效保單
        r.set("month_report_user_valid_policy_count_"+str(d[0]),d[1],10)
        print(d[1])
        i=0
        for d2 in data:
            if d[1] > d2[1]:
                i+=1
        r.set("greater_user_policy_count_"+str(d[0]),i,10)
        print("用戶的有效保單數(shù)量 % s" % r.get("month_report_user_valid_policy_count_"+str(d[0])))
        print("大于用戶保單數(shù)的用戶數(shù) % s" % r.get("greater_user_policy_count_" + str(d[0])))