Python MySQL_Interface.update_asQuery方法代码示例

# 需要导入模块: from DB_Interface import MySQL_Interface [as 别名]
# 或者: from DB_Interface.MySQL_Interface import update_asQuery [as 别名]
    def run(self):
        while True:
            client=MongoClient('localhost',27017)
            db=client['microblog_spider']
            mission_mongo=db.update_mission
            assemble_mongo=db.assemble_factory
            current_time=int(time.time())
            target_time=current_time-60*60*6   #将6个小时仍未完成的任务清除出去
            expired_mission=mission_mongo.find({'mission_start':{'$lt':target_time}}).limit(1)
            expired_mission=[x for x in expired_mission]
            if expired_mission.__len__()==0:
                # 如果没有符合要求的过期任务，则休眠
                time.sleep(60)
            else:
                # 如果有过期的任务
                expired_mission=expired_mission[0]
                mission_id=expired_mission['mission_id']
                user_content=expired_mission['user_list']
                user_list=[x['container_id'] for x in user_content]

                #　将mysql中相关用户isGettingBlog清空
                user_list_str=''
                for item in user_list:
                    user_list_str+='\''+str(item)+'\','
                user_list_str=user_list_str[:-1]
                dbi=MySQL_Interface()
                query='update user_info_table set isGettingBlog=null where container_id in ({user_list});' \
                    .format(user_list=user_list_str)
                dbi.update_asQuery(query)

                # 将assemble_factory中数据清空
                assemble_mongo.remove({'container_id':mission_id})

                # 将Mongo中该任务从任务表中清空。
                mission_mongo.remove({'mission_id':mission_id})

# 需要导入模块: from DB_Interface import MySQL_Interface [as 别名]
# 或者: from DB_Interface.MySQL_Interface import update_asQuery [as 别名]
class deal_isGettingBLog_user(threading.Thread):
    def __init__(self):
        threading.Thread.__init__(self)
        self.dbi=MySQL_Interface()

    def run(self):
        while True:
            self.dbi=MySQL_Interface()
            t=time.time()
            time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(t-12*60*60))

            #删掉cache_history中的行
            query='delete from cache_history where container_id in (select container_id from user_info_table where isGettingBlog<\'{time}\' and update_time is null)'\
                .format(time=time_stick)
            self.dbi.update_asQuery(query)

            # 删掉mongodb-assemble factory中的相关值
            select_query='select container_id from user_info_table where isGettingBlog<\'{time}\' and update_time is null'.format(time=time_stick)
            res=[x[0] for x in self.dbi.select_asQuery(select_query)]
            client=MongoClient('localhost',27017)
            db=client['microblog_spider']
            assemble_table=db.assemble_factory
            assemble_table.remove({'container_id':{'$in':res}})

            # 将user info table中超时行的isGettingBlog清空
            query="update user_info_table set isGettingBlog=null where isGettingBlog<\'{time}\' and update_time is null".format(time=time_stick)
            self.dbi.update_asQuery(query)

            time.sleep(60)

# 需要导入模块: from DB_Interface import MySQL_Interface [as 别名]
# 或者: from DB_Interface.MySQL_Interface import update_asQuery [as 别名]
class deal_fetching_user(threading.Thread):
    #定期清理获取时间过长的部分

    def __init__(self):
        threading.Thread.__init__(self)
        self.dbi=MySQL_Interface()

    def run(self):
        while True:
            self.dbi=MySQL_Interface()
            t=time.time()
            time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(t-3600))
            query="update ready_to_get set is_fetching=null where is_fetching < \'{time}\' ;".format(time=time_stick)
            # print(query)
            # query='select * from ready_to_get where is_fetching < {time}'.format(time=time_stick)
            self.dbi.update_asQuery(query)
            time.sleep(1)

