def getSoapClient(url):
_INFO('', ['trying get soap client, url = ' + url])
try:
client = Client(url)
_INFO('', ['Getting Soap Clent, Success'])
#print client
except Exception, e:
_ERROR('', ['Getting Soap Clent, Failed'])
sys.exit(-1)
def get_dist_by_map(map_1, map_2):
try:
return getDistSimply(float(map_1.split(',')[0]),
float(map_1.split(',')[1]),
float(map_2.split(',')[0]),
float(map_2.split(',')[1]))
except Exception, e:
_INFO('get_dist', ['map = ' + map_1 + '\t' + map_2])
return 100000000000
def data_writer(room_list, taskcontent):
if room_list == []:
_ERROR('', ['room_list.size == 0'])
return
try:
InsertHotel_room(room_list)
_INFO('', [taskcontent + ' [success]'])
_INFO('', ['with ' + str(len(room_list)) + ' values!'])
except Exception, e:
_ERROR('', [taskcontent + '[failed]'])
_ERROR('', [str(e)])
def push_flight_data(source_db_name,source_sql_table_name,dest_db_name,flight_info_sql_table,flight_ticket_info_sql_table):
flight_day_info_fuse_dic = fuse_flight_data(source_db_name,source_sql_table_name)
try:
rds = redis.Redis(host='127.0.0.1', port=6379, db=0)
conn = MySQLdb.connect(host='127.0.0.1', user='', charset='utf8',passwd='', db=source_db_name)
cursor = conn.cursor()
except Exception, e:
_INFO('push flight data',['redis or mysql connection error'])
_INFO('push flight data',['error code = ' + str(e)])
return None,None
def loadCandidateHotel():
hotelSet = set()
conn = MySQLdb.connect(host='127.0.0.1', user='******', charset='utf8',passwd='', db='onlinedb')
cursor = conn.cursor()
sql = " select H.uid from hotel H, city C where H.city_mid=C.id and C.status='Open' "
n = cursor.execute(sql)
datas = cursor.fetchall()
for data in datas:
hid = data[0].encode('utf-8').strip()
hotelSet.add(hid)
_INFO('load candidate hotel',['available hotel size = ' + str(len(hotelSet))])
return hotelSet
def parsePrice(content_temp, city):
all_price = []
content = xmlescape(content_temp).replace('\n', '')
#print content_temp
try:
each_hotel_content_list = rateplans_pat.findall(content)
if len(each_hotel_content_list) == 0:
return all_price
_INFO('ctripHotel::parseRoom',
['Parse price failed because of no hotel found'])
except Exception, e:
_ERROR('ctripHotel::parseRoom',
['Parse price failed because of no hotel found',
str(e)])
def loadOpenedHotelSource():
openedSource = set()
conn = MySQLdb.connect(host='127.0.0.1', user='******', charset='utf8',passwd='', db='onlinedb')
cursor = conn.cursor()
sql = " select name from source where status='Open' and type='hotel';"
n = cursor.execute(sql)
datas = cursor.fetchall()
cursor.close()
conn.close()
for data in datas:
source = data[0].encode('utf-8').strip()
openedSource.add(source)
_INFO('loadOpenedHotelSource',['opened source size:' + str(len(openedSource))])
return openedSource
def push_train_data(source_db_name,source_sql_table_name,dest_db_name,train_info_sql_table,train_ticket_info_sql_table):
conn = MySQLdb.connect(host='127.0.0.1', user='', charset='utf8',passwd='', db=source_db_name)
cursor = conn.cursor()
sql = "select train_no,train_type,train_corp,dept_city,dept_id,dest_city,dest_id,dept_day,dept_time,dest_time,dur,price,tax,currency,seat_type,real_class,source,return_rule,stopid,stoptime,daydiff,stop,update_time from " + source_sql_table_name + " ;"
#print 'sql: ' + sql
_INFO('push train data',['get train data from database...'])
try:
n = cursor.execute(sql)
datas = cursor.fetchall()
except Exception, e:
_INFO('push train data',['Error','execute sql error!'])
sys.exit(1)
def loadForexInfo():
forex_rate_dict = {}
conn = MySQLdb.connect(host='127.0.0.1', user='******', charset='utf8',passwd='', db='onlinedb')
cursor = conn.cursor()
sql = 'select currency_code, rate from exchange;'
n = cursor.execute(sql)
datas = cursor.fetchall()
cursor.close()
conn.close()
for data in datas:
forex = data[0].encode('utf-8').strip()
rate = data[1]
forex_rate_dict[forex] = rate
_INFO('loadForexInfo', ['forex_rate_dict.size = ' + str(len(forex_rate_dict))])
return forex_rate_dict
def loadHidUidInfo():
sid_uid_dict = {}
conn = MySQLdb.connect(host='127.0.0.1', user='******', charset='utf8',passwd='', db='onlinedb')
cursor = conn.cursor()
sql = "select source, sid, uid from hotel_unid where status='Open';"
n = cursor.execute(sql)
datas = cursor.fetchall()
cursor.close()
conn.close()
for data in datas:
source = data[0].encode('utf-8').strip()
sid = data[1].encode('utf-8').strip()
uid = data[2].encode('utf-8').strip()
sid_uid_dict[source + '_' + sid] = uid
_INFO('loadHidUidInfo', ['sid_uid_dict.size = ' + str(len(sid_uid_dict))])
return sid_uid_dict
def push_flight_data(source_db_name, source_sql_table_name, dest_db_name,
flight_info_sql_table, flight_ticket_info_sql_table):
flight_day_info_fuse_dic = fuse_flight_data(source_db_name,
source_sql_table_name)
try:
rds = redis.Redis(host='127.0.0.1', port=6379, db=0)
conn = MySQLdb.connect(host='127.0.0.1',
user='',
charset='utf8',
passwd='',
db=source_db_name)
cursor = conn.cursor()
except Exception, e:
_INFO('push flight data', ['redis or mysql connection error'])
_INFO('push flight data', ['error code = ' + str(e)])
return None, None
def fuse_flight_data(db_name,table_name):
conn = MySQLdb.connect(host='127.0.0.1', user='******', charset='utf8',passwd='', db=db_name)
cursor = conn.cursor()
flight_info = {}
flight_day_info = {}
src_table = table_name
sql = "select flight_no,plane_type,flight_corp,dept_id,dest_id,dept_time,dest_time,daydiff,seat_type,real_class,stop_id,source,dept_day,stop_time from " + src_table + " ;";
try:
cursor.execute(sql)
datas = cursor.fetchall()
except Exception, e:
_INFO('fuse flight data',['execute sql error! sql = ' + sql])
_INFO('fuse flight data',['Error! ' + str(e)])
return flight_info, flight_day_info
def loadCandidateHotel():
hotelSet = set()
conn = MySQLdb.connect(host='127.0.0.1',
user='******',
charset='utf8',
passwd='',
db='onlinedb')
cursor = conn.cursor()
sql = " select H.uid from hotel H, city C where H.city_mid=C.id and C.status='Open' "
n = cursor.execute(sql)
datas = cursor.fetchall()
for data in datas:
hid = data[0].encode('utf-8').strip()
hotelSet.add(hid)
_INFO('load candidate hotel',
['available hotel size = ' + str(len(hotelSet))])
return hotelSet
def loadForexInfo():
forex_rate_dic = {}
conn = MySQLdb.connect(host='127.0.0.1', user='******', charset='utf8',passwd='', db='onlinedb')
cursor = conn.cursor()
sql = 'select currency_code, rate from exchange;'
n = cursor.execute(sql)
datas = cursor.fetchall()
for data in datas:
forex = data[0].encode('utf-8').strip()
rate = data[1]
forex_rate_dic[forex] = rate
_INFO('load forex info',['forex_size = ' + str(len(forex_rate_dic))])
cursor.close()
conn.close()
return forex_rate_dic
def loadOpenedHotelSource():
openedSource = set()
conn = MySQLdb.connect(host='127.0.0.1',
user='******',
charset='utf8',
passwd='',
db='onlinedb')
cursor = conn.cursor()
sql = " select name from source where status='Open' and type='hotel';"
n = cursor.execute(sql)
datas = cursor.fetchall()
cursor.close()
conn.close()
for data in datas:
source = data[0].encode('utf-8').strip()
openedSource.add(source)
_INFO('loadOpenedHotelSource',
['opened source size:' + str(len(openedSource))])
return openedSource
def fuse_flight_data(db_name, table_name):
conn = MySQLdb.connect(host='127.0.0.1',
user='******',
charset='utf8',
passwd='',
db=db_name)
cursor = conn.cursor()
flight_info = {}
flight_day_info = {}
src_table = table_name
sql = "select flight_no,plane_type,flight_corp,dept_id,dest_id,dept_time,dest_time,daydiff,seat_type,real_class,stop_id,source,dept_day,stop_time from " + src_table + " ;"
try:
cursor.execute(sql)
datas = cursor.fetchall()
except Exception, e:
_INFO('fuse flight data', ['execute sql error! sql = ' + sql])
_INFO('fuse flight data', ['Error! ' + str(e)])
return flight_info, flight_day_info
def loadHidUidInfo():
sid_uid_dict = {}
conn = MySQLdb.connect(host='127.0.0.1',
user='******',
charset='utf8',
passwd='',
db='onlinedb')
cursor = conn.cursor()
sql = "select source, sid, uid from hotel_unid where status='Open';"
n = cursor.execute(sql)
datas = cursor.fetchall()
cursor.close()
conn.close()
for data in datas:
source = data[0].encode('utf-8').strip()
sid = data[1].encode('utf-8').strip()
uid = data[2].encode('utf-8').strip()
sid_uid_dict[source + '_' + sid] = uid
_INFO('loadHidUidInfo', ['sid_uid_dict.size = ' + str(len(sid_uid_dict))])
return sid_uid_dict
def load_city_data(db_ip,db_name,table_name):
conn = MySQLdb.connect(host = dev_db_ip,user = dev_db_user,passwd = dev_db_pwd,db = dev_db_name,charset='utf8')
cursor = conn.cursor()
#sql = "select id,name,country,map_info from " + table_name
sql = "select %s.id,%s.name,country.name,%s.map_info from %s,country where %s.country_id=country.mid" % \
(table_name, table_name, table_name, table_name, table_name)
print sql
cursor.execute(sql)
cid2name = {}
cid2mapInfo = {}
for data in cursor.fetchall():
if None in data:
continue
cid = data[0].encode('utf-8')
city = data[1].encode('utf-8').strip().lower()
country = data[2].encode('utf-8').strip().lower()
map_info = data[3].encode('utf-8').strip()
try:
tmp_list = map_info.strip().split(',')
lng = float(tmp_list[0])
lat = float(tmp_list[1])
except:
continue
cid2name[cid] = (city,country)
cid2mapInfo[cid] = map_info
_INFO('Load ready city',['ready city count:' + str(len(cid2name))])
return cid2name,cid2mapInfo
def getNewestPrice(candHotelSet, forex_rate_dict, sid_udi_dict, db_name, table_name, city, sc,opened_source_set):
_INFO('getNewestPrice', ['Begin'])
try:
rds = redis.Redis(host='127.0.0.1', port=6379, db=0)
conn = MySQLdb.connect(host='127.0.0.1', user='******', charset='utf8',passwd='', db=db_name)
cursor = conn.cursor()
except Exception,e :
_INFO('getNewestPrice',['redis or mysql connection fail!'])
_INFO('getNewestPrice',['error code = ' + str(e)])
return None
def getNewestPrice(candHotelSet, forex_rate_dict, sid_udi_dict, db_name,
table_name, city, sc, opened_source_set):
_INFO('getNewestPrice', ['Begin'])
try:
rds = redis.Redis(host='127.0.0.1', port=6379, db=0)
conn = MySQLdb.connect(host='127.0.0.1',
user='******',
charset='utf8',
passwd='',
db=db_name)
cursor = conn.cursor()
except Exception, e:
_INFO('getNewestPrice', ['redis or mysql connection fail!'])
_INFO('getNewestPrice', ['error code = ' + str(e)])
return None
elif (len(city) <= 0 or city == 'NULL') and (len(sc) > 0 and sc != 'NULL'):
sql += ' where source = "' + sc + '" ;'
else:
sql += ';'
n = cursor.execute(sql)
datas = cursor.fetchall()
cursor.close()
conn.close()
key_roominfo_dict = {}
key_set = set([])
if len(datas) == 0:
_INFO('getNewestPrice',
['city = ' + city + ', sc = ' + sc + ', no result'])
return {}
sid_set = set(sid_udi_dict.keys())
c = 0
for data in datas:
c += 1
if c == 1 or c % 10000 == 0:
_INFO('getNewestPrice', ['c = ' + str(c)])
info = {}
source = data[2].encode('utf-8').strip() #source
real_source = data[5].encode('utf-8').strip() #real_source
sql += ' where source = "' + sc + '" ;'
else:
sql += ';'
n = cursor.execute(sql)
datas = cursor.fetchall()
cursor.close()
conn.close()
key_roominfo_dict = {}
key_set = set([])
if len(datas) == 0:
_INFO('getNewestPrice', ['city = ' + city + ', sc = ' + sc + ', no result'])
return {}
sid_set = set(sid_udi_dict.keys())
c = 0
for data in datas:
c += 1
if c == 1 or c % 10000 == 0:
_INFO('getNewestPrice', ['c = ' + str(c)])
info = {}
source = data[2].encode('utf-8').strip() #source
real_source = data[5].encode('utf-8').strip() #real_source
user='',
charset='utf8',
passwd='',
db=source_db_name)
cursor = conn.cursor()
except Exception, e:
_INFO('push flight data', ['redis or mysql connection error'])
_INFO('push flight data', ['error code = ' + str(e)])
return None, None
sql = "select flight_no,plane_type,flight_corp,dept_id,dest_id,dept_day,dept_time,dest_time,dur,rest,price,tax,surcharge,currency,seat_type,real_class,stop_id,stop_time,daydiff,source,return_rule,stop,updatetime from " + source_sql_table_name + " ;"
#print 'sql: ' + sql
_INFO('push flight data', ['get flight data from database...'])
try:
n = cursor.execute(sql)
datas = cursor.fetchall()
except Exception, e:
_INFO('push flight data',
['Error', 'execute sql error!', 'sql = ' + str(sql)])
sys.exit(1)
cursor.close()
conn.close()
_INFO('push flight data', ['get flight data from sql database done!'])
def getRatePlanByHotelCode(client, hotelcode_set, from_date, to_date, city):
AllianceID = '15951'
SID = '439022'
SecretKey = '4F3E7530-391A-45A3-829B-880FFBBC4264'
TimeStamp = str(int(time.time()))
RequestType = 'OTA_HotelSearch'
Signature = Signate(AllianceID, SID, SecretKey, TimeStamp, RequestType)
#print Signature
req_xml = minidom.getDOMImplementation()
dom = req_xml.createDocument(None, 'Request', None)
root = dom.documentElement
header = dom.createElement('Header')
header.setAttribute('AllianceID', AllianceID)
header.setAttribute('SID', SID)
header.setAttribute('TimeStamp', TimeStamp)
header.setAttribute('RequestType', RequestType)
header.setAttribute('Signature', Signature)
hotel_request = dom.createElement('HotelRequest')
request_body = dom.createElement('RequestBody')
request_body.setAttribute('xmlns:ns',
'http://www.opentravel.org/OTA/2003/05')
request_body.setAttribute('xmlns:xsi',
'http://www.w3.org/2001/XMLSchema-instance')
request_body.setAttribute('xmlns:xsd', 'http://www.w3.org/2001/XMLSchema')
ns_ota_hsr = dom.createElement('ns:OTA_HotelRatePlanRQ')
ns_ota_hsr.setAttribute('Version', '1.0')
ns_ota_hsr.setAttribute('TimeStamp', '2012-05-01T00:00:00.000+08:00')
ns_rate_plans = dom.createElement('ns:RatePlans')
ns_rate_plan = dom.createElement('ns:RatePlan')
ns_date_range = dom.createElement('ns:DateRange')
ns_date_range.setAttribute('Start', from_date)
ns_date_range.setAttribute('End', to_date)
ns_rate_plan_candidates = dom.createElement('ns:RatePlanCandidates')
for hotelcode in hotelcode_set:
ns_rate_plan_candidate = dom.createElement('ns:RatePlanCandidate')
ns_rate_plan_candidate.setAttribute('AvailRatesOnlyInd', 'false')
ns_hotel_refs = dom.createElement('ns:HotelRefs')
ns_hotel_ref = dom.createElement('ns:HotelRef')
ns_hotel_ref.setAttribute('HotelCode', hotelcode)
ns_hotel_refs.appendChild(ns_hotel_ref)
ns_rate_plan_candidate.appendChild(ns_hotel_refs)
ns_rate_plan_candidates.appendChild(ns_rate_plan_candidate)
ns_rate_plan.appendChild(ns_date_range)
ns_rate_plan.appendChild(ns_rate_plan_candidates)
ns_rate_plans.appendChild(ns_rate_plan)
ns_ota_hsr.appendChild(ns_rate_plans)
request_body.appendChild(ns_ota_hsr)
hotel_request.appendChild(request_body)
root.appendChild(header)
root.appendChild(hotel_request)
xml_str = dom.toxml().decode('utf-8')
room_list = []
try:
resp = client.service.Request(xml_str)
resp = resp.__str__()
_INFO('client.service.Request',
['hotelcode = ' + hotelcode + ', len = ' + str(len(resp))])
room_list = parsePrice(resp, city)
#print 'ok2'
_INFO('ctripHotel::parsePrice',
['Parsed ' + str(len(room_list)) + ' values'])
except Exception, e:
_ERROR('ctripHotel::getRatePlanByHotelCode',
['hotelcode = ' + str(hotelcode),
str(e)])
return room_list
return all_price
_INFO('ctripHotel::parseRoom',
['Parse price failed because of no hotel found'])
except Exception, e:
_ERROR('ctripHotel::parseRoom',
['Parse price failed because of no hotel found',
str(e)])
for each_hotel_content in each_hotel_content_list:
room = Room()
try:
room.source_hotelid = pattern_search(hotelcode_pat,
each_hotel_content)
if room.source_hotelid == 'NULL':
_INFO('ctripHotel::parseRoom', ['Cannot parse this hotel id'])
except Exception, e:
_ERROR(
'ctripHotel::parseRoom',
['Cannot parse this hotel', str(e)])
#print room.source_hotelid
try:
each_room_content_list = rateplan_pat.findall(each_hotel_content)
if len(each_room_content_list) == 0:
_ERROR('ctripHotel::parseRoom', ['Parse this room failed'])
return room_list
except Exception, e:
_ERROR('ctripHotel::parseRoom', ['Parse this room failed', e])
return room_list
for each_room_content in each_room_content_list:
sid2uid[key] = uid
uid2status = {}
for uid in uid2status_set:
cand_set = uid2status_set[uid]
if 4 in cand_set:
uid2status[uid] = 4
elif 2 in cand_set:
uid2status[uid] = 2
elif 3 in cand_set:
uid2status[uid] = 3
else:
uid2status[uid] = 0
_INFO('Load merged data',['merged data count = ' + str(num)])
return uid2sid,uid2cid,sid2uid,uid2status,uid2star
#def load_unmerged_data():
# conn = MySQLdb.connect(host='127.0.0.1', user='******', charset='utf8',passwd='',db='merge_hotel_20150706')
# cursor = conn.cursor()
#
#
# uid2sid,uid2cid,sid2uid,uid2status = load_merged_data('127.0.0.1','merge_hotel_20150706','hotel_unid')
# drop_sid = get_confilict_data('127.0.0.1','merge_hotel_20150706','hotelinfo_expedia')
#
# sql = "select source,source_id from hotelinfo_expedia;"
#
# cursor.execute(sql)
#print 'sql: ' + sql
_INFO('push train data',['get train data from database...'])
try:
n = cursor.execute(sql)
datas = cursor.fetchall()
except Exception, e:
_INFO('push train data',['Error','execute sql error!'])
sys.exit(1)
cursor.close()
conn.close()
_INFO('push train data',['get train data from sql database done!'])
_INFO('push train data',['get available data...'])
train_data = {}
train_key_set = set()
for data in datas:
#print data
dept_id = data[4].encode('utf-8').strip()
dest_id = data[6].encode('utf-8').strip()
dept_day = data[7].encode('utf-8').strip().replace('-','')
source = data[16].encode('utf-8').strip()
update_time = str(data[22]).strip()
train_key = 'train_' + dept_id + '_' + dest_id + '_' + dept_day + '_' + source
rds = redis.Redis(host='127.0.0.1', port=6379, db=0)
conn = MySQLdb.connect(host='127.0.0.1', user='', charset='utf8',passwd='', db=source_db_name)
cursor = conn.cursor()
except Exception, e:
_INFO('push flight data',['redis or mysql connection error'])
_INFO('push flight data',['error code = ' + str(e)])
return None,None
sql = "select flight_no,plane_type,flight_corp,dept_id,dest_id,dept_day,dept_time,dest_time,dur,rest,price,tax,surcharge,currency,seat_type,real_class,stop_id,stop_time,daydiff,source,return_rule,stop,updatetime from " + source_sql_table_name + " ;"
#print 'sql: ' + sql
_INFO('push flight data',['get flight data from database...'])
try:
n = cursor.execute(sql)
datas = cursor.fetchall()
except Exception, e:
_INFO('push flight data',['Error','execute sql error!','sql = ' + str(sql)])
sys.exit(1)
cursor.close()
conn.close()
_INFO('push flight data',['get flight data from sql database done!'])
_INFO('push flight data',['get available data...'])
