def updateFlight(flight_num, dept_time):
#get airline name
airline_name = session['airline_name']
if request.method == "POST":
#fetch selection
new_status = request.form.get('statusSelect')
print("new-status", new_status)
#update database
cursor = conn.cursor()
query = "UPDATE flight SET flight_status = %s WHERE (airline_name, flight_num, dept_time) = (%s, %s, %s)"
cursor.execute(query,
(new_status, airline_name, flight_num, dept_time))
cursor.close()
conn.commit()
message = "Update Flights Success"
session["flight_updated"] = True
return redirect("/flightManage")
else:
cursor = conn.cursor()
#check if such flight exits
query = "SELECT * FROM flight WHERE airline_name = %s AND flight_num = %s AND dept_time = %s"
cursor.execute(query, (airline_name, flight_num, dept_time))
data = cursor.fetchone()
cursor.close()
if data:
print(data)
return render_template("updateFlight.html", flight=data)
else:
noFound = "There's an issue in updating the flight: such flight does not exist"
return render_template('flightManage.html', noFound=noFound)
def viewMyFlights():
if session['role'] == 'customer':
email = session['email']
username = session['username']
cursor = conn.cursor()
current_date = datetime.datetime.now()
query = "select * from ticket natural join flight natural join airport as A, airport as B where cust_email = %s and dept_time > %s and dept_from = A.name and arr_at = B.name"
cursor.execute(query, (email, current_date))
data1 = cursor.fetchall()
conn.commit()
cursor.close()
return render_template('viewMyFlights.html',
flights=data1,
role='customer')
elif session['role'] == 'agent':
email = session['email']
cursor = conn.cursor()
current_date = datetime.datetime.now()
query = "select * from ticket natural join flight natural join airport as A, airport as B where agent_email = %s and dept_time > %s and dept_from = A.name and arr_at = B.name"
cursor.execute(query, (email, current_date))
data1 = cursor.fetchall()
conn.commit()
cursor.close()
return render_template('viewMyFlights.html',
flights=data1,
role='agent')
def viewFlight():
#get airline name
airline_name = session['airline_name']
default = ""
if request.method == "POST":
#grabs information from the forms
dept_from = request.form['dept_from']
arr_at = request.form['arr_at']
start_date = request.form['start_date']
end_date = request.form['end_date']
if datetime.strptime(start_date, "%Y-%m-%d") > datetime.strptime(
end_date, "%Y-%m-%d"):
return render_template("flightManage.html",
error="The dates you entered are invalid.")
#database query
cursor = conn.cursor()
query = "SELECT * FROM flight NATURAL JOIN airplane, airport as A, airport as B \
where airline_name = %s AND date(dept_time) >= %s AND date(dept_time) <= %s \
AND flight.dept_from = A.name and flight.arr_at = B.name and (A.name = %s or A.city = %s) \
and (B.name = %s or B.city = %s)"
cursor.execute(query, (airline_name, start_date, end_date, dept_from,
dept_from, arr_at, arr_at))
data1 = cursor.fetchall()
cursor.close()
msg = (dept_from, arr_at, start_date, end_date)
else:
# default views
cursor = conn.cursor()
query = 'SELECT * FROM flight WHERE airline_name = %s AND DATE(dept_time) BETWEEN DATE(CURRENT_TIMESTAMP) \
AND DATE(CURRENT_TIMESTAMP) + INTERVAL 30 DAY'
cursor.execute(query, (airline_name))
data1 = cursor.fetchall()
cursor.close()
default = "Default: Future 30 Days"
msg = "Default: Future 30 Days"
# send to the html
if data1:
for each in data1:
print("Received Data:/n", each['airline_name'], each['flight_num'],
each['dept_time'])
return render_template('flightManage.html', flights=data1, msg=msg)
else:
#returns an error message to the html page
noFound = "No flights available within the given conditions"
return render_template('flightManage.html',
default=default,
noFound=noFound)
def managePlane():
#get airline name
airline_name = session['airline_name']
if request.method == "POST":
#fetch data
airplane_id = request.form["airplane_id"]
seats = request.form["seats"]
#check duplicates
cursor = conn.cursor()
query = "SELECT * FROM airplane WHERE (airline_name, airplane_id) = (%s, %s)"
cursor.execute(query, (airline_name, airplane_id))
data = cursor.fetchall()
if data:
noFound = "Such airplane ID already exists"
return render_template("airSystemManage.html",
noFound=noFound,
message="airplane",
state_plane=True)
cursor.close()
#initiate query
cursor = conn.cursor()
query = "INSERT INTO airplane VALUES (%s, %s, %s)"
cursor.execute(query, (airline_name, airplane_id, seats))
cursor.close()
conn.commit()
session["airplane_updated"] = True
return redirect("/airSystemManage/airplane")
else:
# display all the planes operated by the airline
cursor = conn.cursor()
query = "SELECT * FROM airplane WHERE airline_name = %s"
cursor.execute(query, (airline_name))
data = cursor.fetchall()
cursor.close()
if data:
for each in data:
print("data:", each)
return render_template("airSystemManage.html",
airplane=data,
state_plane=True)
else:
noFound = "There is not airplane in the system"
return render_template("airSystemManage.html",
noFound=noFound,
state_plane=True)
def purchaseTickets():
airline_name = request.form['airline_name']
flight_num = request.form['flight_num']
dept_time = request.form['dept_time']
current_price = request.form['price']
airline_name2 = request.form['airline_name2']
flight_num2 = request.form['flight_num2']
dept_time2 = request.form['dept_time2']
current_price2 = request.form['price2']
total = float(current_price) + float(current_price2)
#open cursor
cursor = conn.cursor()
#excutes query for flight
query = "select * from flight natural join airplane, airport as A, airport as B where airline_name = %s and flight_num = %s and dept_time = %s and dept_from = A.name and arr_at = B.name"
cursor.execute(query, (airline_name, flight_num, dept_time))
#store the results
data = cursor.fetchone()
data['current_price'] = current_price
data2 = 0
if airline_name2 != '':
query = "select * from flight natural join airplane, airport as A, airport as B where airline_name = %s and flight_num = %s and dept_time = %s and dept_from = A.name and arr_at = B.name"
cursor.execute(query, (airline_name2, flight_num2, dept_time2))
data2 = cursor.fetchone()
data2['current_price'] = current_price2
conn.commit()
cursor.close()
print(airline_name)
return render_template("purchaseTickets.html",
flight=data,
return_flight=data2,
total=total,
role=session['role'])
def add_user(cls, username, password, email):
query = f"INSERT INTO users (username, password, email) VALUES ('{username}', '{password}', '{email}')"
cursor = conn.cursor()
cursor.execute(query)
conn.commit()
cursor.close()
return True
def checkRatings(flight_num, dept_time):
#get airline name
airline_name = session['airline_name']
#fetch data
cursor = conn.cursor()
query = "SELECT airline_name,flight_num, dept_time, AVG(rate) as avg_rate \
FROM rates \
WHERE (airline_name,flight_num, dept_time) = (%s, %s, %s)"
cursor.execute(query, (airline_name, flight_num, dept_time))
data1 = cursor.fetchone()
if data1["avg_rate"]:
avg_rate = "{0:.2f}".format(float(data1["avg_rate"]))
else:
noFound = "This Flight has no ratings yet"
return render_template("report.html", noFound=noFound)
query = "SELECT airline_name,flight_num, dept_time, cust_email, rate, comments \
FROM rates \
WHERE (airline_name,flight_num, dept_time) = (%s, %s, %s) "
cursor.execute(query, (airline_name, flight_num, dept_time))
data = cursor.fetchall()
cursor.close()
conn.commit()
if data:
for each in data:
print(each)
return render_template("report.html", avg_rate=avg_rate, ratings=data)
else:
noFound = "This Flight has no ratings yet"
return render_template("report.html", noFound=noFound)
def search_by_isbn(cls, isbn):
query = f"SELECT * FROM book WHERE isbn ILIKE '%{isbn}%'"
cursor = conn.cursor()
cursor.execute(query)
book = cursor.fetchone()
cursor.close()
return book
def search_by_id(cls, id):
query = f"SELECT title, author, year, id, isbn FROM book WHERE id = '{id}'"
cursor = conn.cursor()
cursor.execute(query)
book = cursor.fetchone()
cursor.close()
return book
def get_comments(cls, id):
query = f"SELECT content, published from review WHERE book_id = {id}"
cursor = conn.cursor()
cursor.execute(query)
comments = cursor.fetchall()
cursor.close()
return comments
def checkFlight():
#grabs information from the forms
airline_name = request.form['airline_name']
flight_num = request.form['flight_num']
dept_date = request.form['dept_date']
arr_date = request.form['arr_date']
#open cursor
cursor = conn.cursor()
#excutes query
if dept_date and arr_date:
query = "select * from flight where airline_name = %s and flight_num = %s and date(dept_time) = %s and date(arr_time) = %s"
cursor.execute(query, (airline_name, flight_num, dept_date, arr_date))
elif dept_date:
query = "select * from flight where airline_name = %s and flight_num = %s and date(dept_time) = %s"
cursor.execute(query, (airline_name, flight_num, dept_date))
elif arr_date:
query = "select * from flight where airline_name = %s and flight_num = %s and date(arr_time) = %s"
cursor.execute(query, (airline_name, flight_num, arr_date))
else:
pass
#store the results
data3 = cursor.fetchall()
cursor.close()
conn.commit()
error = None
if data3:
return render_template("check.html", status=data3)
else:
error = "The Flight You are Searching Is Empty"
return render_template("check.html", error=error)
def loginAuthStaff():
username = request.form['username']
password = request.form['password']
#cursor used to send queries
cursor = conn.cursor()
#executes query
query = 'SELECT * FROM airline_staff WHERE username = %s'
cursor.execute(query, (username))
#stores the results in a variable
data = cursor.fetchone()
#use fetchall() if you are expecting more than 1 data row
error = None
if(data):
if(sha256_crypt.verify(password, data['password'])):
#creates a session for the the user
#session is a built in
session['username'] = username
session['role'] = "staff"
session['first_name'] = data["first_name"]
session['last_name'] = data["last_name"]
session['airline_name'] = data["airline_name"]
return redirect(url_for('staff.staffHome'))
else:
#returns an error message to the html page
error = 'Invalid password'
return render_template('login.html', error=error)
else:
#returns an error message to the html page
error = 'Invalid username'
return render_template('login.html', error=error)
def loginAuthAgent():
email = request.form['email']
password = request.form['password']
#cursor used to send queries
cursor = conn.cursor()
#executes query
query = 'SELECT * FROM booking_agent WHERE email = %s'
cursor.execute(query, (email))
#stores the results in a variable
data = cursor.fetchone()
#use fetchall() if you are expecting more than 1 data row
cursor.close()
error = None
if(data):
if(sha256_crypt.verify(password, data['password'])):
#creates a session for the the user
#session is a built in
session['email'] = email
session['role'] = 'agent'
return redirect(url_for('agent.agentHome'))
else:
#returns an error message to the html page
error = 'Invalid password'
return render_template('login.html', error=error)
else:
#returns an error message to the html page
error = 'Invalid username'
return render_template('login.html', error=error)
def registerAuthAgent():
email = request.form['email']
password = sha256_crypt.encrypt(request.form['password'])
agent_id = request.form['id']
#cursor used to send queries
cursor = conn.cursor()
#executes query
query = 'SELECT * FROM booking_agent WHERE email = %s'
cursor.execute(query, (email))
#stores the results in a variable
data = cursor.fetchone()
query = 'SELECT * FROM booking_agent WHERE agent_id = %s'
cursor.execute(query, (agent_id))
data2 = cursor.fetchone()
#use fetchall() if you are expecting more than 1 data row
error = None
if(data):
#If the previous query returns data, then user exists
error = "This user already exists"
cursor.close()
return render_template('register.html', error = error)
elif (data2):
#If the previous query returns data, then user exists
error = "This agent id already exists"
cursor.close()
return render_template('register.html', error = error)
else:
ins = 'INSERT INTO booking_agent VALUES(%s, %s, %s, %s)'
cursor.execute(ins, (email, password, agent_id, 0))
conn.commit()
cursor.close()
return render_template('index.html')
def registerAuthCustomer():
#grabs information from the forms
email = request.form['email']
password = sha256_crypt.encrypt(request.form['password'])
name = request.form['name']
building_num = request.form['building_num']
street = request.form['street']
city = request.form['city']
state = request.form['state']
phone_num = request.form['phone_num']
passport_num = request.form['passport_num']
passport_expr = request.form['passport_expr']
passport_country = request.form['passport_country']
DOB = request.form['DOB']
#cursor used to send queries
cursor = conn.cursor()
#executes query
query = 'SELECT * FROM customer WHERE email = %s'
cursor.execute(query, (email))
#stores the results in a variable
data = cursor.fetchone()
#use fetchall() if you are expecting more than 1 data row
error = None
if(data):
#If the previous query returns data, then user exists
error = "This user already exists"
cursor.close()
return render_template('register.html', error = error)
else:
ins = 'INSERT INTO customer VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
cursor.execute(ins, (email, password, name, building_num, street, city, state, phone_num, passport_num, passport_expr, passport_country, DOB, 0))
conn.commit()
cursor.close()
return render_template('index.html')
def viewTopCustomer():
#get airline name
airline_name = session['airline_name']
#start to fetch the data
cursor = conn.cursor()
query = "SELECT cust_email, COUNT(*) AS travel_times FROM ticket WHERE airline_name = %s \
AND DATE(purchase_time) BETWEEN NOW() - INTERVAL 1 YEAR AND NOW() + INTERVAL 1 DAY"
cursor.execute(query, (airline_name))
data1 = cursor.fetchall()
max_times = 0
for each in data1:
if each["travel_times"] > max_times:
max_times = each["travel_times"]
query2 = "SELECT cust_email, COUNT(*) AS travel_times FROM ticket WHERE airline_name = %s HAVING travel_times = %s"
cursor.execute(query2, (airline_name, max_times))
data = cursor.fetchall()
cursor.close()
if data:
for each in data:
print(data)
return render_template("report.html", passenger=data)
else:
noFound = "There is an issue in displaying the information you want"
return render_template("report.html", noFound=noFound)
def manageAirport():
if request.method == "POST":
#fetch data
name = request.form["name"]
city = request.form["city"]
#check duplicates
cursor = conn.cursor()
query = "SELECT * FROM airport WHERE name = %s"
cursor.execute(query, (name))
data = cursor.fetchall()
if data:
noFound = "Such airport name already exists"
return render_template("airSystemManage.html",
noFound=noFound,
message="airport",
state_airport=True)
cursor.close()
#initiate query
cursor = conn.cursor()
query = "INSERT INTO airport VALUES (%s, %s)"
cursor.execute(query, (name, city))
cursor.close()
conn.commit()
session["airport_updated"] = True
return redirect("/airSystemManage/airport")
else:
# display all the planes operated by the airline
cursor = conn.cursor()
query = "SELECT * FROM airport"
cursor.execute(query)
data = cursor.fetchall()
cursor.close()
if data:
for each in data:
print("data:", each)
return render_template("airSystemManage.html",
airport=data,
state_airport=True)
else:
noFound = "There is no airport in the system"
return render_template("airSystemManage.html",
noFound=noFound,
state_airport=True)
def commission():
if request.method == "POST":
to_date = request.form['to_date']
from_date = request.form['from_date']
if datetime.datetime.strptime(from_date,
"%Y-%m-%d") > datetime.datetime.strptime(
to_date, "%Y-%m-%d"):
return render_template("commission.html",
error="The dates you entered are invalid.")
cursor = conn.cursor()
query = "SELECT IFNULL(SUM(sold_price) , 0) as total_price, IFNULL(COUNT(*) , 0) as ticket_num FROM ticket WHERE DATE(purchase_time) BETWEEN %s AND %s AND agent_email = %s"
cursor.execute(query, (from_date, to_date, session['email']))
data = cursor.fetchone()
conn.commit()
cursor.close()
total_price = "{0:.2f}".format(float(data['total_price']) * 0.1)
average_commission = "{0:.2f}".format(
float(data['total_price']) * 0.1 / float(data['ticket_num']))
return render_template("commission.html",
total_price=total_price,
average_commission=average_commission,
ticket_num=data['ticket_num'],
from_date=from_date,
to_date=to_date)
else:
cursor = conn.cursor()
query = "SELECT IFNULL(SUM(sold_price) , 0) as total_price, IFNULL(COUNT(*) ,0) as ticket_num FROM ticket WHERE DATE(purchase_time) BETWEEN NOW() - INTERVAL 30 DAY AND NOW() + INTERVAL 1 DAY AND agent_email = %s"
cursor.execute(query, (session['email']))
data = cursor.fetchone()
conn.commit()
cursor.close()
print("data:", data)
if data['total_price'] != 0 and data['ticket_num'] != 0:
total_price = "{0:.2f}".format(float(data['total_price']) * 0.1)
average_commission = "{0:.2f}".format(
float(data['total_price']) * 0.1 / float(data['ticket_num']))
ticket_num = data['ticket_num']
else:
total_price = 0
average_commission = 0
ticket_num = 0
return render_template("commission.html",
total_price=total_price,
average_commission=average_commission,
ticket_num=ticket_num)
def revenueCompare():
#get airline name
airline_name = session['airline_name']
cursor = conn.cursor()
#colors:
colors = ["#FDB45C", "#FEDCBA"]
if request.method == "POST":
default = ""
option = request.form.get("revSelect")
if option == "rev_past_month":
title = "Revenue comparison for the past month"
query_direct = "SELECT SUM(sold_price) as total_price FROM ticket \
WHERE agent_email IS NULL and DATE(purchase_time) BETWEEN DATE(NOW()) - INTERVAL 1 MONTH and DATE(NOW())"
query_indirect = "SELECT SUM(sold_price) as total_price FROM ticket \
WHERE agent_email IS NOT NULL and DATE(purchase_time) BETWEEN DATE(NOW()) - INTERVAL 1 MONTH and DATE(NOW())"
else:
title = "Revenue comparison for the past year"
query_direct = "SELECT SUM(sold_price) as total_price FROM ticket \
WHERE agent_email IS NULL and DATE(purchase_time) BETWEEN DATE(NOW()) - INTERVAL 1 YEAR and DATE(NOW())"
query_indirect = "SELECT SUM(sold_price) as total_price FROM ticket \
WHERE agent_email IS NOT NULL and DATE(purchase_time) BETWEEN DATE(NOW()) - INTERVAL 1 YEAR and DATE(NOW())"
else:
default = "Default:"
title = "Revenue comparison for the past month"
query_direct = "SELECT SUM(sold_price) as total_price FROM ticket \
WHERE agent_email IS NULL and DATE(purchase_time) BETWEEN DATE(NOW()) - INTERVAL 1 MONTH and DATE(NOW())"
query_indirect = "SELECT SUM(sold_price) as total_price FROM ticket \
WHERE agent_email IS NOT NULL and DATE(purchase_time) BETWEEN DATE(NOW()) - INTERVAL 1 MONTH and DATE(NOW())"
cursor.execute(query_direct)
direct_sales = cursor.fetchone()
cursor.execute(query_indirect)
indirect_sales = cursor.fetchone()
labels = ['direct_sales', 'indirect_sales']
values = [
float(direct_sales["total_price"]),
float(indirect_sales["total_price"])
]
print(values)
try:
mymax = max(values)
except:
mymax = 100000
return render_template("report.html", default = default, title = title, revenue = True, \
max = mymax, set = zip(values, labels, colors))
def purchaseDetails():
airline_name = request.form['airline_name']
flight_num = request.form['flight_num']
dept_time = request.form['dept_time']
current_price = request.form['price']
airline_name2 = request.form['airline_name2']
flight_num2 = request.form['flight_num2']
dept_time2 = request.form['dept_time2']
current_price2 = request.form['price2']
card_type = request.form['card_type']
card_num = request.form['card_num']
name_on_card = request.form['name_on_card']
expr_date = request.form['expr_date']
cursor = conn.cursor()
time = datetime.datetime.now().strftime("%Y/%m/%d %H:%M:%S")
if session['role'] == 'agent':
cust_email = request.form['cust_email']
query = "select count(*) from customer where email = %s"
cursor.execute(query, cust_email)
num = cursor.fetchone()
print(num)
if num['count(*)'] == 0:
return "Failure"
#excutes query for flight
query = "insert into ticket values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(query,
(str(datetime.datetime.now().timestamp()),
current_price, card_type, card_num, name_on_card,
expr_date, time, session['email'], cust_email,
airline_name, flight_num, dept_time))
#store the results
if len(airline_name2) > 0:
query = "insert into ticket values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(query,
(str(datetime.datetime.now().timestamp()),
current_price2, card_type, card_num, name_on_card,
expr_date, time, session['email'], cust_email,
airline_name2, flight_num2, dept_time2))
else:
#excutes query for flight
query = "insert into ticket values (%s, %s, %s, %s, %s, %s, %s, null, %s, %s, %s, %s)"
cursor.execute(
query, (str(datetime.datetime.now().timestamp()), current_price,
card_type, card_num, name_on_card, expr_date, time,
session['email'], airline_name, flight_num, dept_time))
#store the results
if len(airline_name2) > 0:
query = "insert into ticket values (%s, %s, %s, %s, %s, %s, %s, null, %s, %s, %s, %s)"
cursor.execute(
query,
(str(datetime.datetime.now().timestamp()), current_price2,
card_type, card_num, name_on_card, expr_date, time,
session['email'], airline_name2, flight_num2, dept_time2))
conn.commit()
cursor.close()
return "Success"
def giveComments(ticket_id):
if request.method == 'POST':
rate = request.form['rate']
comment = request.form['comment']
cursor = conn.cursor()
query = "select * from ticket where ticket_id = %s"
cursor.execute(query, (ticket_id))
ticket = cursor.fetchone()
query = "select * from rates where cust_email = %s and airline_name = %s and flight_num = %s and dept_time = %s"
cursor.execute(query, (session['email'], ticket['airline_name'],
ticket['flight_num'], ticket['dept_time']))
data = cursor.fetchone()
if data == None:
query = "insert into rates values (%s, %s, %s, %s, %s, %s)"
cursor.execute(
query,
(session['email'], ticket['airline_name'],
ticket['flight_num'], ticket['dept_time'], rate, comment))
else:
query = "update rates set rate = %s, comments = %s where cust_email = %s and airline_name = %s and flight_num = %s and dept_time = %s"
cursor.execute(
query,
(rate, comment, session['email'], ticket['airline_name'],
ticket['flight_num'], ticket['dept_time']))
conn.commit()
cursor.close()
return redirect('/comments')
else:
cursor = conn.cursor()
#excutes query for flight
query = "select * from ticket natural join flight natural join airport as A, airport as B where ticket_id = %s and dept_from = A.name and arr_at = B.name"
cursor.execute(query, (ticket_id))
#store the results
data = cursor.fetchone()
query = "select * from rates where cust_email = %s and airline_name = %s and flight_num = %s and dept_time = %s"
cursor.execute(query, (session['email'], data['airline_name'],
data['flight_num'], data['dept_time']))
#store the results
rates = cursor.fetchone()
conn.commit()
cursor.close()
return render_template('giveComments.html', flight=data, rates=rates)
def test_drop_duplicate(conn):
"""make sure no duplicate exists"""
d.drop_duplicate(conn)
# check if count song == count distinct song
cur = conn.cursor()
cur.execute('SELECT count(*) FROM music')
count1 = cur.fetchall()
cur.execute('SELECT count(distinct title) FROM music')
count2 = cur.fetchall()
assert count1 == count2, "no duplicate exists in music"
def test_drop_song(conn):
"""test if drop_song works as expected"""
filename = os.listdir("./music/mp3")[0]
title = filename[:-4]
d.drop_song(title, conn)
# check if the song exists in database
cur = conn.cursor()
cur.execute('SELECT count(*) FROM music where title = %s', (title, ))
rowcount = cur.fetchall()
assert rowcount[0][0] == 0, "drop_song works"
def test_update_fingerprinted(conn):
"""test update_fingerprinted status when done"""
# use the newly-added fingerprints in previous test
filename = os.listdir("./music/wav")[0]
song_id = d.select_songid(filename, conn)
cur = conn.cursor()
cur.execute('SELECT fingerprinted FROM music where song_id = %s',
(song_id, ))
status = cur.fetchall()
assert status[0][0] == 1, "update_fingerprinted works"
def comments():
#open cursor
cursor = conn.cursor()
#excutes query for flight
query = "select * from ticket natural join flight natural join airport as A, airport as B where cust_email = %s and dept_time < %s and dept_from = A.name and arr_at = B.name"
cursor.execute(query, (session['email'], datetime.datetime.now()))
#store the results
data = cursor.fetchall()
conn.commit()
cursor.close()
return render_template("comments.html", flights=data)
def load_follower_followed_data():
cur = conn.cursor()
cur.execute("""SELECT id, username FROM twitter_users tu
INNER JOIN huff_twitter_users_junction htuj ON twitter_user_id = tu.id
WHERE followers IS NULL AND followed IS NULL""")
update_followers_and_followed_count_for_fetched(cur)
cur.execute("""SELECT id, username FROM twitter_users tu
WHERE followers IS NULL AND followed IS NULL""")
update_followers_and_followed_count_for_fetched(cur)
def search(cls, text):
if "'" in text:
t = text.split("'")
text = "''".join(t)
query = f"SELECT title, author, year, id, isbn FROM book WHERE title ILIKE '%{text}%' OR author ILIKE '%{text}%'"
cursor = conn.cursor()
cursor.execute(query)
result = cursor.fetchall()
cursor.close()
return result
def staffHome():
#fetch data from session
username = session["username"]
cursor = conn.cursor()
query = 'SELECT first_name, last_name, airline_name FROM airline_staff WHERE username = %s'
cursor.execute(query, (username))
data = cursor.fetchone()
cursor.close()
#debugging
print(data["first_name"], data["last_name"], data["airline_name"])
cursor.close()
return render_template("staffHome.html", username=username, info=data)
def test_add_song(conn):
"""test if add_song works as expected"""
filename = os.listdir("./music/mp3")[0]
pathfile = "./music/mp3/" + filename
tup = c.meta(pathfile)
d.add_song(tup, conn)
# check if the song is added
cur = conn.cursor()
query = 'SELECT count(*) FROM music where title = %s'
val = (filename[:-4], )
cur.execute(query, val)
rowcount = cur.fetchall()
assert rowcount[0][0] != 0, "add_song works"
def load_user(username):
query = f"SELECT username, password, email, id FROM users WHERE username = '******' OR email = '{username}'"
cursor = conn.cursor()
cursor.execute(query)
user = cursor.fetchone()
cursor.close()
if user:
username = user[0]
password = user[1]
email = user[2]
id = user[3]
return User(id, username, password, email)
return None
def add_coment(cls, book_id, user_id, published, content):
cursor = conn.cursor()
# check if user allready add comment for this book
query = f"SELECT * FROM review WHERE user_id = '{user_id}' AND book_id = '{book_id}'"
cursor.execute(query)
comment = cursor.fetchone()
if comment:
cursor.close()
return False
query=f"INSERT INTO review (book_id, user_id, published, content) VALUES ({book_id}, {user_id}, '{published}', '{content}')"
cursor.execute(query)
conn.commit()
cursor.close()
return True
def load_followers_for_users_from_file():
huff_twitt_matches = open("matching_users.txt", "r")
failed_users_file = open("failed_users3.txt", "a")
cur = conn.cursor()
start_time = time.time()
line_no = 0
LINES_TO_SKIP = 0
for line in huff_twitt_matches:
curr_time = time.time()
print("Elapsed time: ", curr_time - start_time)
line_no += 1
if line_no < LINES_TO_SKIP:
continue
try:
huff_name, twitter_names = split_huff_and_twitter_line_from_list(line)
for twitter_name in twitter_names:
twitter_user = get_api_and_move_to_end().get_user(twitter_name) # 1 CALL
print("Working on ", twitter_name, " having ", twitter_user.followers_count, "followers")
save_user_in_db(twitter_user, cur)
link_huff_user_with_twitter(huff_name, twitter_user.id, cur)
next_cursor = -1
followers = get_api_and_move_to_end().followers(twitter_user.id, next_cursor) # 1 CALL
for follower in followers:
save_user_in_db(follower, cur)
save_a_follower(twitter_user.id, follower.id, cur)
try:
conn.commit()
except:
print("### SOMETHING BAD HAS HAPPENED WHEN WORKING ON {}, BUT WE GO FORWARD".format(twitter_name))
conn.rollback()
failed_users_file.write(line + "\n")
failed_users_file.flush()
except:
print("### SOMETHING HAS BROKEN INCORRECTLY ", str(sys.exc_info()), traceback.print_exc())
conn.rollback()
failed_users_file.write(line)
failed_users_file.flush()
failed_users_file.close()
def extract_hashtags_from_tweets():
cur = conn.cursor()
cur.execute("SELECT t.id, t.text FROM tweets t")
for row in cur.fetchall():
tweet_id = row[0]
hashtags = re.findall("#([a-zA-Z]+)", row[1])
print(hashtags)
for hashtag in set(hashtags):
hashtag = hashtag.lower()
cur.execute("SELECT tweet_id FROM twitter_hashtags WHERE tweet_id = %s AND hashtag = %s",
(tweet_id, hashtag))
if not cur.fetchone():
cur.execute("INSERT INTO twitter_hashtags (tweet_id, hashtag) VALUES (%s, %s)", (tweet_id, hashtag))
conn.commit()
def transform_huff_tags():
cur = conn.cursor()
cur.execute("SELECT id, name FROM tags")
i = 0
for tag_id, name in cur.fetchall():
i += 1
if i % 1000 == 0:
print(i)
conn.commit()
new_name = name.replace(" ", "").lower()
cur.execute("INSERT INTO cleaned_tags (tag_id, name) VALUES (%s, %s)", (tag_id, new_name))
conn.commit()
def top_hashtags():
top_tags = open("data/huff_top_tags.csv", "r")
failed_tags_file = open("failed_tags.txt", "a")
cur = conn.cursor()
start_time = time.time()
LINES_TO_SKIP = 0
line_no = 0
for tag in top_tags:
line_no += 1
if line_no < LINES_TO_SKIP:
continue
tag = tag.strip()
tag = tag.replace(" ", "")
print("Search for tag ", tag)
print("Time elapsed: ", (time.time() - start_time))
try:
search_results = get_api_and_move_to_end().search(q="#" + tag, lang="en", rpp=100)
for result in search_results:
if not is_user_in_db(result.author.id, cur):
save_user_in_db(result.author, cur)
cur.execute("""INSERT INTO tweets (id, author_id, text, date)
VALUES (%s, %s, %s, %s)""", (result.id, result.author.id, result.text, result.created_at))
conn.commit()
except:
print("### SOMETHING HAS BROKEN INCORRECTLY ", str(sys.exc_info()), traceback.print_exc())
conn.rollback()
failed_tags_file.write(tag)
failed_tags_file.flush()
def load_following_for_users_from_file():
huff_twitt_matches = open("matching_users.txt", "r")
failed_users_file = open("failed_users4.txt", "a")
cur = conn.cursor()
start_time = time.time()
existing_users = 0
not_existing_users = 0
line_no = 0
LINES_TO_SKIP = 124
for line in huff_twitt_matches:
curr_time = time.time()
print("Elapsed time: ", curr_time - start_time, "existed:", existing_users, "not existed:", not_existing_users)
line_no += 1
if line_no < LINES_TO_SKIP:
continue
try:
huff_name, twitter_names = split_huff_and_twitter_line_from_list(line)
for twitter_name in twitter_names:
print("Working on ", twitter_name)
if not is_user_in_db(twitter_name, cur):
twitter_user = get_api_and_move_to_end().get_user(twitter_name) # 1 CALL
print("who wasn't in db")
save_user_in_db(twitter_user, cur)
link_huff_user_with_twitter(huff_name, twitter_user.id, cur)
next_cursor = -1
response = get_api_and_move_to_end().friends_ids(screen_name=twitter_name,
cursor=next_cursor) # 1 CALL
followed_ids = response[0]
print("Has ", len(followed_ids), " follows")
if len(followed_ids) > 100: # limit to 100
followed_ids = followed_ids[:100]
for followed_id in followed_ids:
if not is_user_in_db(followed_id, cur):
save_user_in_db(followed_id, cur)
not_existing_users += 1
else:
existing_users += 1
save_a_follower(followed_id, get_id_by_name(twitter_name, cur), cur)
try:
conn.commit()
except:
print("### SOMETHING BAD HAS HAPPENED WHEN WORKING ON {}, BUT WE GO FORWARD".format(twitter_name))
conn.rollback()
failed_users_file.write(line + "\n")
failed_users_file.flush()
except:
print("### SOMETHING HAS BROKEN INCORRECTLY ", str(sys.exc_info()), traceback.print_exc())
conn.rollback()
failed_users_file.write(line)
failed_users_file.flush()
failed_users_file.close()
from database import conn
import csv
cur = conn.cursor()
cur.execute("""SELECT follower.id AS follower, followed.id AS followed
FROM twitter_follows tf
INNER JOIN twitter_users AS follower ON tf.follower = follower.id
INNER JOIN twitter_users AS followed ON tf.followed = followed.id""")
to_graphviz_file = open("data/user_edges.csv", "w")
csv_writer = csv.writer(to_graphviz_file)
csv_writer.writerow(["Source", "Target"])
for follower, followed in cur.fetchall():
csv_writer.writerow([follower, followed])
cur.execute("""SELECT id, username,
EXISTS (SELECT * FROM huff_twitter_users_junction WHERE twitter_user_id = id) AS in_huff_db
FROM twitter_users WHERE TRUE""")
to_graphviz_file = open("data/user_nodes.csv", "w")
csv_writer = csv.writer(to_graphviz_file)
csv_writer.writerow(["id", "username", "is_in_huff"])
for user_id, user_name, is_in_huff in cur.fetchall():
csv_writer.writerow([user_id, user_name.strip(), is_in_huff])
