import time
import threading
import pymysql as psql

class DBQuery(threading.Thread):
    def __init__(self,ip,port,database,user,password):
        threading.Thread.__init__(self)
        self.ip=ip
        self.port=port
        self.database=database
        self.user=user
        self.password=password
        self.conn=psql.connect(host=self.ip,port=self.port,database=self.database,charset="utf8",user=self.user,passwd=self.password)
        self.unit_cmd_1=None
        self.unit_cmd_2=None
        self.unit_cmd_3=None
        self.isClose = False
        self.callback = None
    def Close(self):
        self.isClose = True
    def setCallback(self,callback):
        self.callback = callback
    def connect(self):
        try:
            self.conn = psql.connect(host=self.ip, port=self.port, database=self.database, charset="utf8",
                                        user=self.user, passwd=self.password)
            return True
        except:
            return False
    def run(self):
        while self.isClose is False:
            try:
                self.conn.ping()  # 采用连接对象的ping()函数检测连接状态
            except:
                self.connect()
                time.sleep(0.5)
                continue
            #获取一个光标
            cursor = self.conn.cursor()
            #查询指令队列所有信息
            SQL1="select * from command_queue where unit = 1 and type = 2 order by queue_id ASC;"
            SQL2="select * from command_queue where unit = 2 and type = 2 order by queue_id ASC;"
            SQL3="select * from command_queue where unit = 3 and type = 2 order by queue_id ASC;"
            cmd_dict1 = {}
            cmd_dict2 = {}
            cmd_dict3 = {}

            #执行语句 返回结果数量
            command_count=cursor.execute(SQL1)
            self.conn.commit()
            #结果数量大于0
            if(command_count > 0):
                queue_cmd=cursor.fetchall()
                column=[index[0] for index in cursor.description  ]# 列名
                cmd_dict1 = [dict(zip(column, row)) for row in queue_cmd]  # row是数据库返回的一条一条记录，其中的每一天和column写成字典，最后就是字典数组

            command_count=cursor.execute(SQL2)
            self.conn.commit()
            #结果数量大于0
            if(command_count > 0):
                queue_cmd=cursor.fetchall()
                column=[index[0] for index in cursor.description  ]# 列名
                cmd_dict2 = [dict(zip(column, row)) for row in queue_cmd]  # row是数据库返回的一条一条记录，其中的每一天和column写成字典，最后就是字典数组

            command_count=cursor.execute(SQL3)
            self.conn.commit()
            #结果数量大于0
            if(command_count > 0):
                queue_cmd=cursor.fetchall()
                column=[index[0] for index in cursor.description  ]# 列名
                cmd_dict3 = [dict(zip(column, row)) for row in queue_cmd]  # row是数据库返回的一条一条记录，其中的每一天和column写成字典，最后就是字典数组

            if(cmd_dict1 != self.unit_cmd_1):
                self.unit_cmd_1 = cmd_dict1.copy()
                if self.callback is not None and len(self.unit_cmd_1) != 0:
                    self.callback(self.unit_cmd_1,1)

            if(cmd_dict2 != self.unit_cmd_2):
                self.unit_cmd_2 = cmd_dict2.copy()
                if self.callback is not None and len(self.unit_cmd_2) != 0:
                    self.callback(self.unit_cmd_2,2)
            if(cmd_dict3 != self.unit_cmd_3):
                self.unit_cmd_3 = cmd_dict3.copy()
                if self.callback is not None and len(self.unit_cmd_3) != 0:
                    self.callback(self.unit_cmd_3,3)
            # 关闭光标
            cursor.close()
            time.sleep(1)