def query_data():
# connect to the database with the filename configured above
# returning a 2-tuple that contains a connection and cursor object
# --> see file database_helpers for more
database_tuple = connect_to_database(app.config["DATABASE_FILE"])
# now, get all of the meetings from the database, not just the new one.
# first, define the query to get all meetings:
today = datetime.today()
delta = (today - timedelta(days=15)).replace(hour=0,
minute=0,
second=0,
microsecond=0)
delta_timestamp = datetime.timestamp(delta)
sql_query = f"SELECT * FROM Meetings WHERE (Timestamp > {delta_timestamp});"
print(sql_query)
# query the database, by passinng the database cursor and query,
# we expect a list of tuples corresponding to all rows in the database
query_response = query_database(database_tuple[1], sql_query)
close_conection_to_database(database_tuple[0])
return query_response
def index():
try:
date = time.time()
date = int(date)
# connect to the database with the filename configured above
# returning a 2-tuple that contains a connection and cursor object
# --> see file database_helpers for more
database_tuple = connect_to_database(app.config["DATABASE_FILE"])
# now, get all of the meetings from the database, not just the new one.
# first, define the query to get all meetings:
sql_query = "SELECT * FROM Meetings WHERE date BETWEEN \"{date}\"- 1209600 AND \"{date}\" ;".format(
date=date)
# query the database, by passinng the database cursor and query,
# we expect a list of tuples corresponding to all rows in the database
query_response = query_database(database_tuple[1], sql_query)
close_conection_to_database(database_tuple[0])
# In addition to HTML, we will respond with an HTTP Status code
# The status code 201 means "created": a row was added to the database
return render_template('index.html',
page_title="Covid Diary",
meetings=query_response)
except Exception:
# something bad happended. Return an error page and a 500 error
error_code = 500
return render_template('error.html', page_title=error_code), error_code
def create_meeting():
try:
# name = request.form.get('name')
# meetingdate = request.form.get('meetingdate')
# app.logger.info(name)
# turn this into an SQL command. For example:
# "Adam" --> "INSERT INTO Meetings (name) VALUES("Adam");"
# sql_insert = "INSERT INTO Contacts (name, meetingdate) VALUES (\"{name}\", \"{meetingdate}\");".format(
# name=name, meetingdate=meetingdate)
# connect to the database with the filename configured above
# returning a 2-tuple that contains a connection and cursor object
# --> see file database_helpers for more
database_tuple = connect_to_database(app.config["DATABASE_FILE"])
# now that we have connected, add the new meeting (insert a row)
# --> see file database_helpers for more
#change_database(database_tuple[0], database_tuple[1], sql_insert)
# now, get all of the meetings from the database, not just the new one.
# first, define the query to get all meetings:
# sql_query = "SELECT * FROM Meetings ORDER BY Meetingdate DESC;"
# query the database, by passinng the database cursor and query,
# we expect a list of tuples corresponding to all rows in the database
#query_response = query_database(database_tuple[1], sql_query)
close_conection_to_database(database_tuple[0])
# find today's date, the date two weeks ago, and pare down the list of
# meetings based on those dates to the last two weeks worth
# date_today = get_todays_date()
# date_two_weeks_ago = get_two_weeks_ago_date(date_today)
# recent_meetings = get_recent_meetings(query_response,
# date_today,
# date_two_weeks_ago)
# calendar_of_21_days = build_calendar_of_21_days(recent_meetings,
# date_today,
# date_two_weeks_ago)
# formatted_date_today = format_date(date_today)
# formatted_date_two_weeks_ago = format_date(date_two_weeks_ago)
# return render_template('index.html', page_title="Covid Diary",
# meetings=recent_meetings,
# date_today=formatted_date_today,
# date_two_weeks_ago=formatted_date_two_weeks_ago,
# calendar=calendar_of_21_days,
# query_response=query_response), 201
except Exception:
# something bad happended. Return an error page and a 500 error
error_code = 500
return render_template('error.html', page_title=error_code), error_code
def create_meeting():
try:
name = request.form.get('name')
location = request.form.get('location')
# app.logger.info(name)
# turn this into an SQL command. For example:
# "Adam" --> "INSERT INTO Meetings (name) VALUES("Adam");"
sql_insert = "INSERT INTO Meetings (name, date, location) VALUES (\"{name}\", \"{date}\", \"{location}\");".format(
name=name, date=date, location=location)
# connect to the database with the filename configured above
# returning a 2-tuple that contains a connection and cursor object
# --> see file database_helpers for more
database_tuple = connect_to_database(app.config["DATABASE_FILE"])
# now that we have connected, add the new meeting (insert a row)
# --> see file database_helpers for more
change_database(database_tuple[0], database_tuple[1], sql_insert)
# now, get all of the meetings from the database, not just the new one.
# first, define the query to get all meetings:
sql_query_recent = "SELECT * FROM Meetings WHERE date > \"{dateTwoWeeks}\" ORDER BY date DESC;".format(
dateTwoWeeks=dateTwoWeeks)
sql_query_general = "SELECT * FROM Meetings ORDER BY date DESC;"
sql_query_contacts = "SELECT * FROM Contacts;"
sql_query_locations = "SELECT * FROM Locations;"
# query the database, by passinng the database cursor and query,
# we expect a list of tuples corresponding to all rows in the database
query_response_recent = query_database(database_tuple[1],
sql_query_recent)
query_response_general = query_database(database_tuple[1],
sql_query_general)
query_response_contacts = query_database(database_tuple[1],
sql_query_contacts)
query_response_locations = query_database(database_tuple[1],
sql_query_locations)
close_conection_to_database(database_tuple[0])
# In addition to HTML, we will respond with an HTTP Status code
# The status code 201 means "created": a row was added to the database
return render_template('index.html',
page_title="Covid Diary",
meetings_recent=query_response_recent,
meetings_general=query_response_general,
contacts=query_response_contacts,
locations=query_response_locations), 201
except Exception:
# something bad happended. Return an error page and a 500 error
error_code = 500
return render_template('error.html', page_title=error_code), error_code
def create_meeting():
try:
name = request.form.get("name")
date = request.form.get("date")
# app.logger.info(name)
# turn this into an SQL command. For example:
# "Adam" --> "INSERT INTO Meetings (name) VALUES("Adam");"
sql_insert = (
'INSERT INTO Meetings (name, date) VALUES ("{name}","{date}") ;'.
format(name=name, date=date))
# connect to the database with the filename configured above
# returning a 2-tuple that contains a connection and cursor object
# --> see file database_helpers for more
database_tuple = connect_to_database(app.config["DATABASE_FILE"])
# now that we have connected, add the new meeting (insert a row)
# --> see file database_helpers for more
change_database(database_tuple[0], database_tuple[1], sql_insert)
# now, get all of the meetings from the database, not just the new one.
# first, define the query to get all meetings:
sql_query = "SELECT Name, julianday('now')-julianday(Date) as day FROM Meetings where day < 15;"
# query the database, by passinng the database cursor and query,
# we expect a list of tuples corresponding to all rows in the database
query_response = query_database(database_tuple[1], sql_query)
close_conection_to_database(database_tuple[0])
# In addition to HTML, we will respond with an HTTP Status code
# The status code 201 means "created": a row was added to the database
return (
render_template("index.html",
page_title="Covid Diary",
meetings=query_response),
201,
)
except Exception:
# something bad happended. Return an error page and a 500 error
error_code = 500
return render_template("error.html", page_title=error_code), error_code
def add_new_contact():
msg_color = "green"
msg = "Added :)"
new_contact = request.form.get('new_contact')
database_tuple = connect_to_database(app.config["DATABASE_FILE"])
if new_contact != "":
sql_insert_contact = "INSERT INTO Contacts (name) VALUES (\"{new_contact}\");".format(
new_contact=new_contact)
change_database(database_tuple[0], database_tuple[1],
sql_insert_contact)
else:
msg = "Nooooo :( You forgot to write the new contact details into the box"
msg_color = "red"
sql_query_recent = "SELECT * FROM Meetings WHERE date > \"{dateTwoWeeks}\" ORDER BY date DESC;".format(
dateTwoWeeks=dateTwoWeeks)
sql_query_general = "SELECT * FROM Meetings ORDER BY date DESC;"
sql_query_contacts = "SELECT * FROM Contacts;"
sql_query_locations = "SELECT * FROM Locations;"
query_response_recent = query_database(database_tuple[1], sql_query_recent)
query_response_general = query_database(database_tuple[1],
sql_query_general)
query_response_contacts = query_database(database_tuple[1],
sql_query_contacts)
query_response_locations = query_database(database_tuple[1],
sql_query_locations)
close_conection_to_database(database_tuple[0])
return render_template('index.html',
page_title="Covid Diary",
meetings_recent=query_response_recent,
meetings_general=query_response_general,
contacts=query_response_contacts,
locations=query_response_locations,
msg=msg,
msg_color=msg_color), 201
def index():
try:
# Connect to database and get all meetings sorted by most recent to oldest
database_tuple = connect_to_database(app.config["DATABASE_FILE"])
#sql_query = "SELECT * FROM Meetings ORDER BY Meetingdate DESC;"
# query_response = query_database(database_tuple[1], sql_query)
# close_conection_to_database(database_tuple[0])
contacts_sql_query = "SELECT Name FROM Contacts;"
contacts_query_response = query_database(database_tuple[1], contacts_sql_query)
close_conection_to_database(database_tuple[0])
# Define today's date and the date two weeks ago, and render index with only those meetings
# date_today = get_todays_date()
# date_two_weeks_ago = get_two_weeks_ago_date(date_today)
# recent_meetings = get_recent_meetings(query_response,
# date_today,
# date_two_weeks_ago)
# calendar_of_21_days = build_calendar_of_21_days(recent_meetings,
# date_today,
# date_two_weeks_ago)
# formatted_date_today = format_date(date_today)
# formatted_date_two_weeks_ago = format_date(date_two_weeks_ago)
# return render_template('index.html', page_title="Covid Diary",
# meetings=recent_meetings,
# date_today=formatted_date_today,
# date_two_weeks_ago=formatted_date_two_weeks_ago,
# calendar=calendar_of_21_days,
# query_response=query_response), 201
return render_template('index.html', page_title="Covid Diary",
contacts_query_response=contacts_query_response), 201
except Exception:
# something bad happended. Return an error page and a 500 error
error_code = 500
return render_template('error.html', page_title=error_code), error_code
def create_meeting():
try:
name = request.form.get('name')
# app.logger.info(name)
# turn this into an SQL command. For example:
# "Adam" --> "INSERT INTO Meetings (name) VALUES("Adam");"
sql_insert = "INSERT OR REPLACE INTO Meetings (name, date) VALUES (\"{name}\", date('now','-0 day'));".format(
name=name)
delete_older = "DELETE FROM meetings WHERE date <= date('now','-14 day');"
# connect to the database with the filename configured above
# returning a 2-tuple that contains a connection and cursor object
# --> see file database_helpers for more
database_tuple = connect_to_database(app.config["DATABASE_FILE"])
# now that we have connected, add the new meeting (insert a row)
# --> see file database_helpers for more
change_database(database_tuple[0], database_tuple[1], sql_insert)
change_database(database_tuple[0], database_tuple[1], delete_older)
# now, get all of the meetings from the database, not just the new one.
# first, define the query to get all meetings:
sql_query = "SELECT * FROM Meetings;"
# query the database, by passinng the database cursor and query,
# we expect a list of tuples corresponding to all rows in the database
query_response = query_database(database_tuple[1], sql_query)
close_conection_to_database(database_tuple[0])
# In addition to HTML, we will respond with an HTTP Status code
# The status code 201 means "created": a row was added to the database
return render_template('index.html', page_title="Covid Diary",
meetings=query_response), 201
except Exception:
# something bad happended. Return an error page and a 500 error
error_code = 500
return render_template('error.html', page_title=error_code), error_code
def index():
try:
database_tuple = connect_to_database(app.config["DATABASE_FILE"])
# now, get all of the meetings from the database, not just the new one.
# first, define the query to get all meetings:
sql_query_recent = "SELECT * FROM Meetings WHERE date > \"{dateTwoWeeks}\" ORDER BY date DESC;".format(
dateTwoWeeks=dateTwoWeeks)
sql_query_general = "SELECT * FROM Meetings ORDER BY date DESC;"
sql_query_contacts = "SELECT * FROM Contacts;"
sql_query_locations = "SELECT * FROM Locations;"
# query the database, by passinng the database cursor and query,
# we expect a list of tuples corresponding to all rows in the database
query_response_recent = query_database(database_tuple[1],
sql_query_recent)
query_response_general = query_database(database_tuple[1],
sql_query_general)
query_response_contacts = query_database(database_tuple[1],
sql_query_contacts)
query_response_locations = query_database(database_tuple[1],
sql_query_locations)
close_conection_to_database(database_tuple[0])
# In addition to HTML, we will respond with an HTTP Status code
# The status code 201 means "created": a row was added to the database
return render_template('index.html',
page_title="Covid Diary",
meetings_recent=query_response_recent,
meetings_general=query_response_general,
contacts=query_response_contacts,
locations=query_response_locations), 201
except Exception:
# something bad happended. Return an error page and a 500 error
error_code = 500
return render_template('error.html', page_title=error_code), error_code
def create_meeting():
try:
today = datetime.today()
date_str = today.strftime("%Y/%m/%d")
timestamp = datetime.timestamp(today)
name = request.form.get('name')
# app.logger.info(name)
# turn this into an SQL command. For example:
# "Adam" --> "INSERT INTO Meetings (name) VALUES("Adam");"
sql_insert = f"INSERT INTO Meetings (Name, Date, Timestamp) VALUES (\"{name}\", \"{date_str}\", \"{timestamp}\");"
print(sql_insert)
# connect to the database with the filename configured above
# returning a 2-tuple that contains a connection and cursor object
# --> see file database_helpers for more
database_tuple = connect_to_database(app.config["DATABASE_FILE"])
# now that we have connected, add the new meeting (insert a row)
# --> see file database_helpers for more
change_database(database_tuple[0], database_tuple[1], sql_insert)
close_conection_to_database(database_tuple[0])
meetings = query_data()
# In addition to HTML, we will respond with an HTTP Status code
# The status code 201 means "created": a row was added to the database
return render_template('index.html',
page_title="Covid Diary",
meetings=meetings), 201
except Exception as error:
print(error)
# something bad happended. Return an error page and a 500 error
error_code = 500
return render_template('error.html', page_title=error_code), error_code
def create_meeting():
name = request.form.get('name')
date = request.form.get('date')
g = geocoder.ip('me')
localisation = g.address
# app.logger.info(name)
# turn this into an SQL command. For example:
# "Adam" --> "INSERT INTO Meetings (name) VALUES("Adam");"
sql_insert = """
INSERT INTO Meetings (name, date, localisation) VALUES (\"{name}\",\"{date}\",\"{localisation}\");
""".format(name=name, date=date, localisation=localisation)
# connect to the database with the filename configured above
# returning a 2-tuple that contains a connection and cursor object
# --> see file database_helpers for more
database_tuple = connect_to_database(app.config["DATABASE_FILE"])
# now that we have connected, add the new meeting (insert a row)
# --> see file database_helpers for more
change_database(database_tuple[0], database_tuple[1], sql_insert)
# now, get all of the meetings from the database, not just the new one.
# first, define the query to get all meetings:
sql_query = "SELECT * FROM Meetings;"
# query the database, by passinng the database cursor and query,
# we expect a list of tuples corresponding to all rows in the database
query_response = query_database(database_tuple[1], sql_query)
close_conection_to_database(database_tuple[0])
# In addition to HTML, we will respond with an HTTP Status code
# The status code 201 means "created": a row was added to the database
return render_template('index.html',
page_title="Covid Diary",
meetings=query_response), 201
def create_meeting():
try:
name = request.form.get('name')
phone_nb = request.form.get('phone_nb')
address = request.form.get('address')
e_mail = request.form.get('e-mail')
# app.logger.info(name)
# turn this into an SQL command. For example:
# "Adam" --> "INSERT INTO Meetings (name) VALUES("Adam");"
sql_insert_contact = "INSERT INTO contacts (name, phone_nb, address, e_mail) VALUES (\"{name}\",\"{phone_nb}\",\"{address}\",\"{e_mail}\");".format(
name=name, phone_nb=phone_nb, address=address, e_mail=e_mail)
print(sql_insert_contact)
meeting_date = request.form.get('meeting_date')
contact = request.form.get('contacts')
print(meeting_date, contact)
sql_query_contact_id = "SELECT contact_id FROM contacts WHERE name = (\"{contact}\")".format(
contact=contact)
print(sql_query_contact_id)
database_tuple = connect_to_database(app.config["DATABASE_FILE"])
# now that we have connected, add the new meeting (insert a row)
# --> see file database_helpers for more
if name != None:
change_database(database_tuple[0], database_tuple[1],
sql_insert_contact)
elif meeting_date != None and contact != None:
contact_id = query_database(database_tuple[1],
sql_query_contact_id)
contact_id = int(contact_id[0][0])
print(contact_id)
sql_insert_meeting = "INSERT INTO meetings (meeting_date, contact_id) VALUES (\"{meeting_date}\",\"{contact_id}\");".format(
meeting_date=meeting_date, contact_id=contact_id)
print(sql_insert_meeting)
change_database(database_tuple[0], database_tuple[1],
sql_insert_meeting)
# now, get all of the meetings from the database, not just the new one.
# first, define the query to get all meetings:
sql_query = """SELECT date(meeting_date), contacts.name
FROM meetings
JOIN contacts ON meetings.contact_id=contacts.contact_id
WHERE meeting_date<=date('now') and meeting_date>=date('now','-14 days')
ORDER BY date(meeting_date) DESC;"""
# query the database, by passinng the database cursor and query,
# we expect a list of tuples corresponding to all rows in the database
meetings = query_database(database_tuple[1], sql_query)
print(meetings)
# meetings_table = tabulate(meetings, headers=["Name","Date"], showindex="Always", tablefmt="html")
names = query_names(database_tuple[1])
close_conection_to_database(database_tuple[0])
# In addition to HTML, we will respond with an HTTP Status code
# The status code 201 means "created": a row was added to the database
return render_template('index.html',
page_title="Covid Diary",
meetings=meetings,
names=names), 201
except Exception:
# something bad happended. Return an error page and a 500 error
error_code = 500
return render_template('error.html', page_title=error_code), error_code