# 需要导入模块: from DB_Interface import MySQL_Interface [as 别名]
# 或者: from DB_Interface.MySQL_Interface import update_asQuery [as 别名]
class control_ready_table(threading.Thread):
    def __init__(self):
        threading.Thread.__init__(self)
        self.dbi=MySQL_Interface()
    def run(self):
        while True:
            self.dbi=MySQL_Interface()
            num=self.dbi.get_line_num('ready_to_get')
            if num>150*1000:
                query='select m.fans_num from (' \
                      'select fans_num from ready_to_get ' \
                      'ORDER BY fans_num limit 50000' \
                      ') as m order by fans_num desc limit 1'
                res=self.dbi.select_asQuery(query)[0][0]
                query='delete from ready_to_get where fans_num<{num}'\
                    .format(num=res)
                self.dbi.update_asQuery(query)
            else:
                time.sleep(600)

# 需要导入模块: from DB_Interface import MySQL_Interface [as 别名]
# 或者: from DB_Interface.MySQL_Interface import update_asQuery [as 别名]
    def get(self):
        global proxy
        uuid=str(self.get_argument('uuid'))
        task_id=self.task_assign(uuid)

        if proxy.get_ave_proxy_size()<30:   # check the size of current proxy size
            self.write('no task')
            self.finish()
            return

        if task_id==-1:       # checi if this uuid is valid
            self.write('no task')
            self.finish()
            return

        if task_id==1:         # get the social web of certain user
            dbi=MySQL_Interface()
            query='select * from ready_to_get where is_fetching is null order by fans_num desc limit 1;'
            res=dbi.select_asQuery(query)
            if res.__len__()==0:
                self.write('no task')
                self.finish()
                return
            res=res[0]
            col_info=dbi.get_col_name('ready_to_get')
            uid=res[col_info.index('uid')]

            self.write('{uid},connect'.format(uid=uid))
            self.finish()

            time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            query="update ready_to_get set is_fetching=\'{t_time}\' where uid={uid} ;"\
                .format(t_time=time_stick,uid=uid)
            dbi.update_asQuery(query)

        if task_id==2:      # get the history microblog of a certain user
            dbi=MySQL_Interface()
            query='select container_id,blog_num from user_info_table ' \
                  'where (isGettingBlog is null and update_time is null) ' \
                  'order by fans_num desc limit 1 ;'
            # query='select container_id,blog_num from user_info_table ' \
            #       'order by rand() limit 1 ;'
            [container_id,blog_num]=dbi.select_asQuery(query)[0]
            self.write('{c_id};{blog},history'
                       .format(c_id=container_id,blog=blog_num))
            self.finish()
            time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            query="update user_info_table set isGettingBlog=\'{t_time}\' where container_id={cid} ;"\
                .format(t_time=time_stick,cid=container_id)
            dbi.update_asQuery(query)

        if task_id==3:   # this part is in test
            dbi=MySQL_Interface()
            current_time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            target_time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()-60*60*24)) #提早一天
            query='select uid,update_time from user_info_table ' \
                  'where update_time<\'{target_time}\' and isGettingBlog is null limit {batch}'\
                .format(target_time=target_time_stick,batch=10)
            res=dbi.select_asQuery(query)
            res=[[line[0],int(time.mktime(line[1].timetuple()))] for line in res]
            # res=[[line[0],int(time.mktime(time.strptime(str(line[1]),'%Y-%m-%d %H:%M:%S')))] for line in res]
            res=[line[0]+'-'+str(line[1]) for line in res]
            inn=''
            for item in res:
                inn+=item+';'
            inn=inn[0:-1]
            # uid-stamp;uid-timestamp;...;,update  (the formation of order)
            commend='{list},update'.format(list=inn)
            self.write(commend)
            self.finish()

