import pymysql

class MY_SQL:

    def __init__(self):
        self.db = pymysql.connect('localhost', 'root', 'eagle', 'tushare', charset='utf8')

    # 写入分类表
    def save_cate_table(self, data):

        cursor = self.db.cursor()

        cursor.execute('DROP TABLE IF EXISTS t_cate')

        sql = '''
                CREATE TABLE `tushare`.`t_cate`  (
                  `id` int(0) NOT NULL AUTO_INCREMENT,
                  `code` varchar(255) NULL,
                  `name` varchar(255) NULL,
                  `cate` varchar(255) NULL,
                  PRIMARY KEY (`id`)
                );
                '''

        try:
            # 创建表
            cursor.execute(sql)

            for i in data.index:
                code = data.loc[i]['code']
                name = data.loc[i]['name']
                cate = data.loc[i]['c_name']

                sql = "INSERT INTO `t_cate`(`code`, `name`, `cate`) VALUES ('%s', '%s', '%s')" % (code, name, cate)

                cursor.execute(sql)
                self.db.commit()

        except Exception as e:
            self.db.rollback()
            print(e)

    # 按行业分类查询票
    def getListForCate(self, catename):
        cursor = self.db.cursor()

        sql = 'select code, name, cate from t_cate where cate = "' + catename +'"'

        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()

            # self.db.close()

            return results

        except Exception as e:
            self.db.rollback()
            print(e)

    # 写入实时行情
    def save_now_table(self, data):
        cursor = self.db.cursor()

        cursor.execute('DROP TABLE IF EXISTS t_now')

        sql = '''
                        CREATE TABLE `tushare`.`t_now`  (
                          `id` int(0) NOT NULL AUTO_INCREMENT,
                          `code` varchar(255) NULL,
                          `name` varchar(255) NULL,
                          `percent` varchar(255) NULL,
                          `price` varchar(255) NULL,
                          `turn` varchar(255) NULL,
                          PRIMARY KEY (`id`)
                        );
                        '''

        try:
            # 创建表
            cursor.execute(sql)

            for i in data.index:
                code = data.loc[i]['code']
                name = data.loc[i]['name']
                percent = data.loc[i]['changepercent']
                price = data.loc[i]['trade']
                turn = data.loc[i]['turnoverratio']

                sql = "INSERT INTO `t_now`(`code`, `name`, `percent`, `price`, `turn`) VALUES ('%s', '%s', '%s', '%s', '%s')" % (code, name, percent, price, turn)

                cursor.execute(sql)
                self.db.commit()

        except Exception as e:
            self.db.rollback()
            print(e)

    # 查询单票实时涨幅
    def getPercent(self, code):
        cursor = self.db.cursor()

        sql = 'select percent from t_now where code = "' + code + '"'

        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()

            # self.db.close()

            return results

        except Exception as e:
            self.db.rollback()
            print(e)

    # 清空历史表
    def clear_history(self):
        cursor = self.db.cursor()

        cursor.execute('DROP TABLE IF EXISTS t_history')

        sql = '''
                                CREATE TABLE `tushare`.`t_history`  (
                                  `id` int(0) NOT NULL AUTO_INCREMENT,
                                  `code` varchar(255) NULL,
                                  `name` varchar(255) NULL,
                                  `cate` varchar(255) NULL,
                                  `his_percent` double(255, 2) NULL,
                                  `his_date` varchar(255) NULL,
                                  PRIMARY KEY (`id`)
                                );
                                '''

        try:
            # 创建表
            cursor.execute(sql)

        except Exception as e:
            self.db.rollback()
            print(e)

    # 插入历史行情数据
    def insertHistory(self, code, name, cate, date, percent):
        cursor = self.db.cursor()

        try:

            sql = "INSERT INTO `t_history`(`code`, `name`, `cate`, `his_percent`, `his_date`) VALUES ('%s', '%s', '%s', '%s', '%s')" % (code, name, cate, percent, date)

            cursor.execute(sql)
            cursor.close()
            self.db.commit()

        except Exception as e:
            # self.db.rollback()
            print(e)

    # 查询历史数据
    def selectDay(self, code):
        cursor = self.db.cursor()

        sql = 'select code, name, cate, his_percent, his_date from t_history where code = "' + code + '"'

        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()

            return results

        except Exception as e:
            self.db.rollback()
            print(e)

    # 根据日期和行业查询历史数据
    def selectRelationStocks(self, day, cate):
        cursor = self.db.cursor()

        sql = 'select code from t_history where his_date = "' + day + '" and cate = "'+ cate +'"'

        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()

            return results

        except Exception as e:
            self.db.rollback()
            print(e)

    # 查询历史数据
    def selectCount(self, code):
        cursor = self.db.cursor()

        sql = 'select count(*) from t_history where code = "' + code + '"'

        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchone()

            return results

        except Exception as e:
            self.db.rollback()
            print(e)


    # 关闭数据库连接
    def closeDB(self):
        self.db.close()