def index():
# 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"])
# sql_query_names = "SELECT name FROM contacts"
# names = query_database(database_tuple[1], sql_query_names)
# names = [name[0] for name in names]
names = query_names(database_tuple[1])
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;"""
meetings = query_database(database_tuple[1], sql_query)
return render_template('index.html',
names=names,
meetings=meetings,
page_title="Covid Diary")
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 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