# 需要导入模块: from DB_Interface import MySQL_Interface [as 别名]
# 或者: from DB_Interface.MySQL_Interface import update_asQuery [as 别名]
    def post(self):

        # 从客户端获取信息
        try:
            user_history=self.get_argument('user_history')
            latest_time=self.get_argument('latest_time')
            latest_timestamp=self.get_argument('latest_timestamp')
            container_id=self.get_argument('container_id')
            isDivided=self.get_argument('isDivided')
            user_history=eval(user_history)
            if isDivided==1 or isDivided=='1' :
                block_num=self.get_argument('block_num')
                current_block=self.get_argument('current_block')
            self.write('success to return user history')
            self.finish()
            print('Success: to get data from web')
        except Exception as e:
            self.write('fail to return user history')
            self.finish()
            print('Error:server-HistoryReturn:'
                  'Unable to get value from http package,Reason:')
            print(e)
            return


        # 连接
        try:
            dbi=MySQL_Interface()
        except:
            print('Error:server-HistoryReturn:'
                  'Unable to connect to MySQL')

        # 从MYSQL获取该用户相关信息
        try:
            query='select * from user_info_table where container_id=\'{cid}\''\
                .format(cid=container_id)
            user_info=dbi.select_asQuery(query)[0]
            col_name=dbi.get_col_name('user_info_table')
        except Exception as e:
            print('Error:server-HistoryReturn:'
                  'No such user in MySQL.user_info_table,Reason:')
            print(e)

        # 将数据存入Mongodb以后将相关信息存入mysql，并将isGettingBlog字段设为空
        try:
            blog_len=user_history.__len__()
            wanted_blog_len=user_info[col_name.index('blog_num')]
            blog_accuracy=blog_len/wanted_blog_len
            time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            if not user_info[col_name.index('update_time')]:
                save_data_inMongo(user_history)
                # query='update user_info_table set ' \
                #       'update_time=\'{up_time}\',' \
                #       'latest_blog=\'{latest_blog}\',' \
                #       'isGettingBlog=null ' \
                #       'where container_id=\'{cid}\';'\
                #     .format(up_time=time_stick,latest_blog=latest_time,cid=container_id)
                query='update user_info_table set ' \
                      'update_time=\'{up_time}\',' \
                      'latest_blog=\'{latest_blog}\'' \
                      'where container_id=\'{cid}\';' \
                    .format(up_time=time_stick,latest_blog=latest_time,cid=container_id)
                dbi.update_asQuery(query)
            else:
                query='update user_info_table set isGettingBlog=null where container_id=\'{cid}\''\
                    .format(cid=container_id)
                dbi.update_asQuery(query)

            query='insert into accuracy_table values ({acc},\'{t_s}\') ;'\
                .format(acc=blog_accuracy,t_s=time_stick)
            dbi.insert_asQuery(query)

            print('Success: insert user into MongoDB, the num of data is {len}'
                  .format(len=blog_len))
        except Exception as e:
            print('Error:server-HistoryReturn:'
                  'Unable to update data in MySQL.user_info_tabe,Reason:')
            print(e)

