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)
