loca_conn.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "import os\n",
    "import json\n",
    "import datetime\n",
    "import random\n",
    "from shutil import copyfile,make_archive, rmtree\n",
    "import fault_tolerance\n",
    "\n",
    "'''\n",
    "clear database\n",
    "\n",
    "use se_proj;\n",
    "update text_data set final_labelid = NULL where  dataid>0;\n",
    "update users set nb_answer =0 where userid>0;\n",
    "update users set nb_accept =0 where userid>0;\n",
    "update users set credits =0 where userid>0;\n",
    "DELETE FROM text_label where labelid>0;\n",
    "update source set nb_finished = 0 where sourceid>0;\n",
    "\n",
    "'''\n",
    "\n",
    "\n",
    "def get_timestamp():\n",
    "    return float(\"{0:.2f}\".format(time.time()))\n",
    "    # return strftime(\"%Y-%m-%d %H:%M:%S\", gmtime())\n",
    "\n",
    "def timestamp_ealier_than(begin, days):\n",
    "    return int((begin - datetime.timedelta(days=days)).strftime(\"%s\"))\n",
    "\n",
    "class sql_conn:\n",
    "    def __init__(self, conn):\n",
    "        self.conn = conn\n",
    "        self.cursor = conn.cursor()\n",
    "        self.ft_params = {'init_acc':0.5, 'nb_bel_ratio':1, 'threshold':{'off':0,'low':0.51, 'high':0.8}}\n",
    "        self.__ft_degree_dict = {0:'off',1:'low',2:'high'}\n",
    "\n",
    "    def __search_user_by_name(self, username):\n",
    "        self.cursor.execute(\"select * from users where username='{}';\".format(username))\n",
    "        return self.cursor.fetchone()  # None if it doesn't exist\n",
    "\n",
    "    def __search_user_by_id(self, userid):\n",
    "        self.cursor.execute(\"select * from users where userid={};\".format(userid))\n",
    "        return self.cursor.fetchone()  # None if it doesn't exist\n",
    "\n",
    "    def __search_admin_by_name(self, adminname):\n",
    "        result = self.cursor.execute(\"select * from admin where adminname='{}';\".format(adminname))\n",
    "        return self.cursor.fetchone()  # None if it doesn't exist\n",
    "\n",
    "    def __search_source_by_name(self, sourcename):\n",
    "        result = self.cursor.execute(\"select * from source where sourcename='{}';\".format(sourcename))\n",
    "        return self.cursor.fetchone()  # None if it doesn't exist\n",
    "\n",
    "    def __search_source_by_id(self, sourceid):\n",
    "        result = self.cursor.execute(\"select * from source where sourceid='{}';\".format(sourceid))\n",
    "        return self.cursor.fetchone()  # None if it doesn't exist\n",
    "\n",
    "    def __insertion(self, sql):\n",
    "        try:\n",
    "            self.cursor.execute(sql)\n",
    "            self.conn.commit()\n",
    "            return 1\n",
    "        except:\n",
    "            return -1\n",
    "\n",
    "    def __exe_sql(self, sql):\n",
    "        try:\n",
    "            self.cursor.execute(sql)\n",
    "            return self.cursor.fetchall()\n",
    "        except:\n",
    "            return None\n",
    "\n",
    "    def __get_by_option(self, tablename, target_col, col_val_dict, head=True):\n",
    "        # search by one of the condition\n",
    "        try:\n",
    "            for col in col_val_dict.keys():\n",
    "                if col_val_dict[col] != None:\n",
    "                    pkcol = col\n",
    "            self.cursor.execute(\n",
    "                \"select {} from {} where {}='{}';\".format(target_col, tablename, pkcol, col_val_dict[pkcol]))\n",
    "            if head:\n",
    "                return self.cursor.fetchone()[0]\n",
    "            else:\n",
    "                return self.cursor.fetchone()\n",
    "        except:\n",
    "            return None\n",
    "\n",
    "    def __get_by_mul_cond(self, tablename, target_col, col_val_dict, fetchone=False):\n",
    "        # search by multiple conditions\n",
    "        try:\n",
    "            sql = \"select {} from {} where \".format(target_col, tablename)\n",
    "            for i, col in enumerate(col_val_dict.keys()):\n",
    "                if i != 0:\n",
    "                    sql += \" and \"\n",
    "                sql += \"{} = '{}'\".format(col, col_val_dict[col])\n",
    "\n",
    "            sql += \";\"\n",
    "            # print(sql)\n",
    "            self.cursor.execute(sql)\n",
    "            if fetchone:\n",
    "                return self.cursor.fetchone()[0]\n",
    "            else:\n",
    "                return self.cursor.fetchall()\n",
    "        except:\n",
    "            return None\n",
    "    def __set_col_addup(self, tablename, target_col, cond_col, cond, addoffset):\n",
    "        # cond can be either value or tuple\n",
    "        try:\n",
    "            sql = \"update {} set {}={}+{} where {} \".format(tablename, target_col, target_col, addoffset,cond_col)\n",
    "            if type(cond) is int:\n",
    "                sql += \"={};\".format(cond)\n",
    "            elif type(cond) is str:\n",
    "                sql += \"='{}';\".format(cond)\n",
    "            elif type(cond) is tuple:\n",
    "                sql += \"in {};\".format(cond)\n",
    "            print(sql)\n",
    "            return self.__insertion(sql) #return -1 if execution fail\n",
    "        except:\n",
    "            return 0\n",
    "        \n",
    "    def __set_col(self, tablename, target_col, cond_col, cond, value):\n",
    "        # cond can be either value or tuple\n",
    "        try:\n",
    "            sql = \"update {} set {}={} where {} \".format(tablename, target_col, value, cond_col)\n",
    "            if type(cond) is int:\n",
    "                sql += \"={};\".format(cond)\n",
    "            elif type(cond) is str:\n",
    "                sql += \"='{}';\".format(cond)\n",
    "            elif type(cond) is tuple:\n",
    "                sql += \"in {};\".format(cond)\n",
    "            print(sql)\n",
    "            return self.__insertion(sql) #return -1 if execution fail\n",
    "        except:\n",
    "            return 0\n",
    "\n",
    "    def get_by_cond_tuple(self, tablename, target_col, cond_col, tup):\n",
    "        try:\n",
    "            sql = \"select {} from {} where {} in {};\".format(target_col, tablename, cond_col, tup)\n",
    "            print(sql)\n",
    "            self.cursor.execute(sql)\n",
    "            return self.cursor.fetchall()\n",
    "        except:\n",
    "            return None\n",
    "    # user*******************************************************************************\n",
    "    def get_all_user(self):\n",
    "        user_list = self.__exe_sql(\"select * from users;\")\n",
    "        result = []\n",
    "        for user in user_list:\n",
    "            \n",
    "            [user_id, user_email, user_name, password, signup_time, user_credit, nb_answer, nb_accept, _,_] = user\n",
    "            acc =  nb_accept/nb_answer if nb_answer!=0 else 0\n",
    "            result.append({\"user_email\": user_email, \"user_name\": user_name, \"signin_date\":signup_time, \n",
    "                           \"user_credit\": user_credit, \"nb_answer\": nb_answer, \"acc\":acc})\n",
    "        return result\n",
    "    \n",
    "    \n",
    "    def get_user(self, userid=None, username=None, user_email=None):\n",
    "        return self.__get_by_option('users', '*', {'userid': userid, 'username': username, 'email_address': user_email},\n",
    "                                    head=False)\n",
    "\n",
    "    def get_user_number(self):\n",
    "        return self.__exe_sql(\"select count(*) from users;\")[0][0]\n",
    "\n",
    "    def get_user_id(self, username=None, user_email=None):\n",
    "        return self.__get_by_option('users', 'userid', {'username': username, 'email_address': user_email})\n",
    "\n",
    "    def get_user_passwd(self, userid=None, username=None, user_email=None):\n",
    "        return self.__get_by_option('users', 'password',\n",
    "                                    {'userid': userid, 'username': username, 'email_address': user_email})\n",
    "\n",
    "    def get_user_email(self, userid=None, username=None):\n",
    "        return self.__get_by_option('users', 'email_address', {'userid': userid, 'username': username})\n",
    "\n",
    "    def get_user_name(self, userid=None, user_email=None):\n",
    "        return self.__get_by_option('users', 'username', {'userid': userid, 'email_address': user_email})\n",
    "\n",
    "    def get_user_credit(self, userid=None, username=None, user_email=None):\n",
    "        return self.__get_by_option('users', 'credits',\n",
    "                                    {'userid': userid, 'username': username, 'email_address': user_email})\n",
    "\n",
    "    def get_user_nb_accept(self, userid=None, username=None, user_email=None):\n",
    "        return self.__get_by_option('users', 'nb_accept',\n",
    "                                    {'userid': userid, 'username': username, 'email_address': user_email})\n",
    "\n",
    "    def get_user_nb_answer(self, userid=None, username=None, user_email=None):\n",
    "        return self.__get_by_option('users', 'nb_answer',\n",
    "                                    {'userid': userid, 'username': username, 'email_address': user_email})\n",
    "\n",
    "    def get_user_nb_val(self, userid=None, username=None, user_email=None):\n",
    "        return self.__get_by_option('users', 'nb_val',\n",
    "                                    {'userid': userid, 'username': username, 'email_address': user_email})\n",
    "\n",
    "    def get_user_nb_val_tp(self, userid=None, username=None, user_email=None):\n",
    "        return self.__get_by_option('users', 'nb_val_tp',\n",
    "                                    {'userid': userid, 'username': username, 'email_address': user_email})\n",
    "\n",
    "    def get_user_val_acc(self, userid=None, username=None, user_email=None):\n",
    "        total = self.get_user_nb_val(userid, username, user_email)\n",
    "        if total != 0:\n",
    "            return self.get_user_nb_val_tp(userid, username, user_email) / total\n",
    "        else:\n",
    "            return 0\n",
    "        \n",
    "    def set_user_nb_answer(self, userid, addoffset=1):\n",
    "        # return : **1** success; **0** fail; **-1** fail\n",
    "        return self.__set_col_addup('users', 'nb_answer', 'userid', userid, addoffset)\n",
    "    \n",
    "    def set_user_nb_accept(self, userid, addoffset=1):\n",
    "        return self.__set_col_addup('users', 'nb_answer', 'userid', userid, 1)\n",
    "\n",
    "    def get_user_accept_rate(self, userid=None, username=None, user_email=None):\n",
    "        total = self.get_user_nb_answer(userid, username, user_email)\n",
    "        if total != 0:\n",
    "            return self.get_user_nb_accept(userid, username, user_email) / self.get_user_nb_answer(userid, username,\n",
    "                                                                                                   user_email)\n",
    "        else:\n",
    "            return 0\n",
    "\n",
    "\n",
    "    def insert_user(self, username, user_email, passwd, signin_time=get_timestamp(), credits=0, nb_accept=0,\n",
    "                    nb_answer=0):\n",
    "        # insertion: 1 success, 0: already exist, -1: fail\n",
    "        if self.__search_user_by_name(username) == None:\n",
    "            sql = \"INSERT INTO `se_proj`.`users` (`username`,`email_address`,`password`,`signin_date`,`credits`) VALUES ('{}','{}','{}','{}','{}');\" \\\n",
    "                .format(username, user_email, passwd, signin_time, credits)\n",
    "            return self.__insertion(sql)\n",
    "        else:\n",
    "            return 0\n",
    "\n",
    "    def user_exist(self, userid=None, username=None, user_email=None):\n",
    "        result = self.__get_by_option('users', '*',\n",
    "                                      {'userid': userid, 'username': username, 'email_address': user_email})\n",
    "        return True if result != None else False\n",
    "    \n",
    "    def get_user_credits_rank(self, user_email):\n",
    "        userid = self.get_user_id(user_email=user_email)\n",
    "        sql = \"select i.rank from \\\n",
    "        (SELECT u.userid,\\\n",
    "        @curRank := @curRank + 1 AS rank  FROM  se_proj.users u, (SELECT @curRank := 0) r \\\n",
    "        ORDER BY  u.credits desc ) i where i.userid={};\".format(userid)\n",
    "        return int(self.__exe_sql(sql)[0][0])\n",
    "\n",
    "    \n",
    "    def get_user_source(self, user_email):\n",
    "        userid = self.get_user_id(user_email=user_email)\n",
    "        return self.__exe_sql(\"select distinct(td.datasource) from text_label tl \\\n",
    "        join text_data td on td.dataid=tl.dataid join users u  on tl.userid= u.userid where u.userid={};\".format(userid))\n",
    "    \n",
    "    def get_user_mainpage_pan(self, user_email):\n",
    "        result = {1:0,2:0,3:0}\n",
    "        try:\n",
    "            # return [(priority, count), (priority, count), ...]\n",
    "            userid = self.get_user_id(user_email=user_email)\n",
    "            sql = \"select x.priority, count(*) from \\\n",
    "            (select  distinct(td.datasource), s.priority from text_label tl \\\n",
    "            join text_data td on tl.dataid=td.dataid join source s on s.sourceid = td.datasource \\\n",
    "            where userid={}) x group by x.priority;\".format(userid)\n",
    "            tup =  self.__exe_sql(sql)\n",
    "            for t in tup:\n",
    "                result[t[0]]=t[1]\n",
    "            return result\n",
    "        except:\n",
    "            return result\n",
    "    \n",
    "    def __user_label_later_number(self, ts_list, tr):\n",
    "        cnt = 0\n",
    "        for ts in ts_list:\n",
    "            if ts[0]>tr:\n",
    "                cnt += 1\n",
    "        return cnt\n",
    "    def get_user_mainpage_pan_history(self, user_email):\n",
    "        userid = self.get_user_id(user_email=user_email)\n",
    "        sql = \"select labeldate from text_label where userid={} order by labeldate desc;\".format(userid) \n",
    "        ts_list = self.__exe_sql(sql)\n",
    "        days_list = [1,3,7,30]\n",
    "        result = []\n",
    "        now = datetime.datetime.now()\n",
    "        for d in days_list:\n",
    "            ts_tr = timestamp_ealier_than(now, d) #threshold\n",
    "            result.append(self.__user_label_later_number(ts_list, ts_tr))\n",
    "            \n",
    "        result.append(len(ts_list))\n",
    "        return result\n",
    "    \n",
    "    def update_user_accpet_credits(self, userid):\n",
    "        sql = \"update users set nb_accept=(select count(*) from text_label   \\\n",
    "        where userid={0} and correct =1), credits=nb_accept where userid={0};\".format(userid)\n",
    "        return self.__insertion(sql)\n",
    "    \n",
    "    def update_user_nb_answer(self, userid):\n",
    "        sql = \"update users set nb_answer=(select count(*) from text_label where userid={0}) where userid={0};\".format(userid)\n",
    "        return self.__insertion(sql)\n",
    "        \n",
    "    # admin*****************************************************************************\n",
    "    def get_admin(self, adminid=None, adminname=None, admin_email=None):\n",
    "        return self.__get_by_option('admin', '*',  \n",
    "                                    {'adminid': adminid, 'adminname': adminname, 'email_address': admin_email},\n",
    "                                    head=False)\n",
    "            \n",
    "    def get_admin_nb_task(self, adminid):\n",
    "        sql = \"select sum(nb_json) from se_proj.source where publisher ={};\".format(adminid)\n",
    "        return self.__exe_sql(sql)[0][0]\n",
    "        \n",
    "            \n",
    "    def get_adminid(self, email_addr):\n",
    "        return self.__get_by_option('admin', 'adminid',  \n",
    "                                    {'email_address': email_addr},head=False)[0]\n",
    "    def get_all_admin(self):\n",
    "        admin_list = self.__exe_sql(\"select * from admin;\")\n",
    "        result = []\n",
    "        for admin in admin_list:\n",
    "            [adminid,email_address, adminname, _, _] = admin\n",
    "            nb_task = self.get_admin_nb_task(adminid)\n",
    "            \n",
    "            \n",
    "            nb_source = len(self.get_admin_source(admin_email=email_address))\n",
    "            result.append({\"admin_email\":email_address, \"adminname\":adminname,  \n",
    "                          \"nb_source\":nb_source, \"nb_task\":int(nb_task if nb_task!=None else 0)})\n",
    "\n",
    "        return result\n",
    "        \n",
    "    def get_admin_passwd(self, admin_email=None):\n",
    "        return self.__get_by_option('admin', 'password',\n",
    "                                    {'email_address': admin_email})\n",
    "    \n",
    "    def get_admin_access_level(self, admin_email):\n",
    "        return self.__get_by_option('admin', 'access_level',\n",
    "                                    {'email_address': admin_email})\n",
    "    def insert_admin(self, email_addr, adminname, passwd, access_level=1):\n",
    "        # insertion: 1 success, 0: already exist, -1: fail\n",
    "        if self.__search_admin_by_name(adminname) == None:\n",
    "            sql = \"INSERT INTO `se_proj`.`admin` (`email_address`,`adminname`,`password`,`access_level`) VALUES ('{}','{}','{}','{}');\" \\\n",
    "                .format(email_addr, adminname, passwd, access_level)\n",
    "            return self.__insertion(sql)\n",
    "        else:\n",
    "            return 0\n",
    "        \n",
    "    def get_admin_source(self, admin_email=None):\n",
    "        adminid = self.__get_by_option('admin', 'adminid', {'email_address': admin_email})\n",
    "        return self.__exe_sql(\"select * from source where publisher={}\".format(adminid))\n",
    "\n",
    "    # source*****************************************************************************\n",
    "\n",
    "    def get_source_number(self):\n",
    "        return self.__exe_sql(\"select count(*) from source;\")[0][0]\n",
    "\n",
    "    def get_source(self, sourcename=None, sourceid=None):\n",
    "        return self.__get_by_option('source', '*', {'sourceid': sourceid, 'sourcename': sourcename}, head=False)\n",
    "\n",
    "    def get_all_source(self):\n",
    "        return self.__exe_sql(\"select * from source;\")\n",
    "\n",
    "    def get_source_id(self, sourcename):\n",
    "        return self.__get_by_option('source', 'sourceid', {'sourcename': sourcename})\n",
    "    \n",
    "    def get_source_nb_json(self, sourceid):\n",
    "        return self.__exe_sql(\"select nb_json from source where sourceid={};\".format(sourceid))[0][0]\n",
    "\n",
    "    def insert_source(self, sourcename, finished=0, publisher='NULL', description='', publish_time=get_timestamp(),\n",
    "                      priority=1, ft_degree=0):\n",
    "        # insertion: 1 success, 0: already exist, -1: fail\n",
    "        if self.__search_source_by_name(sourcename) == None:\n",
    "            if ft_degree not in [0,1,2]:  #illegal \n",
    "                ft_degree = 0  #default 0\n",
    "            \n",
    "            sql = \"INSERT INTO `se_proj`.`source` (`sourcename`,`nb_finished`,`publisher`,`description`,`publish_date`, `priority`, `fault_tolerance_degree`) \\\n",
    "            VALUES ('{}',{}, {},'{}',{},{},{});\".format(sourcename, finished, publisher, description, publish_time,\n",
    "                                                     priority, ft_degree)\n",
    "            print(sql)\n",
    "            return self.__insertion(sql)\n",
    "        else:\n",
    "            return 0\n",
    "        \n",
    "    def get_source_ftdgree(self, sourcename=None, sourceid=None):\n",
    "        return self.__get_by_option('source', 'fault_tolerance_degree', {'sourcename':sourcename, 'sourceid':sourceid})\n",
    "        \n",
    "        \n",
    "    def get_recent_source(self, limit=5):\n",
    "        return self.__exe_sql(\"select * from source order by publish_date desc limit {};\".format(limit))\n",
    "    \n",
    "    def get_source_by_priority(self, priority):\n",
    "        # priority should be 1 2 or 3\n",
    "        return self.__exe_sql(\"select * from se_proj.source where priority ={};\".format(priority))\n",
    "\n",
    "    def update_source_nb_finished(self, sourceid):\n",
    "        sql = \"update source set nb_finished= (select count(*) from text_data where datasource={} and final_labelid is not NULL) where sourceid={};\".format(sourceid, sourceid)\n",
    "        print(sql)\n",
    "        return self.__insertion(sql)\n",
    "    \n",
    "    def get_data_final_label(self, dataid):\n",
    "        # get the label_content of the data's final_labelid referenced to \n",
    "        sql = \"select tl.label_content from text_data td join text_label tl \\\n",
    "        on td.final_labelid = tl.labelid where td.dataid={};\".format(dataid)\n",
    "        return self.__exe_sql(sql)\n",
    "    \n",
    "    # data *****************************************************************************\n",
    "    def __insert_textdata(self, sourceid, data_index, data_path, final_labelid='NULL'):\n",
    "        if self.__search_source_by_id(sourceid) != None:\n",
    "            sql = \"INSERT INTO `se_proj`.`text_data` (`datasource`,`data_index`,`data_path`,`final_labelid`) VALUES ({},{},'{}',{});\" \\\n",
    "                .format(sourceid, data_index, data_path, final_labelid)\n",
    "            return self.__insertion(sql)\n",
    "        else:\n",
    "            return 0\n",
    "\n",
    "    def load_data(self, root_path, sourceid=None, sourcename=None):\n",
    "        # load data(json file) from root folder into database\n",
    "        # return 0 fail, -1 set nb_json fail\n",
    "        sourceid = self.__get_by_option('source', 'sourceid', {'sourceid': sourceid, 'sourcename': sourcename})\n",
    "        try:\n",
    "            _, _, files = next(os.walk(root_path))\n",
    "            if(1 != self.__set_col('source', 'nb_json', 'sourceid', sourceid, len(files))):\n",
    "                return -1\n",
    "            for f in files:\n",
    "                with open(os.path.join(root_path, f)) as js:\n",
    "                    data_index = json.load(js)['index']\n",
    "                if None != self.__get_by_mul_cond('text_data', 'dataid',\n",
    "                                                  {'datasouce': sourceid, 'data_index': data_index}):\n",
    "                    continue\n",
    "                self.__insert_textdata(sourceid, data_index, os.path.join(root_path, f))\n",
    "            return 1\n",
    "        except:\n",
    "            return 0\n",
    "\n",
    "    def __get_textdata_sth(self, target_col, data_index, sourceid=None, sourcename=None):\n",
    "        sourceid = self.__get_by_option('source', 'sourceid', {'sourceid': sourceid, 'sourcename': sourcename})\n",
    "        return self.__get_by_mul_cond('text_data', target_col, {'datasource': sourceid, 'data_index': data_index})\n",
    "\n",
    "    def get_textdataid(self, data_index, sourceid=None, sourcename=None):\n",
    "        return self.__get_textdata_sth('dataid', data_index, sourceid, sourcename)\n",
    "    \n",
    "    def fetch_data(self, sourcename, user_email, nb=5):\n",
    "        userid=self.get_user_id(user_email=user_email)\n",
    "        sourceid = self.get_source_id(sourcename=sourcename)\n",
    "\n",
    "        sql = \"select dataid,datasource, data_index, data_path from text_data \\\n",
    "        where datasource={} and final_labelid is NULL and dataid not in \\\n",
    "        (select td.dataid from text_data td \\\n",
    "        join text_label tl on td.dataid=tl.dataid \\\n",
    "        where tl.userid={} and td.datasource={});\".format(sourceid, userid, sourceid)\n",
    "        print(sql)\n",
    "        \n",
    "        \n",
    "        l = self.__exe_sql(sql)\n",
    "        \n",
    "        if len(l)>nb:\n",
    "            l = random.sample(l,nb)\n",
    "        result = []\n",
    "        #set_trace()\n",
    "        for i in l:\n",
    "            with open(i[-1]) as f:\n",
    "                #print(i[-1])\n",
    "                data = json.load(f)\n",
    "            data['dataid'] = int(i[0])\n",
    "            result.append(data)\n",
    "        return result\n",
    "\n",
    "    \n",
    "    # label *****************************************************************************\n",
    "    \n",
    "    # index: dataid, userid\n",
    "    def __get_label_sth(self, target_col, dataid, userid=None, username=None, user_email=None):\n",
    "        if userid == None:\n",
    "            userid = self.__get_by_option('users', 'userid', {'username': username, 'email_address': user_email})\n",
    "        return self.__get_by_mul_cond('text_label', target_col, {'dataid': dataid, 'userid': userid})\n",
    "\n",
    "    def get_label_correct(self, dataid, userid=None, username=None, user_email=None):\n",
    "        # return 0 not determined, 1 correct, -1 not correct\n",
    "        return self.__get_label_sth('correct', userid, username, user_email)\n",
    "    \n",
    "    def set_label_correct(self, labelid, value=1):\n",
    "        #set correct to the value\n",
    "        # labelid can be either int or int tuple\n",
    "        # return 1 if success\n",
    "        return self.__set_col('text_label','correct', 'labelid', labelid, value)\n",
    "    \n",
    "    \n",
    "    def insert_label(self, user_email, json_list, save_dir='/home/se2018/label/', label_date=get_timestamp(), correct=0):\n",
    "        # insert label , save label json file from the same user of the same project\n",
    "#         try:\n",
    "        if 1:\n",
    "            if(json_list == []):\n",
    "                return 1\n",
    "            #set_trace()\n",
    "            userid=self.get_user_id(user_email=user_email)\n",
    "            if(userid == None):\n",
    "                print(\"user not found\")\n",
    "                return -1\n",
    "            proj_name = json_list[0]['projectName']\n",
    "            save_dir = save_dir+'{}/'.format(proj_name)\n",
    "            \n",
    "            \n",
    "            #self.set_user_nb_answer(userid, addoffset = len(json_list))\n",
    "            \n",
    "            if not os.path.exists(save_dir):\n",
    "                os.makedirs(save_dir)\n",
    "                \n",
    "            for j in json_list:\n",
    "                #save the file\n",
    "                save_path = save_dir+str(j['index'])+'_'+str(userid)+'_label.json'\n",
    "                with open(save_path, 'w') as outfile:\n",
    "                    json.dump(j, outfile)\n",
    "\n",
    "                sourceid = self.get_source_id(sourcename=proj_name)\n",
    "                ftdegree = self.get_source_ftdgree(sourceid=sourceid)\n",
    "                \n",
    "                label_content = []\n",
    "                for subtask in j['task']:\n",
    "                    #set_trace()\n",
    "                    label_content.append(subtask['label'])\n",
    "\n",
    "                    \n",
    "                \n",
    "                label_content = str(label_content).replace(\"'\", \"`\")\n",
    "#                 ft_flag = self._pre_ft_processing(j['dataid'], label_content)\n",
    "#                 print(\"ft_flag : {}\".format(ft_flag))\n",
    "                \n",
    "#                 correct = max(0, ft_flag)# ft_flag=0->correct=0; ft_flag=1->correct=1; ft_flag=-1->correct=0;\n",
    "                \n",
    "                sql = \"INSERT INTO text_label(`dataid`,`userid`,`labeldate`,`label_path`,`label_content`,`correct`) VALUES \\\n",
    "                ({},{},{},'{}','{}',{});\".format(j['dataid'], userid, label_date, save_path, label_content, 0)\n",
    "\n",
    "                              \n",
    "#                 if ft_flag == 1:\n",
    "#                     # set user nb_accept and credits\n",
    "#                     sql = \"update users set nb_accept = nb_accept+1 , credits=credits+1 where userid = ;\".format(userid)\n",
    "                    \n",
    "                #print(sql)\n",
    "                if(self.__insertion(sql)== -1):\n",
    "                    print(\"insert label info into database fail\")\n",
    "                \n",
    "                #update nb_answer\n",
    "                if 1!=self.update_user_nb_answer(userid):\n",
    "                    print(\"update user nb_answer fail\")\n",
    "                \n",
    "                # fault tolerance\n",
    "                ft_degree = self.get_source_ftdgree(sourceid= sourceid)\n",
    "                print(\"goto ft process, sourceid = {}, ft_degree = {}\".format(sourceid, ft_degree))\n",
    "                re = self.fault_tol_process(j['dataid'],sourceid, ft_degree) \n",
    "                if re not in [0,1]:\n",
    "                    return re\n",
    "                    \n",
    "            return 1\n",
    "#         except:\n",
    "#             return -1\n",
    "        \n",
    "\n",
    "#     def _pre_ft_processing(self, dataid, user_ans):\n",
    "#         # return 0 if incorrect, 1 if correct, -1 if not final yet, go for fault tolerance process\n",
    "#         final_label = self.get_data_final_label(dataid= dataid)\n",
    "#         if(final_label == []):\n",
    "#             # no final label, continue ft process\n",
    "#             return -1\n",
    "#         else:\n",
    "#             # the question is finaled, compare the user answer with the fianl answer \n",
    "#             return int(user_ans == final_label[0][0])\n",
    "        \n",
    "        \n",
    "    def fault_tol_process(self, dataid, sourceid, ft_degree):\n",
    "        #return -1 if error, 0 if none is detected correct\n",
    "        #return 1 if success\n",
    "        \n",
    "        ft_data = self.load_ft_data(dataid)\n",
    "        nb_json = self.get_source_nb_json(sourceid= sourceid)\n",
    "        correct_labelid = fault_tolerance.ft_algo(ft_data,nb_json,self.ft_params['threshold'][self.__ft_degree_dict[ft_degree]], self.ft_params['init_acc'], self.ft_params['nb_bel_ratio'])\n",
    "        #save ft log\n",
    "        log = {'data':ft_data, 'result':correct_labelid}\n",
    "        with open('/home/se2018/Log/'+str(get_timestamp())+'.json', 'w') as file:\n",
    "                file.write(json.dumps(log))\n",
    "        \n",
    "        print(\"correct answer: {}\".format(correct_labelid))\n",
    "        if correct_labelid!=None:\n",
    "            #set correct=1 in table text_label\n",
    "            if len(correct_labelid)==1:\n",
    "                correct_labelid = correct_labelid[0]\n",
    "                final_labelid = correct_labelid\n",
    "            else:\n",
    "                correct_labelid = tuple(correct_labelid)\n",
    "                final_labelid = correct_labelid[0]\n",
    "            if (-1 == self.set_label_correct(correct_labelid, value=1)):\n",
    "                return 2 # set fail\n",
    "\n",
    "            #update user's nb_accept and credits\n",
    "            #select userid\n",
    "            if type(correct_labelid) is int:\n",
    "                sql = \"select userid from text_label where labelid = {}\".format(correct_labelid)\n",
    "            elif type(correct_labelid) is tuple:\n",
    "                sql = \"select userid from text_label where labelid in {};\".format(correct_labelid)\n",
    "            userid_list = self.__exe_sql(sql)\n",
    "            for userid in userid_list:\n",
    "                if 1!=self.update_user_accpet_credits(userid=userid[0]):\n",
    "                    print(\"update user nb_accept, credits fail\")\n",
    "            \n",
    "#             if type(correct_labelid) is int:\n",
    "#                 sql = \"update users set nb_accept = nb_accept+1 , credits=credits+1 where userid in \\\n",
    "#                 (select userid from text_label where labelid = {});\".format(correct_labelid)\n",
    "#             elif type(correct_labelid) is tuple:\n",
    "#                 sql = \"update users set nb_accept = nb_accept+1 , credits=credits+1 where userid in \\\n",
    "#                 (select userid from text_label where labelid in {});\".format(correct_labelid)\n",
    "#             print(sql)\n",
    "#             if(-1 == self.__insertion(sql)):\n",
    "#                 return 3 # set fail\n",
    "            #modify text_data to add final_labelid\n",
    "        \n",
    "            if 1!=self.__set_col('text_data','final_labelid', 'dataid', dataid, final_labelid):\n",
    "                print(\"update final_labelid of dataid {} fail\".format(dataid))\n",
    "            \n",
    "            #set number of finished\n",
    "            if -1 == self.update_source_nb_finished(sourceid=sourceid):\n",
    "                print(\"update source nb_finished of sourceid {} fail\".format(sourceid))\n",
    "            \n",
    "            return 1\n",
    "        else:\n",
    "            return 0\n",
    "        \n",
    "    def load_ft_data(self, dataid):\n",
    "        # load data for fault tolerance\n",
    "        # return [dataid, label_content, userid, user nb_accpet, user nb_answer ]\n",
    "        sql = \"SELECT tl.labelid, tl.label_content, tl.userid, u.nb_accept, u.nb_answer \\\n",
    "        FROM text_label tl join users u on u.userid=tl.userid where dataid={} \".format(dataid)\n",
    "        return self.__exe_sql(sql)\n",
    "    \n",
    "    def download_label(self, sourcename, zip_path, root_path = '~/tmp'):\n",
    "        try:\n",
    "            sourceid = self.get_source_id(sourcename=sourcename)\n",
    "            # get path\n",
    "            #data_path = self.__get_by_mul_cond('text_data','data_path',{'datasource':sourceid}, fetchone=False)\n",
    "            label_path = self.__get_finallabel_path(sourceid)\n",
    "\n",
    "            if not os.path.exists(root_path):\n",
    "                os.makedirs(root_path)\n",
    "            #set_trace()\n",
    "\n",
    "            for p in label_path:\n",
    "                file_name = p[0].split('/')[-1]\n",
    "                copyfile(p[0], os.path.join(root_path, file_name))\n",
    "\n",
    "            # make zip\n",
    "            make_archive(zip_path, 'zip', root_path)\n",
    "            #delete tmp dir\n",
    "            rmtree(root_path)\n",
    "            \n",
    "            return zip_path+'.zip'\n",
    "        except:\n",
    "            return ''\n",
    "        \n",
    "\n",
    "    \n",
    "    def __get_finallabel_path(self, sourceid):\n",
    "        sql = \"select tl.label_path from text_data td join text_label tl on td.final_labelid = tl.labelid where td.datasource={};\".format(sourceid)\n",
    "        return self.__exe_sql(sql)\n",
    "    \n",
    "    def get_recapcha(self):\n",
    "        try:\n",
    "            sql = \"select label_path from text_label where correct =1;\"\n",
    "            result = random.choice(self.__exe_sql(sql))[0]\n",
    "            #print(result)\n",
    "            with open(result) as js:\n",
    "                file = json.load(js)\n",
    "            return file\n",
    "        except:\n",
    "            return None\n",
    "        \n",
    "    def close(self):\n",
    "        self.cursor.close()\n",
    "        self.conn.close()\n",
    "        \n",
    "        \n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "from mysql.connector import connection\n",
    "from IPython.core.debugger import set_trace\n",
    "import os\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "cnx = connection.MySQLConnection(user = 'root', password = 'se2018', host = '127.0.0.1', database = 'se_proj')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "c = sql_conn(cnx)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "select dataid,datasource, data_index, data_path from text_data         where datasource=15 and final_labelid is NULL and dataid not in         (select td.dataid from text_data td         join text_label tl on td.dataid=tl.dataid         where tl.userid=25 and td.datasource=15);\n"
     ]
    }
   ],
   "source": [
    "a = c.fetch_data(sourcename='nb_test', user_email='7@7.com', nb=5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'data': '装的非常合适，物流也很快，当天发货隔天到了，超级满意',\n",
       "  'dataid': 46,\n",
       "  'index': 10,\n",
       "  'projectName': 'nb_test',\n",
       "  'task': [{'aim': '请标出下面这句话的情感态度',\n",
       "    'choices': ['Positive', 'Neutral', 'Negative'],\n",
       "    'front': 'option',\n",
       "    'label': '',\n",
       "    'mode': 'single'},\n",
       "   {'aim': '请选择这句话的情感：',\n",
       "    'choices': ['开心', '满意', '失望', '生气'],\n",
       "    'front': 'box',\n",
       "    'label': [],\n",
       "    'mode': 'multiple'},\n",
       "   {'aim': '请选出句子中的形容词：', 'front': 'blank', 'label': [], 'mode': 'open'}]},\n",
       " {'data': '今天天气真好啊',\n",
       "  'dataid': 38,\n",
       "  'index': 2,\n",
       "  'projectName': 'nb_test',\n",
       "  'task': [{'aim': '请标出下面这句话的情感态度',\n",
       "    'choices': ['Positive', 'Neutral', 'Negative'],\n",
       "    'front': 'option',\n",
       "    'label': '',\n",
       "    'mode': 'single'},\n",
       "   {'aim': '请选择这句话的情感：',\n",
       "    'choices': ['开心', '满意', '失望', '生气'],\n",
       "    'front': 'box',\n",
       "    'label': [],\n",
       "    'mode': 'multiple'},\n",
       "   {'aim': '请选出句子中的形容词：', 'front': 'blank', 'label': [], 'mode': 'open'}]},\n",
       " {'data': '质量超级差，轻轻的靠一下就烂了，比10块钱一张的还不如，建议大家不要买，非常差劲?',\n",
       "  'dataid': 41,\n",
       "  'index': 9,\n",
       "  'projectName': 'nb_test',\n",
       "  'task': [{'aim': '请标出下面这句话的情感态度',\n",
       "    'choices': ['Positive', 'Neutral', 'Negative'],\n",
       "    'front': 'option',\n",
       "    'label': '',\n",
       "    'mode': 'single'},\n",
       "   {'aim': '请选择这句话的情感：',\n",
       "    'choices': ['开心', '满意', '失望', '生气'],\n",
       "    'front': 'box',\n",
       "    'label': [],\n",
       "    'mode': 'multiple'},\n",
       "   {'aim': '请选出句子中的形容词：', 'front': 'blank', 'label': [], 'mode': 'open'}]},\n",
       " {'data': '我实在想不出来了',\n",
       "  'dataid': 43,\n",
       "  'index': 5,\n",
       "  'projectName': 'nb_test',\n",
       "  'task': [{'aim': '请标出下面这句话的情感态度',\n",
       "    'choices': ['Positive', 'Neutral', 'Negative'],\n",
       "    'front': 'option',\n",
       "    'label': '',\n",
       "    'mode': 'single'},\n",
       "   {'aim': '请选择这句话的情感：',\n",
       "    'choices': ['开心', '满意', '失望', '生气'],\n",
       "    'front': 'box',\n",
       "    'label': [],\n",
       "    'mode': 'multiple'},\n",
       "   {'aim': '请选出句子中的形容词：', 'front': 'blank', 'label': [], 'mode': 'open'}]},\n",
       " {'data': '这个project太麻烦了',\n",
       "  'dataid': 39,\n",
       "  'index': 11,\n",
       "  'projectName': 'nb_test',\n",
       "  'task': [{'aim': '请标出下面这句话的情感态度',\n",
       "    'choices': ['Positive', 'Neutral', 'Negative'],\n",
       "    'front': 'option',\n",
       "    'label': '',\n",
       "    'mode': 'single'},\n",
       "   {'aim': '请选择这句话的情感：',\n",
       "    'choices': ['开心', '满意', '失望', '生气'],\n",
       "    'front': 'box',\n",
       "    'label': [],\n",
       "    'mode': 'multiple'},\n",
       "   {'aim': '请选出句子中的形容词：', 'front': 'blank', 'label': [], 'mode': 'open'}]}]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "goto ft process, sourceid = 15, ft_degree = 1\n",
      "threshold:0.51  init_acc:0.5, nb_bel_ratio1\n",
      "lenth of ans: 3 number_belief: 13.2\n",
      "new answer detect||use init belief, total = 0.5\n",
      "new answer detect||use init belief, total = 1.0\n",
      "hit old answer || use init belief, total = 1.5\n",
      "find answer \n",
      "correct answer: [1195, 1202]\n",
      "update text_label set correct=1 where labelid in (1195, 1202);\n",
      "*****************************************\n",
      "[(17,), (25,)]\n",
      "update text_data set final_labelid=1195 where dataid =46;\n",
      "update source set nb_finished= (select count(*) from text_data where datasource=15 and final_labelid is not NULL) where sourceid=15;\n",
      "goto ft process, sourceid = 15, ft_degree = 1\n",
      "threshold:0.51  init_acc:0.5, nb_bel_ratio1\n",
      "lenth of ans: 4 number_belief: 13.2\n",
      "new answer detect||use init belief, total = 0.5\n",
      "new answer detect||use init belief, total = 1.0\n",
      "new answer detect||use init belief, total = 1.5\n",
      "hit old answer || use init belief, total = 2.0\n",
      "correct answer: None\n",
      "goto ft process, sourceid = 15, ft_degree = 1\n",
      "threshold:0.51  init_acc:0.5, nb_bel_ratio1\n",
      "lenth of ans: 3 number_belief: 13.2\n",
      "new answer detect||use init belief, total = 0.5\n",
      "new answer detect||use init belief, total = 1.0\n",
      "hit old answer || use init belief, total = 1.5\n",
      "find answer \n",
      "correct answer: [1199, 1204]\n",
      "update text_label set correct=1 where labelid in (1199, 1204);\n",
      "*****************************************\n",
      "[(17,), (25,)]\n",
      "update text_data set final_labelid=1199 where dataid =41;\n",
      "update source set nb_finished= (select count(*) from text_data where datasource=15 and final_labelid is not NULL) where sourceid=15;\n",
      "goto ft process, sourceid = 15, ft_degree = 1\n",
      "threshold:0.51  init_acc:0.5, nb_bel_ratio1\n",
      "lenth of ans: 3 number_belief: 13.2\n",
      "new answer detect||use init belief, total = 0.5\n",
      "new answer detect||use init belief, total = 1.0\n",
      "hit old answer || use init belief, total = 1.5\n",
      "find answer \n",
      "correct answer: [1196, 1205]\n",
      "update text_label set correct=1 where labelid in (1196, 1205);\n",
      "*****************************************\n",
      "[(17,), (25,)]\n",
      "update text_data set final_labelid=1196 where dataid =43;\n",
      "update source set nb_finished= (select count(*) from text_data where datasource=15 and final_labelid is not NULL) where sourceid=15;\n",
      "goto ft process, sourceid = 15, ft_degree = 1\n",
      "threshold:0.51  init_acc:0.5, nb_bel_ratio1\n",
      "lenth of ans: 3 number_belief: 13.2\n",
      "new answer detect||use init belief, total = 0.5\n",
      "new answer detect||use init belief, total = 1.0\n",
      "hit old answer || use init belief, total = 1.5\n",
      "find answer \n",
      "correct answer: [1194, 1206]\n",
      "update text_label set correct=1 where labelid in (1194, 1206);\n",
      "*****************************************\n",
      "[(17,), (25,)]\n",
      "update text_data set final_labelid=1194 where dataid =39;\n",
      "update source set nb_finished= (select count(*) from text_data where datasource=15 and final_labelid is not NULL) where sourceid=15;\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.insert_label(user_email='7@7.com', json_list=a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.get_user_credits_rank(user_email=\"huyx@sb.com\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.get_user_mainpage_pan(user_email=\"ii@ii.com\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.get_user_mainpage_pan_history(user_email=\"xuch@sb.com\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.load_data(root_path='/home/se2018/data/xiednproj/', sourcename='xiednproj')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#test user\n",
    "print(c.insert_user('test', 'test@sb.com', 'test', credits = 0))\n",
    "print(c.get_user_id('jiangtk'))\n",
    "print(c.get_user_passwd(username='test'))\n",
    "print(c.get_user_passwd(userid=1))\n",
    "print(c.get_user_credit(username='jiangtk'))\n",
    "print(c.user_exist(username='huyx'))\n",
    "print(c.user_exist(userid=1))\n",
    "print(c.user_exist(userid=0))\n",
    "print(c.get_user(username='huyx'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# test admin\n",
    "print(c.insert_admin('test@sb.com', 'test', 'test', access_level=1))\n",
    "print(c.get_admin(adminname='xiedn'))\n",
    "print(c.get_all_user())\n",
    "print(c.get_user_number())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# test source\n",
    "print(c.insert_source('te',description='test_de\\dsc'))\n",
    "print(c.get_source_id(sourcename='d'))\n",
    "print(c.get_source(sourcename='test_proj'))\n",
    "print(c.get_all_source())\n",
    "print(c.get_source_number())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(c.load_data('/home/se2018/data/test_proj/', sourcename='test_proj'))\n",
    "print(c.get_textdataid(data_index=723154, sourcename='test_proj'))\n",
    "#print(c.update_final_labelid(index=723154, labelid=1, souceid=11))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = 'addf'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(a) is str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}