def create_location_image(location_name):
cur.execute(
"SELECT * FROM Photo WHERE LocationName='{}'".format(location_name))
photo = cur.fetchone()
if photo:
return
key = 'AIzaSyCoIJcakxVen5qGdu_PsV_ajdl33qwGskI'
rv = requests.get(
'https://maps.googleapis.com/maps/api/place/autocomplete/json?key={}&input={}&type=geocode&'
.format(key, location_name))
data = rv.json()
if data['status'] != 'OK':
return
location = data['predictions'][0]
place_id = location['place_id']
rv = requests.get(
'https://maps.googleapis.com/maps/api/place/details/json?key={}&placeid={}'
.format(key, place_id))
data = rv.json()['result']
if 'photos' not in data:
url = None
else:
photo_ref = data['photos'][0]['photo_reference']
url = 'https://maps.googleapis.com/maps/api/place/photo?maxwidth=400&photoreference={}&key={}'.format(
photo_ref, key)
cur.execute("INSERT INTO Photo VALUES ('{}', '{}')".format(
url, location_name))
conn.commit()
def create_trip(keyword, location_name, user, lpnum):
'''
Create trip from location's go next
'''
go_nexts = get_location_go_nexts(location_name)
if not go_nexts:
raise ValueError('Location {} does not exist'.format(location_name))
create_trip_location(keyword, location_name, lpnum)
create_location_image(location_name)
# Get info and create location for all go nexts
num_go_nexts = min(len(go_nexts), 5)
for location in go_nexts[:num_go_nexts]:
name = location[0]
coords = location[1]
if name:
create_trip_location(keyword, name, lpnum)
create_location_image(name)
# Create trip
cur.execute('''
INSERT INTO Trip
VALUES ('{}', '{}', {})
'''.format(keyword, location_name, lpnum))
conn.commit()
def recommend_trip(username):
query = '''
SELECT u.TripKeyword, MAX(u.LPNum)
FROM TripUser u
WHERE u.Assessment = 1 AND u.Username = '******'
GROUP BY u.TripKeyword
'''.format(username)
cur.execute(query)
rv = cur.fetchall()
recommended = [[], []]
if not rv:
return []
else:
for trip_info in rv:
keyword = trip_info[0]
lpnum = trip_info[1]
recommended[0].append(
"http://fa16-cs411-47.cs.illinois.edu/{}/search/{}/{}".format(
username, keyword, lpnum + 1))
recommended[1].append(keyword + " {}".format(lpnum + 1))
return recommended
def delete_past_trip(username, keyword, lpnum):
cur.execute('''
DELETE FROM TripUser
WHERE Username='******' AND TripKeyword='{}' AND LPNum={}
'''.format(username, keyword, lpnum))
conn.commit()
profile_url = "{}/{}".format(url, username)
return redirect(profile_url)
def example_query():
cur.execute(
"SELECT page_title FROM page WHERE page_title LIKE '%Republic%'")
query_name = "SELECT page_title FROM page WHERE page_title LIKE '%Republic%'"
query = str(cur.fetchall())
return render_template('example_query.html',
query_name=query_name,
query=query)
def create_user(username, password, name):
user = get_user_by_username(username)
if user:
return False
else:
cur.execute("INSERT INTO User VALUES ('{}', '{}', '{}', {})".format(
username, password, name, True))
conn.commit()
return True
def get_location_go_nexts(location_name):
# Get all associated go nexts with location
db_location_name = location_name.replace(" ", "_")
cur.execute('''
SELECT NextName, NextCoords
FROM LocationGoNext
WHERE SourceName LIKE '{}%'
'''.format(db_location_name))
rv = cur.fetchall()
return rv
def get_trip_locations(keyword, lpnum):
cur.execute('''
SELECT LocationName
FROM TripLocation
WHERE TripKeyword='{}' AND LPNum={}
'''.format(keyword, lpnum))
trip_locations = cur.fetchall()
locations = [get_location_by_name(loc[0]) for loc in trip_locations]
return locations
def like_trip(username, keyword, lpnum, like):
like = like == 'True'
cur.execute('''
UPDATE TripUser
SET Assessment={}
WHERE TripKeyword='{}' AND Username='******' AND LPNum={}
'''.format(like, keyword, username, lpnum))
conn.commit()
return redirect("{}/{}/search/{}/{}".format(url, username, keyword, lpnum))
def get_location_by_name(name):
cur.execute('''
SELECT l.Description, l.Eat, l.See, l.Do, l.Name, p.URL
FROM Location l, Photo p
WHERE l.Name='{}' AND p.LocationName = '{}'
'''.format(name, name))
location = cur.fetchone()
if location:
return location_to_dict(location)
return None
def past_trips(username):
cur.execute('''
SELECT TripKeyword, LPNum
FROM TripUser
WHERE Username = '******'
'''.format(username))
rv = cur.fetchall()
past = [[], []]
if not rv:
return []
else:
for trip_info in rv:
keyword = trip_info[0]
lpnum = trip_info[1]
past[0].append(
"http://fa16-cs411-47.cs.illinois.edu/{}/search/{}/{}".format(
username, keyword, lpnum))
past[1].append(keyword + " {}".format(lpnum))
return past
def create_trip_user(keyword, username, lpnum):
trip_user = get_trip_user(keyword, username, lpnum)
if not trip_user:
cur.execute("INSERT INTO TripUser VALUES ('{}','{}', {}, 0)".format(
keyword, username, lpnum))
conn.commit()
def get_trip_by_keyword(keyword, lpnum):
cur.execute("SELECT * FROM Trip WHERE Keyword='{}' AND LPNum='{}'".format(
keyword, lpnum))
trip = cur.fetchone()
return trip
def get_location_by_coords(coords):
cur.execute("SELECT * FROM Location WHERE Coordinates='{}'".format(coords))
location = cur.fetchone()
return location
def create_trip_location(keyword, location_name, lpnum):
cur.execute("INSERT INTO TripLocation VALUES ('{}','{}', {})".format(
keyword, location_name, lpnum))
conn.commit()
def change_user_logged_in(username, logged_in):
cur.execute("UPDATE User SET LoggedIn={} WHERE Username='******'".format(
logged_in, username))
conn.commit()
def get_user_by_username(username):
cur.execute("SELECT * FROM `User` WHERE Username='******'".format(username))
return cur.fetchone()
from __init__ import cur, conn
cur.execute("DELETE FROM Photo")
cur.execute("DELETE FROM Trip")
cur.execute("DELETE FROM TripUser")
cur.execute("DELETE FROM TripLocation")
conn.commit()
def get_trip_user(keyword, username, lpnum):
cur.execute(
"SELECT * FROM TripUser WHERE TripKeyword='{}' AND Username='******' AND LPNum={}"
.format(keyword, username, lpnum))
trip_user = cur.fetchone()
return trip_user