def test_alerts(self): """Test if alert sender function sends email to subscribed users""" with captured_output() as (out, err): with self.app.app_context(): init_db() get_db().executescript(_data_sql_email) send_alerts_to_subscribed_users(self.app) output = out.getvalue().strip() self.assertIn('Sent email to [email protected]', output)
def test_expired_entries_cleanup(self): """Test if the expired entries are removed from database""" with captured_output() as (out, err): with self.app.app_context(): init_db() get_db().executescript(_data_sql_expired_entry) remove_outdated_entries(self.app) output = out.getvalue().strip() self.assertIn('*****@*****.**', output) self.assertIn('AUH', output) self.assertIn('DOH', output) self.assertIn('2020-05-01', output)
def remove_outdated_entries(app): print('Started cron job which cleans out of date database entries') with app.app_context(): db = get_db() # Select Query to check which rows will be deleted # This is done because delete statement has no output clause and we # need to see what all rows got deleted. https://stackoverflow.com/a/43211369 cursor = db.execute(""" SELECT * FROM tracked_flights WHERE (return_date IS NULL AND date('now') > departure_date) OR (return_date IS NOT NULL AND date('now') > return_date) """) print('Deleting the following rows...') for row in cursor.fetchall(): print(tuple(row)) # Query to delete out of date entries db.execute(""" DELETE FROM tracked_flights WHERE (return_date IS NULL AND date('now') > departure_date) OR (return_date is NOT NULL AND date('now') > return_date) """) db.commit() print('Out of date entries deleted.')
def get_user_emails(app): """Returns all the user emails present in the database""" with app.app_context(): db = get_db() cursor = db.execute(""" SELECT DISTINCT email FROM tracked_flights """) return [row[0] for row in cursor.fetchall()]
def setUp(self): """Method called prior each unittest execution""" self.db_fd, self.db_path = tempfile.mkstemp() app = create_app({'TESTING': True, 'DATABASE': self.db_path}) with app.app_context(): # NOTE: Make sure that insert commands in schema.sql are commented or removed init_db() get_db().executescript(_data_sql) self.app = app self.app_client = app.test_client() self.tokens = { 'test_unsubscribe': 'InRlc3Rib2lAdGVzdGJvaS5jb20i.cshVCqvl20ukOAh1Tgv53NDtjQk', 'test_flightstatus': 'InRlc3Rib2lAdGVzdGJvaS5jb20i.jTrlhxNTqHIsz3kTVweRT9pUo18', 'evil_unsubscribe': 'ImV2aWxib2lAZXZpbGJvaS5jb20i.lMCPwRVp2XAOSqaJzGccdyDQtcE' }
def update_stored_prices(app, updates): with app.app_context(): db = get_db() for update in updates: db.execute( """ UPDATE tracked_flights SET prev_price = ? WHERE id = ? """, (update[1], update[0])) db.commit()
def get_tracked_flights(app, email): """Returns all the flights tracked by given email""" with app.app_context(): db = get_db() cursor = db.execute( """ SELECT * FROM tracked_flights WHERE email = ? """, (email, )) column_names = list(map(lambda x: x[0], cursor.description)) return [{k: v for k, v in zip(column_names, tuple(row))} for row in cursor.fetchall()]
def make_entry(email_id, flight_details, price_details): db = get_db() if len(flight_details) == 2: db.execute( """ INSERT INTO tracked_flights(id, email, dept_aircraft_code, dept_carrier_code, return_aircraft_code, return_carrier_code, adults, children, infants, from_location, to_location, departure_date, return_date, type_of_class, prev_price) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) """, (None, email_id, flight_details[0]['aircraft_code'], flight_details[0]['carrier_code'], flight_details[1]['aircraft_code'], flight_details[1]['carrier_code'], flight_details[0]['adults'], flight_details[0]['children'], flight_details[0]['infants'], flight_details[0]['from_location'], flight_details[0]['to_location'], flight_details[0]['departure_date'], flight_details[0]['return_date'], flight_details[0]['type_of_class'], str(price_details)) ) else: db.execute( """ INSERT INTO tracked_flights(id, email, dept_aircraft_code, dept_carrier_code, return_aircraft_code, return_carrier_code, adults, children, infants, from_location, to_location, departure_date, return_date, type_of_class, prev_price) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) """, (None, email_id, flight_details[0]['aircraft_code'], flight_details[0]['carrier_code'], None, None, flight_details[0]['adults'], flight_details[0]['children'], flight_details[0]['infants'], flight_details[0]['from_location'], flight_details[0]['to_location'], flight_details[0]['departure_date'], None, flight_details[0]['type_of_class'], str(price_details)) ) print('Entry made into DB') db.commit()
def entry_exists(email_id, flight_details): db = get_db() if len(flight_details) == 2: round_entry = ( email_id, flight_details[0]['aircraft_code'], flight_details[0]['carrier_code'], flight_details[1]['aircraft_code'], flight_details[1]['carrier_code'], flight_details[0]['adults'], flight_details[0]['children'], flight_details[0]['infants'], flight_details[0]['from_location'], flight_details[0]['to_location'], flight_details[0]['departure_date'], flight_details[0]['return_date'], flight_details[0]['type_of_class'], ) row = db.execute( """ SELECT * FROM tracked_flights WHERE email = ? AND dept_aircraft_code = ? AND dept_carrier_code = ? AND return_aircraft_code = ? AND return_carrier_code = ? AND adults = ? AND children = ? AND infants = ? AND from_location = ? AND to_location = ? AND departure_date = ? AND return_date = ? AND type_of_class = ? """, round_entry ).fetchall() return (len(row) != 0) else: one_way_entry = ( email_id, flight_details[0]['aircraft_code'], flight_details[0]['carrier_code'], flight_details[0]['adults'], flight_details[0]['children'], flight_details[0]['infants'], flight_details[0]['from_location'], flight_details[0]['to_location'], flight_details[0]['departure_date'], flight_details[0]['type_of_class'], ) row = db.execute( """ SELECT * FROM tracked_flights WHERE email = ? AND dept_aircraft_code = ? AND dept_carrier_code = ? AND adults = ? AND children = ? AND infants = ? AND from_location = ? AND to_location = ? AND departure_date = ? AND type_of_class = ? """, one_way_entry ).fetchall() return (len(row) != 0)
def index(token): # Token verification with open('credentials.txt') as f: credentials = {k: v for k, v in map(str.split, f.readlines())} server_secret = credentials['SERVER_SECRET'] serializer = URLSafeSerializer(server_secret, salt='unsubscribe') try: email = serializer.loads(token) except BadData as e: return 'Invalid token provided' # Take user input, unsubscribe and display confirmation if request.method == 'POST': # for i in request.form.items(): # print(i) flightids_continue_tracking = [ k for (k, v) in request.form.items() if v == 'on' ] # Print rows which will be deleted. db = get_db() cursor = db.execute( """ SELECT * FROM tracked_flights WHERE email = ? AND id NOT IN %s """ % ('(' + ', '.join(flightids_continue_tracking) + ')'), (email, )) print('Deleting the following rows...') for row in cursor.fetchall(): print(tuple(row)) # Delete the rows db.execute( """ DELETE FROM tracked_flights WHERE email = ? AND id NOT IN %s """ % ('(' + ', '.join(flightids_continue_tracking) + ')'), (email, )) db.commit() # Display unsubscribe page # Get tracked flights db = get_db() cursor = db.execute( """ SELECT * FROM tracked_flights WHERE email = ? """, (email, )) column_names = list(map(lambda x: x[0], cursor.description)) flight_details = [{k: v for k, v in zip(column_names, tuple(row))} for row in cursor.fetchall()] return render_template('unsubscribe.html', flight_details=flight_details)