def getCitiesOverview():
# Database query
conn = create_connection('./database/wanderweg.db')
cur = conn.cursor()
sql = 'SELECT id,name,country,population,latitude,longitude,hostelworld_pic,trainline_id FROM cities'
cur.execute(sql)
data = cur.fetchall()
conn.close()
# Format response
cities = []
for entry in data:
if entry[-1] != '0' and entry[-1] != 'ID not found' and entry[
1] not in exclude_ids:
city = {
'name': entry[1],
'city_id': entry[0],
'country': entry[2],
'population': entry[3],
'location': {
'lat': entry[4],
'lng': entry[5]
},
'alt_cover': entry[6]
}
cities.append(city)
response = flask.jsonify(cities)
return response
def user_signup(user, settings=settings):
"""Create a new user in the users table
:param user: dict
:return: user id
"""
token = secrets.token_urlsafe(16)
sql = ''' INSERT INTO users(name,email,password,is_active,login_attempts,last_login_attempt,token)
VALUES(?,?,?,?,?,?,?) '''
try:
conn = create_connection(settings.DATABASE)
cur = conn.cursor()
cur.execute(sql, (
user['name'],
user['email'],
hash_password(user['password']),
0,
0,
None,
token
))
conn.commit()
cur.close()
except Error as e:
log(e)
result = None
else:
result = user_find_by_email(user['email'])
finally:
if (conn):
conn.close()
return result
def main(settings=settings):
"""
Creates sqlite database and users table
"""
database = settings.DATABASE
sql_create_users_table = """CREATE TABLE IF NOT EXISTS users (
id integer PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE,
password text NOT NULL,
is_active integer NOT NULL,
login_attempts integer NOT NULL,
last_login_attempt timestamp,
token text NOT NULL
);"""
# create a database connection
conn = create_connection(database)
if conn is not None:
try:
# create users table
create_table(conn, sql_create_users_table)
except Error as e:
print(e)
finally:
conn.close()
else:
print('Error! could not create database connection')
exit(0)
def getLocalExonyms():
#Get city name and country
conn = create_connection('../database/wanderweg.db')
cur = conn.cursor()
city_sql = 'SELECT name, country FROM cities'
cur.execute(city_sql)
city_data = cur.fetchall()
city_country = {entry[0]:entry[1] for entry in city_data}
exo_sql = 'SELECT * FROM exonyms'
cur.execute(exo_sql)
col_names = [country[0] for country in cur.description[1:]]
country_index = {country:index+1 for index, country in enumerate(col_names)}
exonyms = cur.fetchall()
conn.close()
#Map cities to their local exonym
exonym_dict = {}
for row in exonyms:
english_index = country_index['england']
if row[english_index] in city_country.keys():
cur_city = row[english_index]
#Get the country for the current city
country = city_country[cur_city]
#Get the local city name for the current city
if country.lower() in country_index:
local_name = row[country_index[country.lower()]]
else:
local_name = cur_city
exonym_dict[cur_city] = local_name
return exonym_dict
def create_cities_table(database):
# create a database connection
conn = create_connection(database)
#Create table for city features
sql_create_cities_table = """ CREATE TABLE IF NOT EXISTS cities (
id integer PRIMARY KEY,
name text NOT NULL,
country text NOT NULL,
hostel_url text,
hostelworld_pic text,
population integer,
latitude real,
longitude real,
weather text,
trainline_id text
); """
if conn is not None:
# create cities table
create_table(conn, sql_create_cities_table)
else:
print("Error! cannot create the database connection.")
return conn
def user_activate(user_id, token, settings=settings):
"""Activate a user with a passed user id and token
:param user_id:
:param token:
:return: user id
"""
sql = '''
UPDATE users SET is_active = 1
WHERE token = ? AND id = ?
'''
try:
conn = create_connection(settings.DATABASE)
cur = conn.cursor()
cur.execute(sql, (token, user_id))
conn.commit()
cur.close()
except Error as e:
log(e)
result = False
else:
result = True
finally:
if (conn):
conn.close()
return result
def result():
"""
sends results
:return: json object with results
"""
data = request.args
user_id = data["user_id"]
query = "SELECT words, topic from user_content WHERE user_id = {}".format(
user_id)
conn = create_connection()
(queryResults) = run_query(conn, query)
print(queryResults)
words = []
topics = []
for i in queryResults:
words.append(i["words"])
topics.append(i["topic"])
word_freq = get_word_percent(queryResults)
freq = []
conn.commit()
conn.close()
return jsonify({
"code": 1,
"message": "Stats fetched successfully",
"words_stats": word_freq,
"alldata": queryResults,
"content_type": "application/json"
})
def result():
"""
sends results
:return: json object with results
"""
data = request.args
user_id, session_id, content_id = data["user_id"], data[
"session_id"], data["content_id"]
query = "SELECT words from user_content WHERE content_id = {}".format(
content_id)
conn = create_connection()
words = run_query(conn, query)
query = "SELECT * from content WHERE content_id = {}".format(content_id)
content = run_query(conn, query)[0]
word_freq = get_word_percent(words)
conn.commit()
conn.close()
return jsonify({
"code": 1,
"message": "Stats fetched successfully",
"content_id": content_id,
"words_stats": word_freq,
"content_type": content["content_type"],
"content_image_url": content["content_url"]
})
def get_email(user_id):
sql = "select * from users where user_id=" + str(user_id)
conn = create_connection()
results = run_query(conn=conn, query=sql)
try:
return results[0]["email"]
except Exception as e:
print(e)
return None
def saveDBToText():
conn = create_connection('../database/wanderweg.db')
cur = conn.cursor()
sql = 'SELECT id, name, trainline_id FROM cities'
cur.execute(sql)
data = cur.fetchall()
string = json.dumps(data)
f = open('../database/trainline_ids.txt', 'w+')
f.write(string)
f.close()
def vt_lookup():
conn = create_connection("./maldns.db")
if conn == None:
logging.error("No database connection, exiting.")
sys.exit(1)
while True:
scan_expired_or_unscanned_domains(conn)
logging.info("Done scanning")
def insert_user(data):
"""
insert new user
:param data: user data from front-end
"""
token = secrets.token_urlsafe(20)
query = "INSERT into users(name,email,image_url,api_token) VALUES(%s,%s,%s,%s)"
conn = create_connection()
run_query(conn, query, [data["name"], data["email"], data["image"], token])
conn.close()
def update_user_image(data):
"""
update the user image
:param data: data recieved from the client side
"""
query = "UPDATE users SET image_url='%s' where email='%s'" % (
data["image"], data["email"])
conn = create_connection()
run_query(conn, query, [])
conn.close()
def update_user_token(data):
"""
update token for already registered users
"""
token = secrets.token_urlsafe(20)
query = "UPDATE users SET api_token='%s' where email='%s'" % (
token, data["email"])
conn = create_connection()
run_query(conn, query, [])
conn.close()
def fetchID(sql_id):
conn = create_connection('./database/wanderweg.db')
cur = conn.cursor()
sql = "SELECT trainline_id FROM cities WHERE id = '" + sql_id + "'"
cur.execute(sql)
data = cur.fetchone()
trainline_id = data[0]
if trainline_id == '0' or trainline_id == 'ID not found':
trainline_id = None
return trainline_id
def addIDsToDB(trainline_ids):
db_vals = []
for key, val in trainline_ids.items():
if val == 'One city in pair could not be found': continue
else: db_vals.append((val, key))
conn = create_connection('../database/wanderweg.db')
cur = conn.cursor()
sql = 'UPDATE cities SET trainline_id = ? WHERE name = ?'
cur.executemany(sql, db_vals)
conn.commit()
conn.close()
async def load_data():
db_path = "/data/db_config/koster_lab-nm.db"
movie_dir = "/uploads"
conn = db_utils.create_connection(db_path)
df = pd.read_sql_query(
"SELECT b.filename, b.frame_number, a.species_id, a.x_position, a.y_position, a.width, a.height FROM agg_annotations_frame AS a LEFT JOIN subjects AS b ON a.subject_id=b.id",
conn,
)
df["movie_path"] = (movie_dir + "/" + df["filename"].apply(
lambda x: os.path.basename(x.rsplit("_frame_")[0]) + ".mov"))
return {"data": df.to_dict()}
def loadDBFromText():
with open('../database/trainline_ids.txt', 'r') as f:
content = f.read()
data = json.loads(content)
tuple_data = []
for entry in data:
city_id, city_name, trainline_id = entry
if len(trainline_id) > 20:
tuple_data.append((trainline_id, city_id, city_name))
conn = create_connection('../database/wanderweg.db')
cur = conn.cursor()
sql = 'UPDATE cities SET trainline_id = ? WHERE id = ? AND name = ?'
cur.executemany(sql, tuple_data)
conn.commit()
conn.close()
def topics():
"""
returns topics
:return: json object of topic data
"""
data = request.args
offset = int(data["offset"])
if "limit" in data:
limit = data["limit"]
else:
limit = 10
query = "select * from topics"
conn = create_connection()
results = run_query(conn, query)
conn.close()
return jsonify(get_topics_data(results, offset, limit))
def login():
"""
handles login
:return: json object of login data
"""
name, email, image = request.form["name"], request.form[
"email"], request.form["image"]
data = {"name": name, "email": email, "image": image}
query = "select * from users where email='{}'".format(email)
conn = create_connection()
results = run_query(conn, query)
if len(results) == 0 or results is None:
insert_user(data)
else:
update_user_token(data)
update_user_image(data)
return jsonify(get_user_data(email))
def submit():
"""
handles user submits
:return: json object of submit confirmation
"""
conn = create_connection()
data = request.json
user_id, words_input, datetime, topic = data["user_id"], data[
"words_input"], data["datetime"], data["topic"]
email = get_email(user_id)
for i, word in enumerate(words_input):
print("topic is : ", topic[i])
query = "INSERT into user_content(words, user_id, time,email, topic) VALUES(%s,%s,%s,%s,%s)"
run_query(conn, query, [word, user_id, datetime[i], email, topic[i]])
conn.commit()
conn.close()
return jsonify({"code": 1, "message": "user response saved"})
def dns_capture():
conn = create_connection("./maldns.db")
if conn == None:
sys.exit(1)
create_table(conn)
if config.interface not in netifaces.interfaces():
logging.error("Bad interface. Check config.py")
sys.exit(1)
cap = pyshark.LiveCapture(interface=config.interface,
bpf_filter='udp port 53')
for pkt in cap.sniff_continuously():
store_dns_info(pkt, conn)
conn.close()
def summary():
"""
sends result summary
:return: json object with results
"""
data = request.args
user_id, session_id = data["user_id"], data["session_id"]
query = "SELECT user_content.content_id, words, content_url, content_type FROM user_content " \
"INNER JOIN content ON content.content_id = user_content.content_id WHERE user_content.session_id = '" + session_id + "' GROUP BY content_id"
conn = create_connection()
data = run_query(conn, query)
conn.commit()
conn.close()
data_json = {
"code": 1,
"message": "Results fetched successfully",
"session_id": session_id,
"data": data
}
return jsonify(data_json)
def submit():
"""
handles user submits
:return: json object of submit confirmation
"""
conn = create_connection()
data = request.form
user_id, session_id, words_input, content_id, click_type = data["user_id"], data["session_id"], data["words_input"], \
data["content_id"], data["click_type"]
email = get_email(user_id)
words = ast.literal_eval(str(words_input))
for word in words:
query = "INSERT into user_content(words,time,session_id,content_id,email) VALUES(%s,%s,%s,%s,%s)"
run_query(
conn, query,
[word[0],
get_datetime(word[1]), session_id, content_id, email])
conn.commit()
conn.close()
return jsonify({"code": 1, "message": "user response saved"})
def topics():
"""
returns topics
:return: json object of topic data
"""
data = request.args
offset = int(data["offset"])
if "limit" in data:
limit = data["limit"]
else:
limit = 10
query = "select * from topics"
conn = create_connection()
results = run_query(conn, query)
newresult = []
for i in results:
newresult.append(i["topic_name"])
print(newresult)
conn.close()
return jsonify(get_topics_data(newresult, offset, limit))
def create_exonyms_table(database):
# create a database connection
conn = create_connection(database)
#Create table for city features
sql_create_exonyms_table = """ CREATE TABLE IF NOT EXISTS exonyms (
id integer PRIMARY KEY,
england text NOT NULL,
germany text,
france text,
italy text,
spain text,
portugal text,
brazil text,
sweden text,
poland text,
finland text,
denmark text,
netherlands text,
norwegian text,
czech_republic text,
russia text,
turkey text,
japan text,
korea text,
china text
); """
if conn is not None:
# create exonyms table
create_table(conn, sql_create_exonyms_table)
else:
print("Error! cannot create the database connection.")
return conn
def user_find_by_email(email, settings=settings):
"""Get user data by email
:param: email: string
:return: results
"""
find_user_sql = '''SELECT * FROM users
WHERE email = ?
'''
try:
conn = create_connection(settings.DATABASE)
cur = conn.cursor()
cur.execute(find_user_sql, [email])
user = cur.fetchone()
cur.close()
except Error as e:
log(e)
result = None
else:
result = user
finally:
if (conn):
conn.close()
return result
def get_user_data(email):
"""
Get user data
:param email: email of the user
:return: formatted response for login function
"""
query = "SELECT * from users where email='{}'".format(email)
# print(query)
conn = create_connection()
result = run_query(conn, query)[0]
# print(result)
conn.close()
data = {
"code": 1,
"message": "logged In successfully",
"data": {
"user_id": result["user_id"],
"name": result["name"],
"email": result["email"],
"image": result["image_url"],
"api_token": result["api_token"]
}
}
return data
def getCityInfo(cid):
# Database query
conn = create_connection('./database/wanderweg.db')
cur = conn.cursor()
sql = 'SELECT name,country,hostel_url,population,weather,hostelworld_pic,latitude,longitude FROM cities WHERE id=' + cid
cur.execute(sql)
data = cur.fetchone()
conn.close()
if not data:
info = {'error': 'no country with that ID found'}
response = flask.jsonify(info)
return response
# Fetch activities
activityScraper = GatherActivities()
#Use latitude and longitude to refine activity gathering
activities = activityScraper.scrapeCity(data[0], (data[-2], data[-1]))
# Fetch hostel info
hostelData = gatherHostelData(data[2])
# Format response
info = {
'city_id': int(cid),
'population': data[3],
'name': data[0],
'country': data[1],
'activities': activities,
'hostels': hostelData,
'weather': data[4],
'alt_cover': data[5]
}
response = flask.jsonify(info)
return response
def user_login(email, password, settings=settings):
"""Login a user with email and password
:param: email: string
:param: password: string
:return: user:
"""
currtime = datetime.now()
user = user_find_by_email(email)
if not user:
return None
try:
conn = create_connection(settings.DATABASE)
cur = conn.cursor()
if not verify_password(user[3], password):
# password does not match!
# incr login_attempts and update last_login_attempt for email
update_user_sql = '''
UPDATE users
SET login_attempts = login_attempts + 1,
last_login_attempt = ?
WHERE email = ?
'''
cur.execute(update_user_sql, [
currtime,
email
])
conn.commit()
cur.close()
return None
else:
# Return None if user is not activated
if (user[4] != 1):
return None
# Calculate time since last login
minutes = 0
if user[6]:
difference = datetime.now() - user[6]
minutes = (difference.seconds // 60) % 60
# Return None if failed attempts > 3 and last_login_attempt < 1
# hour ago
print(user[5], minutes)
if ((user[5] >= 3) and (minutes < 60)):
return None
# reset login attempts and last_login_attempt
print('reset login_attempts')
update_user_sql = '''
UPDATE users
SET login_attempts = 0,
last_login_attempt = ?
WHERE email = ?
'''
cur.execute(update_user_sql, [
currtime,
email
])
conn.commit()
cur.close()
user = user_find_by_email(email)
except Error as e:
log(e)
result = None
else:
result = user
finally:
if (conn):
conn.close()
return user
