-
Notifications
You must be signed in to change notification settings - Fork 3
/
app.py
76 lines (61 loc) · 2.34 KB
/
app.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
from flask import Flask, render_template, request, jsonify
from flask import Flask
import mysql.connector
from mysql.connector import errorcode
from dbInteraction import init_db
app = Flask(__name__)
@app.route('/', methods=['GET', 'POST'])
def test_app_root():
"""
Default table route, shows the database with tickboxes to mark visited roads.
:return: A rendered HTML template with the database Geojson_data.
:rtype: flask.Response
"""
try:
conn = mysql.connector.connect(
host='localhost',
user='root',
password='change-me',
database='Test_DB_Zero',
)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Invalid username or password")
else:
print(f"Error connecting to the MySQL server: {err}")
exit(1)
cursor = conn.cursor()
if request.method == 'POST':
updated_roads = request.form.getlist('visited')
visited_by = request.form.getlist('leafletter')[0]
print("POST called: ", updated_roads, flush=True)
print("visited_by: ", visited_by, flush=True)
# cursor.execute("UPDATE Roads SET Visited = 0")
for road_id in updated_roads:
print("road_id:", road_id, flush=True)
cursor.execute("UPDATE Roads SET visited = true, visited_by=%s WHERE road_id = %s", (visited_by, road_id,))
conn.commit()
query = ("SELECT r.road_id, r.road_name, "
"CASE WHEN r.visited = 1 THEN 'Yes' ELSE 'No' END AS visited, "
"COALESCE(l.username, '-') AS username "
"FROM Roads as r "
"LEFT JOIN Leafletters as l "
"ON l.user_id = r.visited_by "
"ORDER BY r.road_name")
cursor.execute(query)
rows = cursor.fetchall()
cursor.execute("SELECT * FROM Leafletters order by username")
leafletters = cursor.fetchall()
conn.close()
return render_template('index.html', rows=rows, leafletters=leafletters)
# The route for showing an interactive map that draws from the database in the previous page.
@app.route('/map')
def test_app_map():
return "Map page"
# Initialize the database
@app.route('/initdb', methods=['GET', 'POST'])
def post_endpoint():
init_db()
return jsonify({'message': 'Success'})
if __name__ == '__main__':
app.run(debug=True, host='0.0.0.0', port=4000)