def force_match_city_channels_price(city_name): """如果有渠道价格更新,并且所有的渠道都更新完毕则进行所有渠道价格匹配""" logger.info( 'city %s, start to force match channel price and update cinema min price', city_name) price_manager = PriceManager.clone() price_manager.save_all_channels_price(city_name) price_manager.cinema_price_match_done(city_name) logger.info( 'city %s, end to force match channel price and update cinema min price', city_name)
def match_cinema_by_phone(self, cinema_cls, city, step): """通过phone进行初略匹配""" logger.info("step %d, begin to match cinema by phone with city %s", step, city) matched_result = self.manager.load_matched_cinema_by_phone( cinema_cls, city, step) for matched_item in matched_result: self.db.save(matched_item, cinema_cls.get_match2mt_result_table_name()) logger.info("step %d, end to match cinema by phone with city %s", step, city)
def match_cinema_by_ai(self, cinema_cls, city, step): """通过AI进行初略匹配""" logger.info("step %d, begin to match cinema by ai with city %s", step, city) unmatched_cinemas = self.manager.load_unmatched_cinema(cinema_cls, city) for cinema in unmatched_cinemas: generator = self.ai_matcher.process_item(cinema, None) matched_item = next(generator) if matched_item is not None: matched_item['match_step'] = step self.db.save(matched_item, cinema_cls.get_match2mt_result_table_name()) logger.info("step %d, end to match cinema by ai with city %s", step, city)
def start_requests(self): cinema_manager = CinemaManager.get_instance() cinema_ids = cinema_manager.need_update_cinema_ids( CinemaTB, self.city_name) logger.info("There are %d cinemas to update", len(cinema_ids)) # cinema_ids = [37000] for cinema_id in cinema_ids: # special usage proxy = get_random_ip_port() # proxy = None yield scrapy.Request(self.cinema_url.format(cinema_id), callback=self.parse, meta={ 'cinema_id': cinema_id, 'proxy': proxy })
def match_channels_movie(): """将更新的渠道电影与豆瓣电影匹配""" channels = [MovieLM, MovieMT, MovieTB] from hyspider.matchers.movie import MovieMatcher matcher = MovieMatcher() for channel in channels: logger.info('begin to match movie %s to db', channel.get_channel_name()) matcher.match_2db(channel) logger.info('end to match movie %s to db', channel.get_channel_name()) logger.info('begin to save match movie result') matcher.save_all_channels_match_results() logger.info('end to save match movie result')
def update_cinema_min_price(self): """根据综合所有渠道后的价格表更新影院最低价格,下面sql与执行的sql等价 update cinema join ( select cinema_id, min(min_price) as min_price from ( select cinema_id, min(price_mt) as min_price from price group by cinema_id union all select cinema_id, min(price_tb) as min_price from price group by cinema_id union all select cinema_id, min(price_lm) as min_price from price group by cinema_id )t group by cinema_id )cinema_min_price on (cinema.id_mt = cinema_min_price.cinema_id) set cinema.min_price = cinema_min_price.min_price """ sql = ''' update cinema join ( select cinema_id, LEAST(mt_min_price,tb_min_price, lm_min_price) as min_price from ( select cinema_id, min(price_mt) as mt_min_price, min(price_tb) as tb_min_price, min(price_lm) as lm_min_price from price GROUP BY cinema_id )tmp )cinema_min_price on (cinema.id_mt = cinema_min_price.cinema_id) set cinema.min_price = cinema_min_price.min_price ''' logger.info("start to update cinema min price") self.db.exec_update(sql) logger.info("end to update cinema min price")
def update_city_prices(city_name): """ 更新渠道电影价格信息 InnoDB默认事物级别是repeatable read,防止同一事物中读取的数据前后不一致,即幻读, 此处需获取cinema表最新状态,每次查询时需开启一个新的事物,即execute_update后要commit或者重新获取CinemaManager """ cinema_manager = CinemaManager.get_instance() price_manager = PriceManager.get_instance() try: city_info = cinema_manager.get_cinema_city_id(city_name) done = 0 channels = [PriceLM, PriceMT, PriceTB] for channel in channels: ids = cinema_manager.need_update_cinema_ids( channel.get_cinema_cls(), city_name) count = len(ids) if count > 0: city_id = city_info['id_{}'.format(channel.get_channel_name())] logger.info('run %s of city %s, %d cinemas need to be updated', channel.get_spider_name(), city_name, count) run_spider(channel.get_spider_name(), city_id=city_id) else: done += 1 logger.info('table %s of city %s need not to be updated', channel.get_table_name(), city_name) if done == 3: # 状态未更新时更新状态,手动在price_update_status插入一条城市记录 if not price_manager.is_cinema_price_update_done(city_name): price_manager.cinema_price_updated_done(city_name) else: logger.info('city %s, channels price status already updated', city_name) except Exception as e: logger.error("Unexpected Update {} Price Error: {}, {}".format( city_name, e, traceback.format_exc()))
def match_city_channels_price(city_name): """如果有渠道价格更新,并且所有的渠道都更新完毕则进行所有渠道价格匹配""" price_manager = PriceManager.clone() if price_manager.is_cinema_price_update_done(city_name): if not price_manager.is_cinema_price_match_done(city_name): try: price_manager.save_all_channels_price(city_name) price_manager.cinema_price_match_done(city_name) except Exception as e: logger.info("Unexpected Match {} Price Error: {}".format( city_name, e)) price_manager.cinema_price_match_done(city_name, -1) else: logger.info('city %s, price match status already update', city_name) else: logger.info('city %s, some channels price do not update done', city_name)
def match_all_channels(self, city): logger.info("begin to match all channels with city %s", city) self.manager.mach_all_channels(city) logger.info("end to match all channels with city %s", city)
def save_all_channels_price(self, city_name): """ 各渠道电影、电影院可能没有匹配成功,渠道也有能没有电影、电影院、电影售价信息 hive形式sql,mysql无full outer join, 只能使用left join union right join 导致下面执行复杂的sql replace into price(cinema_id, movie_id, show_date, begin, end, language, hall, price_mt, price_tb, price_lm) select COALESCE(mt_price_tmp.cinema_id, tb_price_tmp.cinema_id, lm_price_tmp.cinema_id) as cinema_id, COALESCE(mt_price_tmp.movie_id, tb_price_tmp.movie_id, lm_price_tmp.movie_id) as movie_id, COALESCE(mt_price_tmp.show_date, tb_price_tmp.show_date, lm_price_tmp.show_date) as show_date, COALESCE(mt_price_tmp.begin, tb_price_tmp.begin, lm_price_tmp.begin) as begin, COALESCE(mt_price_tmp.end, tb_price_tmp.end, lm_price_tmp.end) as end, COALESCE(mt_price_tmp.language, tb_price_tmp.language, lm_price_tmp.language) as language, COALESCE(mt_price_tmp.hall, tb_price_tmp.hall, lm_price_tmp.hall) as hall, COALESCE(mt_price_tmp.price, 0) as price_mt, COALESCE(tb_price_tmp.price, 0) as price_tb, COALESCE(lm_price_tmp.price, 0) as price_lm from ( select cinema.id_mt as cinema_id, movie.id_db as movie_id, show_date, begin, end, language, hall, price from cinema join price_mt on ( cinema.id_mt=price_mt.cinema_id) join movie on (movie.id_mt=price_mt.movie_id) ) mt_price_tmp full outer join ( select cinema.id_mt as cinema_id, movie.id_db as movie_id, show_date, begin, end, language, hall, price from cinema join price_tb on ( cinema.id_tb=price_tb.cinema_id) join movie on (movie.id_tb=price_tb.movie_id) ) tb_price_tmp on (mt_price_tmp.cinema_id = tb_price_tmp.cinema_id and mt_price_tmp.movie_id = tb_price_tmp.movie_id and mt_price_tmp.show_date = tb_price_tmp.show_date and mt_price_tmp.begin = tb_price_tmp.begin) full outer join ( select cinema.id_mt as cinema_id, movie.id_db as movie_id, show_date, begin, end, language, hall, price from cinema join price_lm on ( cinema.id_lm=price_lm.cinema_id) join movie on (movie.id_lm=price_lm.movie_id) ) lm_price_tmp on (COALESCE(mt_price_tmp.cinema_id, tb_price_tmp.cinema_id) = lm_price_tmp.cinema_id and COALESCE(mt_price_tmp.movie_id, tb_price_tmp.movie_id) = lm_price_tmp.movie_id and COALESCE(mt_price_tmp.show_date, tb_price_tmp.show_date) = lm_price_tmp.show_date and COALESCE(mt_price_tmp.begin, tb_price_tmp.begin) = lm_price_tmp.begin) -------------------------------------------------------------------------------------- 下面sql可查出电影在各个渠道同一个影院都有售卖时的价格信息,于上面sql将full join替换为 join时等价 replace into price(movie_id, cinema_id, show_date, begin, end, language, hall, price_mt, price_tb, price_lm) select t1.id_db as movie_id, t5.id_mt as cinema_id, t3.show_date as show_date, t3.begin as begin, t3.end as end, t3.language as language, t3.hall as hall, t3.price as price_mt, t4.price as price_tb, t2.price as price_lm from movie t1 join price_lm t2 on t1.id_lm = t2.movie_id join price_mt t3 on t1.id_mt = t3.movie_id join price_tb t4 on t1.id_tb = t4.movie_id join cinema t5 on (t2.cinema_id = t5.id_lm and t3.cinema_id=t5.id_mt and t4.cinema_id=t5.id_tb and t2.show_date=t3.show_date and t2.show_date=t4.show_date and t2.begin = t3.begin and t2.begin=t4.begin) """ sql = ''' replace into price(cinema_id, movie_id, show_date, begin, end, language, hall, price_mt, price_tb, price_lm) select COALESCE(mt_cinema_id, tb_cinema_id, lm_cinema_id) as cinema_id, COALESCE(mt_movie_id, tb_movie_id, lm_movie_id) as movie_id, COALESCE(mt_show_date, tb_show_date, lm_show_date) as show_date, COALESCE(mt_begin, tb_begin, lm_begin) as begin, COALESCE(mt_end, tb_end, lm_end) as end, COALESCE(mt_language, tb_language, lm_language) as language, COALESCE(mt_hall, tb_hall, lm_hall) as hall, COALESCE(mt_price, 10000) as price_mt, COALESCE(tb_price, 10000) as price_tb, COALESCE(lm_price, 10000) as price_lm from ( select mt_price_tmp.cinema_id as mt_cinema_id, mt_price_tmp.movie_id as mt_movie_id, mt_price_tmp.show_date as mt_show_date, mt_price_tmp.begin as mt_begin, mt_price_tmp.end as mt_end, mt_price_tmp.language as mt_language, mt_price_tmp.hall as mt_hall, mt_price_tmp.price as mt_price, tb_price_tmp.cinema_id as tb_cinema_id, tb_price_tmp.movie_id as tb_movie_id, tb_price_tmp.show_date as tb_show_date, tb_price_tmp.begin as tb_begin, tb_price_tmp.end as tb_end, tb_price_tmp.language as tb_language, tb_price_tmp.hall as tb_hall, tb_price_tmp.price as tb_price from ( select cinema.id_mt as cinema_id, movie.id_db as movie_id, show_date, begin, end, language, hall, price from cinema_mt join cinema on(cinema_mt.city='{}' and cinema_mt.id=cinema.id_mt) join price_mt on ( cinema.id_mt=price_mt.cinema_id) join movie on (movie.id_mt=price_mt.movie_id) ) mt_price_tmp left join ( select cinema.id_mt as cinema_id, movie.id_db as movie_id, show_date, begin, end, language, hall, price from cinema_mt join cinema on(cinema_mt.city='{}' and cinema_mt.id=cinema.id_mt) join price_tb on ( cinema.id_tb=price_tb.cinema_id) join movie on (movie.id_tb=price_tb.movie_id) ) tb_price_tmp on (mt_price_tmp.cinema_id = tb_price_tmp.cinema_id and mt_price_tmp.movie_id = tb_price_tmp.movie_id and mt_price_tmp.show_date = tb_price_tmp.show_date and mt_price_tmp.begin = tb_price_tmp.begin) union all select mt_price_tmp.cinema_id as mt_cinema_id, mt_price_tmp.movie_id as mt_movie_id, mt_price_tmp.show_date as mt_show_date, mt_price_tmp.begin as mt_begin, mt_price_tmp.end as mt_end, mt_price_tmp.language as mt_language, mt_price_tmp.hall as mt_hall, mt_price_tmp.price as mt_price, tb_price_tmp.cinema_id as tb_cinema_id, tb_price_tmp.movie_id as tb_movie_id, tb_price_tmp.show_date as tb_show_date, tb_price_tmp.begin as tb_begin, tb_price_tmp.end as tb_end, tb_price_tmp.language as tb_language, tb_price_tmp.hall as tb_hall, tb_price_tmp.price as tb_price from ( select cinema.id_mt as cinema_id, movie.id_db as movie_id, show_date, begin, end, language, hall, price from cinema_mt join cinema on(cinema_mt.city='{}' and cinema_mt.id=cinema.id_mt) join price_mt on ( cinema.id_mt=price_mt.cinema_id) join movie on (movie.id_mt=price_mt.movie_id) ) mt_price_tmp right join ( select cinema.id_mt as cinema_id, movie.id_db as movie_id, show_date, begin, end, language, hall, price from cinema_mt join cinema on(cinema_mt.city='{}' and cinema_mt.id=cinema.id_mt) join price_tb on ( cinema.id_tb=price_tb.cinema_id) join movie on (movie.id_tb=price_tb.movie_id) ) tb_price_tmp on (mt_price_tmp.cinema_id = tb_price_tmp.cinema_id and mt_price_tmp.movie_id = tb_price_tmp.movie_id and mt_price_tmp.show_date = tb_price_tmp.show_date and mt_price_tmp.begin = tb_price_tmp.begin) where mt_price_tmp.cinema_id is null ) mt_tb_price_tmp left join ( select cinema.id_mt as lm_cinema_id, movie.id_db as lm_movie_id, show_date as lm_show_date, begin as lm_begin, end as lm_end, language as lm_language, hall as lm_hall, price as lm_price from cinema_mt join cinema on(cinema_mt.city='{}' and cinema_mt.id=cinema.id_mt) join price_lm on ( cinema.id_lm=price_lm.cinema_id) join movie on (movie.id_lm=price_lm.movie_id) ) lm_price_tmp on (COALESCE(mt_tb_price_tmp.mt_cinema_id, mt_tb_price_tmp.tb_cinema_id) = lm_price_tmp.lm_cinema_id and COALESCE(mt_tb_price_tmp.mt_movie_id, mt_tb_price_tmp.mt_movie_id) = lm_price_tmp.lm_movie_id and COALESCE(mt_tb_price_tmp.mt_show_date, mt_tb_price_tmp.tb_show_date) = lm_price_tmp.lm_show_date and COALESCE(mt_tb_price_tmp.mt_begin, mt_tb_price_tmp.tb_begin) = lm_price_tmp.lm_begin) union all select COALESCE(mt_cinema_id, tb_cinema_id, lm_cinema_id) as cinema_id, COALESCE(mt_movie_id, tb_movie_id, lm_movie_id) as movie_id, COALESCE(mt_show_date, tb_show_date, lm_show_date) as show_date, COALESCE(mt_begin, tb_begin, lm_begin) as begin, COALESCE(mt_end, tb_end, lm_end) as end, COALESCE(mt_language, tb_language, lm_language) as language, COALESCE(mt_hall, tb_hall, lm_hall) as hall, COALESCE(mt_price, 10000) as price_mt, COALESCE(tb_price, 10000) as price_tb, COALESCE(lm_price, 10000) as price_lm from ( select mt_price_tmp.cinema_id as mt_cinema_id, mt_price_tmp.movie_id as mt_movie_id, mt_price_tmp.show_date as mt_show_date, mt_price_tmp.begin as mt_begin, mt_price_tmp.end as mt_end, mt_price_tmp.language as mt_language, mt_price_tmp.hall as mt_hall, mt_price_tmp.price as mt_price, tb_price_tmp.cinema_id as tb_cinema_id, tb_price_tmp.movie_id as tb_movie_id, tb_price_tmp.show_date as tb_show_date, tb_price_tmp.begin as tb_begin, tb_price_tmp.end as tb_end, tb_price_tmp.language as tb_language, tb_price_tmp.hall as tb_hall, tb_price_tmp.price as tb_price from ( select cinema.id_mt as cinema_id, movie.id_db as movie_id, show_date, begin, end, language, hall, price from cinema_mt join cinema on(cinema_mt.city='{}' and cinema_mt.id=cinema.id_mt) join price_mt on ( cinema.id_mt=price_mt.cinema_id) join movie on (movie.id_mt=price_mt.movie_id) ) mt_price_tmp left join ( select cinema.id_mt as cinema_id, movie.id_db as movie_id, show_date, begin, end, language, hall, price from cinema_mt join cinema on(cinema_mt.city='{}' and cinema_mt.id=cinema.id_mt) join price_tb on ( cinema.id_tb=price_tb.cinema_id) join movie on (movie.id_tb=price_tb.movie_id) ) tb_price_tmp on (mt_price_tmp.cinema_id = tb_price_tmp.cinema_id and mt_price_tmp.movie_id = tb_price_tmp.movie_id and mt_price_tmp.show_date = tb_price_tmp.show_date and mt_price_tmp.begin = tb_price_tmp.begin) union all select mt_price_tmp.cinema_id as mt_cinema_id, mt_price_tmp.movie_id as mt_movie_id, mt_price_tmp.show_date as mt_show_date, mt_price_tmp.begin as mt_begin, mt_price_tmp.end as mt_end, mt_price_tmp.language as mt_language, mt_price_tmp.hall as mt_hall, mt_price_tmp.price as mt_price, tb_price_tmp.cinema_id as tb_cinema_id, tb_price_tmp.movie_id as tb_movie_id, tb_price_tmp.show_date as tb_show_date, tb_price_tmp.begin as tb_begin, tb_price_tmp.end as tb_end, tb_price_tmp.language as tb_language, tb_price_tmp.hall as tb_hall, tb_price_tmp.price as tb_price from ( select cinema.id_mt as cinema_id, movie.id_db as movie_id, show_date, begin, end, language, hall, price from cinema_mt join cinema on(cinema_mt.city='{}' and cinema_mt.id=cinema.id_mt) join price_mt on ( cinema.id_mt=price_mt.cinema_id) join movie on (movie.id_mt=price_mt.movie_id) ) mt_price_tmp right join ( select cinema.id_mt as cinema_id, movie.id_db as movie_id, show_date, begin, end, language, hall, price from cinema_mt join cinema on(cinema_mt.city='{}' and cinema_mt.id=cinema.id_mt) join price_tb on ( cinema.id_tb=price_tb.cinema_id) join movie on (movie.id_tb=price_tb.movie_id) ) tb_price_tmp on (mt_price_tmp.cinema_id = tb_price_tmp.cinema_id and mt_price_tmp.movie_id = tb_price_tmp.movie_id and mt_price_tmp.show_date = tb_price_tmp.show_date and mt_price_tmp.begin = tb_price_tmp.begin) where mt_price_tmp.cinema_id is null ) mt_tb_price_tmp right join ( select cinema.id_mt as lm_cinema_id, movie.id_db as lm_movie_id, show_date as lm_show_date, begin as lm_begin, end as lm_end, language as lm_language, hall as lm_hall, price as lm_price from cinema_mt join cinema on(cinema_mt.city='{}' and cinema_mt.id=cinema.id_mt) join price_lm on ( cinema.id_lm=price_lm.cinema_id) join movie on (movie.id_lm=price_lm.movie_id) ) lm_price_tmp on (COALESCE(mt_tb_price_tmp.mt_cinema_id, mt_tb_price_tmp.tb_cinema_id) = lm_price_tmp.lm_cinema_id and COALESCE(mt_tb_price_tmp.mt_movie_id, mt_tb_price_tmp.mt_movie_id) = lm_price_tmp.lm_movie_id and COALESCE(mt_tb_price_tmp.mt_show_date, mt_tb_price_tmp.tb_show_date) = lm_price_tmp.lm_show_date and COALESCE(mt_tb_price_tmp.mt_begin, mt_tb_price_tmp.tb_begin) = lm_price_tmp.lm_begin) where COALESCE(mt_tb_price_tmp.mt_cinema_id, mt_tb_price_tmp.tb_cinema_id) is null '''.format(city_name, city_name, city_name, city_name, city_name, city_name, city_name, city_name, city_name, city_name) logger.info("city %s, start to match cinema price", city_name) self.db.exec_update(sql) logger.info("city %s, end to match cinema price", city_name)
def remove_invalid_match(self, cinema_cls, city, step): """删除无效匹配""" logger.info("step %d, begin to remove invalid match with city %s", step, city) self.manager.remove_invalid_match(cinema_cls, step) logger.info("step %d, end to remove invalid match with city %s", step, city)
def __init__(self, **kwargs): self.city_id = kwargs.get('city_id') self.city_name = CityLM.get_city_name(self.city_id) logger.info("city id %s, city name %s", self.city_id, self.city_name)
def update_movies(): """更新渠道电影信息""" channels = [MovieLM, MovieMT, MovieTB, MovieDB] for channel in channels: logger.info('%s to be updated', channel.get_table_name()) run_spider(channel.get_spider_name(), city_id=289)
def update_cinema_min_price(): """更新影院最低价""" logger.info('start to update cinema min price') price_manager = PriceManager.clone() price_manager.update_cinema_min_price() logger.info('end to update cinema min price')