# 需要导入模块: from DB_Interface import MySQL_Interface [as 别名]
# 或者: from DB_Interface.MySQL_Interface import update_asQuery [as 别名]
    def get(self):
        global proxy
        uuid=str(self.get_argument('uuid'))
        task_id=self.task_assign(uuid)

        if proxy.get_ave_proxy_size()<30:   # check the size of current proxy size
            self.write('no task')
            self.finish()
            return

        if task_id==-1:       # checi if this uuid is valid
            self.write('no task')
            self.finish()
            return

        if task_id==1:         # get the social web of certain user
            dbi=MySQL_Interface()
            query='select * from ready_to_get where is_fetching is null order by fans_num desc limit 1;'
            res=dbi.select_asQuery(query)
            if res.__len__()==0:
                self.write('no task')
                self.finish()
                return
            res=res[0]
            col_info=dbi.get_col_name('ready_to_get')
            uid=res[col_info.index('uid')]

            self.write('{uid},connect'.format(uid=uid))
            self.finish()

            time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            query="update ready_to_get set is_fetching=\'{t_time}\' where uid={uid} ;"\
                .format(t_time=time_stick,uid=uid)
            dbi.update_asQuery(query)


        if task_id==2:      # get the history microblog of a certain user
            dbi=MySQL_Interface()
            query='select container_id,blog_num from user_info_table ' \
                  'where (isGettingBlog is null and update_time is null and blog_num<{valve} and blog_num>100)' \
                  'order by fans_num desc limit 1 ;'.format(valve=config.HISTORY_TASK_VALVE)
            # query='select container_id,blog_num from user_info_table ' \
            #       'order by rand() limit 1 ;'
            res=dbi.select_asQuery(query)
            if res.__len__()==0:
                self.write('no task')
                self.finish()
                return
            [container_id,blog_num]=res[0]
            self.write('{c_id};{blog},history'
                       .format(c_id=container_id,blog=blog_num))
            self.finish()
            time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            query="update user_info_table set isGettingBlog=\'{t_time}\' where container_id={cid} ;"\
                .format(t_time=time_stick,cid=container_id)
            dbi.update_asQuery(query)

        if task_id==3:      # get the history microblog of a certain user
            dbi=MySQL_Interface()
            query='select container_id,blog_num from user_info_table ' \
                  'where (isGettingBlog is null and update_time is null and blog_num>={valve}  and blog_num>100)' \
                  'order by fans_num desc limit 1 ;'.format(valve=config.HISTORY_TASK_VALVE)
            # query='select container_id,blog_num from user_info_table ' \
            #       'order by rand() limit 1 ;'
            [container_id,blog_num]=dbi.select_asQuery(query)[0]
            self.write('{c_id};{blog},history'
                       .format(c_id=container_id,blog=blog_num))
            self.finish()
            time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            query="update user_info_table set isGettingBlog=\'{t_time}\' where container_id={cid} ;" \
                .format(t_time=time_stick,cid=container_id)
            dbi.update_asQuery(query)

        if task_id==4 or task_id==5 or task_id==100:   # this part is in test
            dbi=MySQL_Interface()
            current_time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            target_time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()-60*60*24*1)) #提早5天
            if task_id==4:
                batch_size = 100
            elif task_id==5:
                batch_size = 200
            else:
                batch_size = 10
            query='select container_id,update_time,latest_blog from user_info_table ' \
                  'where update_time<\'{target_time}\' and isGettingBlog is null and blog_num>10 order by fans_num desc limit {batch}' \
                .format(target_time=target_time_stick,batch=batch_size)
            print(query)
            res=dbi.select_asQuery(query)

            # 将从mysql中取得的用户列表加上必要的变量以后发送给客户端
            res=[[line[0],int(time.mktime(line[1].timetuple())),int(time.mktime(line[2].timetuple()))] for line in res]
            res_cp=res

            if res_cp.__len__()==0:  # if no task ,then return "no task"
                print('*** warning: no avaliable update mission ***')
                self.write('no task')
                self.finish()
                return

            # print('debug from task handler')
#.........这里部分代码省略.........

# 需要导入模块: from DB_Interface import MySQL_Interface [as 别名]
# 或者: from DB_Interface.MySQL_Interface import update_asQuery [as 别名]

