import pymysql
from datetime import datetime, timedelta
import time


class MyDB:
    def __init__(self, host, user, passwd, db):
        self.conn = pymysql.connect(host, user, passwd, db)
        self.cursor = self.conn.cursor()

    def __del__(self):
        self.conn.close()

    # 获取当然日期
    def get_cur_date(self):
        date = datetime.today()
        curdate = date.strftime('%Y-%m-%d')
        return curdate

    # 获取前N天的日期
    def get_pren_date(self, n=1):
        predate = datetime.today() + timedelta(-n)
        predate = predate.strftime('%Y-%m-%d')
        return predate

    # 获取现有确诊人数最多的5个省份
    def get_province_curconfirm_top5(self):
        curdate = self.get_cur_date() # 获取当天的日期
        sql = "select provinceName,currentConfirmedCount,pub_date from province_daily_datas where pub_date like '%s' order by currentConfirmedCount desc limit 5" % (curdate + '%')
        print('+++ sql: %s' % sql)

        results = []
        try:
            self.cursor.execute(sql)
            results = self.cursor.fetchall()

            n = 1

            while len(results) <= 0:
                predate = self.get_pren_date(n)
                sql = "select provinceName,currentConfirmedCount,pub_date from province_daily_datas where pub_date like '%s' order by currentConfirmedCount desc limit 5" % (predate + '%')
                print('+++ presql: %s' % sql)

                self.cursor.execute(sql)
                results = self.cursor.fetchall()
                n += 1

                if n >= 30:
                    break
                else:
                    time.sleep(1)

        except Exception as e:
            print(e)
        #print(results)
        return results


    #获取当前确诊人数前十的国家
    def get_country_curconfirm_top10(self):
        curdate = self.get_cur_date() # 获取当天的日期
        sql = "select country,curConfirm,pub_date from foreign_datas where pub_date like '%s' order by curConfirm desc limit 10" % (curdate + '%')
        print('+++ sql: %s' % sql)

        results = []
        try:
            self.cursor.execute(sql)
            results = self.cursor.fetchall()

            n = 1

            while len(results) <= 0:
                predate = self.get_pren_date(n)
                sql = "select country,curConfirm,pub_date from foreign_datas where pub_date like '%s' order by curConfirm desc limit 10" % (predate + '%')
                print('+++ presql: %s' % sql)

                self.cursor.execute(sql)
                results = self.cursor.fetchall()
                n += 1

                if n >= 30:
                    break
                else:
                    time.sleep(1)

        except Exception as e:
            print(e)
       # print(results)
        return results

    # 获取最新一天每个省份的现有确诊数量
    def get_province_curConfirms(self):
        curdate = self.get_cur_date()  # 获取当天的日期
        sql = 'select provinceshortName,currentConfirmedCount,pub_date from province_daily_datas where pub_date like "%s"' % (curdate + '%')
        print('+++ sql: %s' % sql)
        results = []
        try:
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
            n = 1

            while len(results) <= 0:
                predate = self.get_pren_date(n)
                sql = 'select provinceshortName,currentConfirmedCount,pub_date from province_daily_datas where pub_date like "%s"' % (predate + '%')
                print('+++ sql: %s' % sql)
                self.cursor.execute(sql)
                results = self.cursor.fetchall()

                n += 1

                if n >= 15:
                    break
                else:
                    time.sleep(1)

        except Exception as e:
            print(e)
        #print(results)
        return results

    # 获取最新一天每个国家的现有确诊数量
    def get_country_curConfirms(self):
        curdate = self.get_cur_date()  # 获取当天的日期
        sql = 'select country,curConfirm,pub_date from foreign_datas where pub_date like "%s"' % (curdate + '%')
        print('+++ sql: %s' % sql)
        results = []
        try:
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
            n = 1

            while len(results) <= 0:
                predate = self.get_pren_date(n)
                sql = 'select country,curConfirm,pub_date from foreign_datas where pub_date like "%s"' % (predate + '%')
                print('+++ sql: %s' % sql)
                self.cursor.execute(sql)
                results = self.cursor.fetchall()

                n += 1

                if n >= 15:
                    break
                else:
                    time.sleep(1)

        except Exception as e:
            print(e)
        #print(results)
        return results

    # 获取最新一天广西的现有确诊数量
    def get_guangxi_curConfirms(self):
        curdate = self.get_cur_date()  # 获取当天的日期
        sql = 'select cityName,confirmedCount,pub_date from city_daily_datas where province="广西" and pub_date like "%s"' % (curdate + '%')
        print('+++ sql: %s' % sql)
        results = []
        try:
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
            n = 1

            while len(results) <= 0:
                predate = self.get_pren_date(n)
                sql = 'select cityName,confirmedCount,pub_date from city_daily_datas where province="广西" and pub_date like "%s"' % (predate + '%')
                print('+++ sql: %s' % sql)
                self.cursor.execute(sql)
                results = self.cursor.fetchall()

                n += 1

                if n >= 15:
                    break
                else:
                    time.sleep(1)

        except Exception as e:
            print(e)
        #print(results)
        return results

    # 获取最新一天河南的现有确诊数量
    def get_henan_curConfirms(self):
        curdate = self.get_cur_date()  # 获取当天的日期
        sql = 'select cityName,curedCount,pub_date from city_daily_datas where province="河南" and pub_date like "%s"' % (curdate + '%')
        print('+++ sql: %s' % sql)
        results = []
        try:
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
            n = 1

            while len(results) <= 0:
                predate = self.get_pren_date(n)
                sql = 'select cityName,curedCount,pub_date from city_daily_datas where province="河南" and pub_date like "%s"' % (predate + '%')
                print('+++ sql: %s' % sql)
                self.cursor.execute(sql)
                results = self.cursor.fetchall()

                n += 1

                if n >= 15:
                    break
                else:
                    time.sleep(1)

        except Exception as e:
            print(e)
        #print(results)
        return results

    # 获取现有确诊人数最多的5个省份
    def get_province_top5(self):
        curdate = self.get_cur_date() # 获取当天的日期
        sql = "select provinceName,currentConfirmedCount,confirmedCount,curedCount,pub_date from province_daily_datas where pub_date like '%s' order by currentConfirmedCount desc limit 5" % (curdate + '%')
        print('+++ sql: %s' % sql)

        results = []
        try:
            self.cursor.execute(sql)
            results = self.cursor.fetchall()

            n = 1

            while len(results) <= 0:
                predate = self.get_pren_date(n)
                sql = "select provinceName,currentConfirmedCount,confirmedCount,curedCount,pub_date from province_daily_datas where pub_date like '%s' order by currentConfirmedCount desc limit 5" % (predate + '%')
                print('+++ presql: %s' % sql)

                self.cursor.execute(sql)
                results = self.cursor.fetchall()
                n += 1

                if n >= 30:
                    break
                else:
                    time.sleep(1)

        except Exception as e:
            print(e)
        #print(results)
        return results

    # 获取现有确诊人数最多的10个国家的信息
    def get_country_top10(self):
        curdate = self.get_cur_date() # 获取当天的日期
        sql = "select country,curConfirm,confirmed,crued,pub_date from foreign_datas where pub_date like '%s' order by curConfirm desc limit 10" % (curdate + '%')
        print('+++ sql: %s' % sql)

        results = []
        try:
            self.cursor.execute(sql)
            results = self.cursor.fetchall()

            n = 1

            while len(results) <= 0:
                predate = self.get_pren_date(n)
                sql = "select country,curConfirm,confirmed,crued,pub_date from foreign_datas where pub_date like '%s' order by curConfirm desc limit 10" % (predate + '%')
                print('+++ presql: %s' % sql)

                self.cursor.execute(sql)
                results = self.cursor.fetchall()
                n += 1

                if n >= 30:
                    break
                else:
                    time.sleep(1)

        except Exception as e:
            print(e)
        #print(results)
        return results

'''
if __name__ == "__main__":
    mydb = MyDB('localhost', 'root', 'Aa183365', 'covid19_datas')
    results = mydb.get_province_curconfirm_top5() # results : []
    print(type(results[0]))
'''