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'])
