示例#1
0
def update_location_actions():
    from re import escape

    location = Location()
    db = Database()
    t = BingTranslate()
    cmd = "SELECT * FROM location"
    location_res = db.query_db(cmd)
    for item in location_res:
        location_id, location_name, location_chinese = item
        for action in location.get_action_for_venue(location_chinese):
            cmd = u"SELECT * FROM action WHERE chinese_name = '%s'" % (action)
            action_res = db.query_db(cmd)
            if len(action_res) > 0:
                action_id = action_res[0][0]
                is_bad = action_res[0][3]
            else:
                action_en = t.translate(action, "zh-CHT", "en")
                cmd = (
                    u"INSERT INTO action (chinese_name, english_name) \
						VALUES ('%s', '%s')"
                    % (escape(action), escape(action_en))
                )
                db.query_db(cmd)
                cmd = u"SELECT * FROM action WHERE chinese_name = '%s'" % (action)
                action_res = db.query_db(cmd)
                action_id = action_res[0][0]
                is_bad = action_res[0][3]
            if is_bad != 1:
                cmd = (
                    "INSERT INTO location_action (location_id, action_id) \
						VALUES (%s, %s)"
                    % (location_id, action_id)
                )
                db.query_db(cmd)
示例#2
0
def update_action_concepts():
    """
	Get related concepts of an action and store it in database.
	"""
    location = Location()
    db = Database()
    cmd = "SELECT * FROM action"
    action_res = db.query_db(cmd)
    for item in action_res:
        action_id, action_chinese, action_english, is_bad = item
        if is_bad == 1:
            continue
        for concept in location.get_concept_for_venue(action_chinese):
            cmd = u"SELECT * FROM concept WHERE name = '%s'" % (concept)
            concept_res = db.query_db(cmd)
            if len(concept_res) > 0:
                concept_id = concept_res[0][0]
                is_bad = concept_res[0][8]
            else:
                continue
            if is_bad != 1:
                cmd = (
                    "INSERT INTO action_concept (action_id, concept_id) \
						VALUES (%s, %s)"
                    % (action_id, concept_id)
                )
                db.query_db(cmd)
示例#3
0
def get_location(geo):
	geo = geo.split(',')
	geo_lat = geo[0]
	geo_long = geo[1]
	location = Location()
	json = {}
	venues = location.get_venues(geo_lat, geo_long)
	output = []
	cmd = "SELECT DISTINCT a.location_id, b.name FROM \
			dialog_location_category AS a, location AS b \
			WHERE a.location_id = b.id"
	location_res = db.query_db(cmd)
	valid_location = map(lambda x: x[1], location_res)
	for chinese, display in venues:
		cmd = "SELECT a.concept_id FROM location_concept AS a, \
				location AS b WHERE a.location_id = b.id AND \
				b.chinese_name = '%s'" % (chinese)
		concept_res = db.query_db(cmd)
		if (display in valid_location) and (len(concept_res) > 8):
			output.append({'display':display, 'chinese':chinese})
	cmd = "SELECT * FROM location"
	location_res = db.query_db(cmd)
	for venue in location_res:
		json_venue = {'display':venue[1], 'chinese':venue[2]}
		if json_venue['display'] not in map(lambda x: x['display'], output):
			cmd = "SELECT a.concept_id FROM location_concept AS a, \
					location AS b WHERE a.location_id = b.id AND \
					b.chinese_name = '%s'" % (venue[2])
			concept_res = db.query_db(cmd)
			if (venue[1] in valid_location) and (len(concept_res) > 8):
				output.append(json_venue)
	json['locations'] = output
	return flask.jsonify(json)