#.........这里部分代码省略.........
                # 将获得数据写入各按月份分类的聚合中
                table_list=[]
                request_updateMonth=[]
                for i in range(data_final.__len__()):
                    temp_time=data_final[i]['created_at']
                    temp_table_name='user_{year}_{month}'.format(year=temp_time[0:4],month=temp_time[5:7])
                    if temp_table_name in table_list:
                        request_updateMonth[table_list.index(temp_table_name)].append(requests[i])
                    else:
                        table_list.append(temp_table_name)
                        request_updateMonth.append([requests[i]])
                print('the number of ori table is {len}'.format(len=request_updateMonth.__len__()))
                print(table_list)
                selected_num = 5
                if table_list.__len__()>selected_num:
                    packed = [[table_list[i],request_updateMonth[i]]
                              for i in range(table_list.__len__())]
                    packed = sorted(packed, key=lambda x:x[0], reverse=True)
                    packed = packed[:selected_num]
                    table_list = [x[0] for x in packed]
                    request_updateMonth = [x[1] for x in packed]
                    print('the number of dealed table is {len}'.format(len=request_updateMonth.__len__()))
                    print(table_list)
                if request_updateMonth.__len__()>=3:
                    print('{a}-{b}-{c}'.format( a=request_updateMonth[0].__len__(),
                                                b=request_updateMonth[1].__len__(),
                                                c=request_updateMonth[2].__len__()
                                                ))

                for i in range(table_list.__len__()):
                    collection=eval('db.{name}'.format(name=table_list[i]))
                    # todo for debug----------------------------
                    print('table {x} is started'.format(x=table_list[i]))
                    #---------------------------------------------------
                    if request_updateMonth[i].__len__()>0:
                        try:
                            collection.bulk_write(request_updateMonth[i])
                        except Exception as e:
                            print('Update Mission :{mi} fail to update table {t}'
                                  .format(mi=mission_id,t=table_list[i]))

                print('Update Mission :{mi} Success:server_database:所获的数组已经写入按月分类聚合中'
                      .format(mi=mission_id))

                # 清理Mydql，更新相关行数中的update_time和latest_blog
                time_stick=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
                # 找出各用户的最近更新时间
                latest_list=[0]*user_list.__len__()
                for line in data_final:
                    this_timestick=int(line['created_timestamp'])
                    this_container='100505'+str(line['user_id'])
                    try:
                        index=user_list.index(this_container)
                        if latest_list[index]<this_timestick:
                            latest_list[index]=this_timestick
                    except:
                        print('error:server_database->deal_update_mission:'
                              'container {id} is not in user_list'.format(id=this_container))

                # 将各用户最近更新时间固化为mysql更新语句。
                case_list=''
                updated_user_list=''
                for i in range(latest_list.__len__()):
                    if latest_list[i]>user_content[i]['latest_blog'] :
                        time_stick_inner=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(latest_list[i]))
                        case_list+=' when \'{cid}\' then \'{tstick}\' '.format(cid=user_list[i],tstick=time_stick_inner)
                        updated_user_list+='\'{cid}\','.format(cid=user_list[i])
                updated_user_list=updated_user_list[:-1]
                # 构建mysql更新语句
                query1='update user_info_table set update_time=\'{time}\' where container_id in ( {user_list} ) ;'\
                    .format(time=time_stick,user_list=user_list_str)
                query2='update user_info_table set latest_blog= case container_id {case_list} end where container_id in ( {ulist2} ) ;'\
                    .format(case_list=case_list,ulist2=updated_user_list)
                dbi=MySQL_Interface()
                dbi.update_asQuery(query2)
                dbi.update_asQuery(query1)
                print('Update Mission :{mi} Success:server_database: UpdateTime和LatestBlog选项已更新'
                      .format(mi=mission_id))
                if user_list_str.__len__()>0:
                    query='update user_info_table set isGettingBlog=null where container_id in ({user_list});' \
                        .format(user_list=user_list_str)
                    dbi.update_asQuery(query)
                print('Update Mission :{mi} Success:erver_database: isGettingBlog选项已清除'.format(mi=mission_id))

            else:
                if user_list_str.__len__()>0:
                    query='update user_info_table set isGettingBlog=null where container_id in ({user_list});'\
                        .format(user_list=user_list_str)
                    dbi=MySQL_Interface()
                    dbi.update_asQuery(query)

            # 将assemble_factory中与当前任务有关数据清空
            assemble_table.remove({'container_id':mission_id})
            print('Update Mission :{mi} Success:server_database: assemble_factory in Mongo is cleared'
                  .format(mi=mission_id))

            # 将mongodb，任务列表中当前任务项清空
            mission_mongo.remove({'mission_id':mission_id})
            print('Update Mission :{mi} Success:server_database: this mission is cleared'
                  .format(mi=mission_id))