def add_guanjia(): guanjia_hongkong = 'select * from vendor_miqilin_hongkong where vendor_city = "香港"' guanjia_hongkong = dbmysql.fetchall(guanjia_hongkong) add_hongkong = 'select * from vendor_miqilin' add_hongkong = dbmysql.fetchall(add_hongkong) add_id = [] for item in add_hongkong: for i in guanjia_hongkong: flag = check(item['vendor_name'],item['address'],i['name'],i['address']) if flag == 2: if item['lat'] == i['lat'] and item['lng'] == i['lng']: flag = 1 else: flag = 0 if flag == 1: add_id.append(item['vendor_id']) break if flag == 0: add_sql = "insert into vendor_miqilin_hongkong(name, book_status, facilities,district, landmark, preferential, environment, " \ "description, payment, price_class,michelin_star,quality, dish,recomm_dish, cuisine, taste,category, " \ "people_group,open_time, close_time, price, address, lat, lng, vendor_city, vendor_url)" \ " values(:vendor_name, :book_status, :facilities,:district, :landmark, :preferential, " \ ":environment, :description, :payment, :price_class,:michelin_star,:quality, :dish," \ ":recomm_dish, :cuisine, :taste, :category, :people_group, :open_time, " \ ":close_time, :price, :address, :lat, :lng, :vendor_city, :vendor_url)" dbmysql.edit(add_sql, item) print(add_id)
def update_price_class(): sql = 'select vendor_id,price from vendor_miqilin_price' results = dbmysql.fetchall(sql) for item in results: price_class = get_price_class(item[1]) sql2 = "update vendor_miqilin_price set price_class=:price_class where vendor_id=:vendor_id" dbmysql.edit(sql2,params={'price_class':price_class,'vendor_id': item[0]})
def update_price(): # sql1 = "select vendor_id,name,price from vendor_miqilin where vendor_city='香港'" # result_raws = dbmysql.fetchall(sql1) # print(len(result_raws)) # return # for item in result_raws: # sql2 = "select price from price where name=:name" # price = dbmysql.first(sql2,params={'name':item[1]}) # if price: # pass # # sql3 = "update vendor_miqilin set price = :price where vendor_id=:vendor_id" # # dbmysql.edit(sql3,params={'price':price[0],'vendor_id':item[0]}) # else: # print(item[1]) # sql = "select name,price from price" # names = dbmysql.fetchall(sql) # for item in names: # sql2 = "select name,price from vendor_miqilin where vendor_city='纽约' and name=:name" # flag = dbmysql.first(sql2,params={'name':item[0]}) # if not flag: # print (str(item[0])+':'+str(item[1])) # 香港 sql1 = "select vendor_id,name,vendor_url,vendor_city from vendor_miqilin_price" results = dbmysql.fetchall(sql1) print(len(results)) city_list = get_dianping_city() city_list[u'纽约'] = 2395 city_list[u'伦敦'] = 2464 # print([item[0] for item in results]) for item in results: shop_url = item[2] shop_id = shop_url.split('/')[-1] city_code = city_list.get(item[3]) avg_price_url = 'http://www.dianping.com/overseas/shop/ajax/reviewAndStar?shopId=%s&cityId=%s\ &mainCategoryId=102' % (shop_id,city_code) response = request.get(avg_price_url) price = get_avg_price(response) # sql2 = "select name,price from price_detail where name=:name" # price = dbmysql.first(sql2,{'name':item[1]}) if price: print(price) sql3 = "update vendor_miqilin_price set price = :price,flag = 0 where vendor_id=:vendor_id" dbmysql.edit(sql3, params={'price': price, 'vendor_id': item[0]}) else: sql3 = "update vendor_miqilin_price set flag = 1 where vendor_id=:vendor_id" dbmysql.edit(sql3, params={'vendor_id': item[0]}) print(str(item[0]) + ' ' + item[1])
def save_data(item): sql = 'update vendor_miqilin set description=:description,ctrip_dish=:ctrip_dish,'+\ 'ctrip_price=:ctrip_price,ctrip_url=:ctrip_url,ctrip_address=:ctrip_address,ctrip_description=:ctrip_description'+\ ' where vendor_id=:vendor_id' data = { 'description':item['description'], 'ctrip_dish':item['ctrip_dish'], 'ctrip_price':item['ctrip_price'], 'ctrip_url':item['ctrip_url'], 'ctrip_address':item['ctrip_address'], 'ctrip_description':item['ctrip_description'], 'vendor_id':item['vendor_id'] } dbmysql.edit(sql,data)
def save_date(item): sql = 'insert into vendor_miqilin_mafengwo(vendor_name,description,price_class,cuisine,characters,category,price,address,vendor_city,'+\ 'vendor_url,business_hours,score_details,comment)' +\ ' values(:vendor_name,:description,:price_class,' +\ ':cuisine,:characters,:category,:price,:address,:vendor_city,' +\ ':vendor_url,:business_hours,:score_details,:comment)' return dbmysql.edit(sql, item)
def filter_data(): sql = 'select * from vendor_miqilin' data = dbmysql.fetchall(sql) for item in data: cuisine = re.sub(r'\(.*?\)','',item['cuisine']).replace('、',';') facilities = filter_facilities(item['facilities']) preferential = filter_preferential(item['preferential']) update_sql = 'update vendor_miqilin set cuisine=:cuisine,facilities=:facilities,preferential=:preferential where vendor_id=:vendor_id' params = { 'cuisine':cuisine, 'facilities':facilities, 'preferential':preferential, 'vendor_id':item['vendor_id'] } dbmysql.edit(update_sql,params=params)
def save_date(item): sql = 'insert into vendor_miqilin(vendor_name,book_status,facilities,district,landmark,preferential,environment,description,payment,price_class,'+\ 'michelin_star,quality,brand,dish,recomm_dish,cuisine,taste,characters,category,people_group,open_time,close_time,price,address,lat,lng,vendor_city,'+\ 'vendor_url,price_range,business_hours,score_details,openrich_url,tripadvisor_url,phone,tripadvisor_cuisine,tripadvisor_character,appraise)' +\ ' values(:vendor_name,:book_status,:facilities,:district,:landmark,:preferential,:environment,:description,:payment,:price_class,' +\ ':michelin_star,:quality,:brand,:dish,:recomm_dish,:cuisine,:taste,:characters,:category,:people_group,:open_time,:close_time,:price,:address,:lat,:lng,:vendor_city,' +\ ':vendor_url,:price_range,:business_hours,:score_details,:openrich_url,:tripadvisor_url,:phone,:tripadvisor_cuisine,:tripadvisor_character,:appraise)' return dbmysql.edit(sql, item)
def save_date(item): sql = 'insert into vendor_miqilin_quna(vendor_name,description,price_class,michelin_star,quality,'+\ 'dish,recomm_dish,cuisine,characters,category,people_group,price,address,vendor_city,'+\ 'vendor_url,business_hours,score_details,phone,appraise)' +\ ' values(:vendor_name,:description,:price_class,:michelin_star,:quality,' +\ ':dish,:recomm_dish,:cuisine,:characters,:category,:people_group,:price,:address,:vendor_city,' +\ ':vendor_url,:business_hours,:score_details,:phone,:appraise)' return dbmysql.edit(sql, item)
def update_price(): sql = 'select vendor_id,price from vendor_miqilin' result = dbmysql.fetchall(sql) i = 0 for item in result: price = item['price'] * 0.8054 price = int(price) price_class = get_price_class(price) update_sql = 'update vendor_miqilin set price=:price,price_class=:price_class where vendor_id=:vendor_id' params = { 'price':price, 'price_class':price_class, 'vendor_id':item['vendor_id'], } i += 1 dbmysql.edit(update_sql,params) print(i)
def update_shop(): raw_shops_sql = "select vendor_id,vendor_name,description,address,recomm_dish from vendor_miqilin where description = ''" check_shops_sql = "select vendor_name,description,address,recomm_dish from vendor_miqilin_quna where description != ''" raw_shops = dbmysql.fetchall(raw_shops_sql) check_shops = dbmysql.fetchall(check_shops_sql) for shop in raw_shops: shop_name = shop['vendor_name'] shop_address = shop['address'] for check_shop in check_shops: flag = check(shop_name, shop_address, check_shop['vendor_name'], check_shop['address']) if flag: update_sql = 'update vendor_miqilin set description=:description where vendor_id=:vendor_id' update_data = { 'description': check_shop['description'], 'vendor_id': shop['vendor_id'] } dbmysql.edit(update_sql, update_data)
def update_latlng(): sql = 'select * from vendor_miqilin_hongkong where vendor_id' data = dbmysql.fetchall(sql) for item in data: address = item['address'] result = request.get(baidu_address%(address,'香港')) result = json.loads(result.content) if result.get('status') == 0: update_sql = 'update vendor_miqilin set lat=:lat,lng=:lng where vendor_id=:vendor_id' params = { 'lat':result['result']['location'].get('lat'), 'lng':result['result']['location'].get('lng'), 'vendor_id': item['vendor_id'] } dbmysql.edit(update_sql, params) else: update_sql = 'update vendor_miqilin set lat_flag = 1 where vendor_id=:vendor_id' dbmysql.edit(update_sql,{'vendor_id': item['vendor_id']}) print(str(item['vendor_id']))
def update_langconv(): sql = 'select * from vendor_miqilin' data = dbmysql.fetchall(sql) for item in data: name = switch_lang.Traditional2Simplified(item['vendor_name']) description = switch_lang.Traditional2Simplified(item['description']) dish = switch_lang.Traditional2Simplified(item['dish']) recomm_dish = switch_lang.Traditional2Simplified(item['recomm_dish']) address = switch_lang.Traditional2Simplified(item['address']) update_sql = 'update vendor_miqilin set vendor_name=:vendor_name,description=:description,dish=:dish,' \ 'recomm_dish=:recomm_dish,address=:address where vendor_id=:vendor_id' params = { 'vendor_name':name, 'description':description, 'dish':dish, 'recomm_dish':recomm_dish, 'address':address, 'vendor_id':item['vendor_id'] } dbmysql.edit(update_sql,params)
def get_airport(response): if response: url = response.url html = etree.HTML(response.content) bont = html.xpath("//div[@class='jc-int-bont']/p//text()") city = bont[0] if bont else '' code = bont[1] if len(bont) > 1 else '' zh_name = html.xpath("//div[@class='xt01']/h1/b/text()") en_name = html.xpath("//div[@class='mod-c01']/div/text()") zh_name = zh_name[0] if zh_name else '' en_name = en_name[0] if en_name else '' item = dict( city=city, code=code, url=url, zh_name=zh_name, en_name=en_name, ) sql = 'insert into aifei(city,code,url,zh_name,en_name) values(:city,:code,:url,:zh_name,:en_name)' dbmysql.edit(sql, item)
def parse(): for i in range(1,24): url = start_url + '?page=%d'%i response = request.get(url) if response: html = etree.HTML(response.content) shops = html.xpath("//li[@class='poi-item poi-item-restaurant']") for shop in shops: name = shop.xpath("div[@class='poi-item-name truncate']/a/text()")[0] row_price = shop.xpath(".//div[@class='poi-item-price']//text()") row_price = [i for i in row_price if i.strip()] price = int(row_price[1][1:]) + int(row_price[-1][1:]) price = int(round(price/2 * 6.3279)) row_price = ' '.join(row_price) item = dict( name = name, price = price, row_price = row_price ) sql = 'insert into price(name,price,row_price) values(:name,:price,:row_price)' dbmysql.edit(sql,item)
def get_airport(response): html = etree.HTML(response.content) datas = html.xpath("//table[@class='ctable']//tr") datas.pop(0) for item in datas: city = item.xpath(".//span[@class='bblue']/text()")[0] three_code = item.xpath("td[2]/text()") three_code = three_code[0] if three_code else '' four_code = item.xpath("td[3]/text()") four_code = four_code[0] if four_code else '' airport = item.xpath("td[4]/text()") airport = airport[0] if airport else '' english_name = item.xpath("td[5]/text()") english_name = english_name[0] if english_name else '' item = dict(city=city, three_code=three_code, four_code=four_code, airport_name=airport, english_name=english_name) sql = 'insert into airport(city,three_code,four_code,airport_name,english_name) values(:city,:three_code,:four_code,:airport_name,:english_name)' dbmysql.edit(sql, item)
def handle_privce(): sql = "select id,detail_price from price_detail" results = dbmysql.fetchall(sql) for item in results: id = item[0] # print(type(item[1])) # detail_price = item[1].decode('utf-8') detail_price = item[1].replace("'", '"') detail_price = json.loads(item[1].replace("'", '"'),encoding='utf-8') price_list = [] for i in detail_price.values(): if i['单点'.decode('utf-8')]: price_list.append(i['单点'.decode('utf-8')].replace('¥','')) if i['套餐'.decode('utf-8')]: price_list.append(i['套餐'.decode('utf-8')].replace('¥','')) max = 0 max_index = 0 for i in price_list: i = i.lower() i = re.sub(r'\(weekend.*?\)', '', i) if i.find('-') > -1: if int(i.split('-')[1]) > max: max_index = i max = int(i.split('-')[1]) else: if int(i) > max: max_index = i max = int(i) if max_index.find('-')>-1: price = int(max_index.split('-')[0]) + int(max_index.split('-')[1]) price = int(price/2) else: price = int(max_index) sql = 'update price_detail set price=:price where id=:id' dbmysql.edit(sql,{'price':price,'id':id})
def update2_shop(): raw_shops_sql = "select vendor_id,vendor_name,address,ctrip_address,ctrip_dish from vendor_miqilin where dish = '' and ctrip_dish != ''" # check_shops_sql = "select vendor_name,description,address,recomm_dish from vendor_miqilin_quna where recomm_dish != ''" raw_shops = dbmysql.fetchall(raw_shops_sql) # check_shops = dbmysql.fetchall(check_shops_sql) num = 0 for shop in raw_shops: # shop = dict(shop) shop_name = shop['vendor_name'] shop_address = shop['address'] flag = check_address(shop_address, shop['ctrip_address']) if flag: update_sql = 'update vendor_miqilin set dish=:dish where vendor_id=:vendor_id' update_data = { 'dish': shop['ctrip_dish'], 'vendor_id': shop['vendor_id'] } effect = dbmysql.edit(update_sql, update_data) if effect: num += 1 print(shop_name) print(num)
def save(item): sql = 'insert into michelin(name,links,book_status,facilities,description,pyment,low_price,detail_price,michelin_star,cuisine,service_time,address,phone,district,landmark,quality,brand,recomm_dishes,taste,characters,category,people_group,lat,lng,country,city,price,other_name,dish) values(:name,:links,:book_status,:facilities,:description,:pyment,:low_price,:detail_price,:michelin_star,:cuisine,:service_time,:address,:phone,:district,:landmark,:quality,:brand,:recomm_dishes,:taste,:characters,:category,:people_group,:lat,:lng,:country,:city,:price,:other_name,:dish)' dbmysql.edit(sql, item) print('%s插入成功' % item['name'])