# -*- coding:utf8 -*-

from pyquery import PyQuery as pq
import requests
import pymysql

def getLastYear(sql):
    db = pymysql.connect('localhost', 'root', 'eagle', 'db_4dd', charset='utf8')
    cursor = db.cursor()
    try:
        cursor.execute(sql)

        return cursor.fetchall()
    except Exception as e:
        db.rollback()
        print(e)
        return ''


def start(sort):
    # 查2018年A位数据
    sql1 = '''
        SELECT a, count(*) as num FROM (
            SELECT * FROM t_history WHERE period LIKE '2019%' ORDER BY createTime DESC
            ) t  GROUP BY a ORDER BY num DESC;
    '''
    sql3 = '''
            SELECT c, count(*) as num FROM (
                SELECT * FROM t_history WHERE period LIKE '2019%' ORDER BY createTime DESC
                ) t  GROUP BY c ORDER BY num DESC;
        '''

    aSort = getLastYear(sql1)[0:sort]
    cSort = getLastYear(sql3)[0:sort]
    a = []
    for i in aSort:
        a.append(i[0])

    c = []
    for i in cSort:
        c.append(i[0])
    print(a)
    print(c)

    sqlLast = '''
        SELECT * FROM t_history WHERE period LIKE '2020%' ORDER BY createTime DESC;
    '''

    last = getLastYear(sqlLast)
    result = []
    for i in last:
        if i[2] in a and i[4] in c:
            print(i)
            result.append(i)

    print(len(result))


start(7)