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_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_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 update_shop(): raw_shops_sql = "select vendor_id,vendor_name,description,address,recomm_dish from vendor_miqilin where 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_name = shop['vendor_name'] shop_address = shop['address'] for check_shop in check_shops: flag = check_address(shop_name,shop_address,check_shop['vendor_name'],check_shop['address']) if flag: update_sql = 'update vendor_miqilin set dish=:dish where vendor_id=:vendor_id' update_data = { 'dish':check_shop['recomm_dish'], 'vendor_id':shop['vendor_id'] } effect = dbmysql.edit(update_sql,update_data) if effect: num += 1 print(shop_name) print(num)
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 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_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 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 main(): sql = "select * from vendor_miqilin where description = '' and ctrip_description is null" results = dbmysql.fetchall(sql) for item in results: item = dict(item) parse(item)