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])