Ideas & Design

This week's assignment is part of my final project. Recall that I want to make a skateboard with a sensor that can send data of acceleration and geolocation(lat,lng) to the cloud while I am skating around the city. I want a clear and user-friendly interface to present the data that I collect from the sensors. Finally, I choose to add data points to an interactive map with some charts and tables around.

MySQL database

As discussed in Week11, we host a database in MySQL server with the use of XAMPP

The only challenge this week is to build a responsive frontend to present the data.

Flask as a back end

It is impossible for a front-edn to fetch data directly from the database. We need flask as a backend to bridge the gap between these two.

image source: link

We use mysql.connector to make connection to the MySQL database and use SQL syntax to select the data we want.

                  
              
from flask import Flask, render_template, jsonify
import mysql.connector
from flask_cors import CORS  # Import the CORS module

app = Flask(__name__)
CORS(app)  # Enable CORS for all routes in your Flask app

# Configure MySQL connection
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="mapping"
)

# Create a cursor to interact with the database
cursor = db.cursor()

@app.route('/')
def index():
    # Example query to fetch data
    query = "SELECT * FROM bikelane"
    cursor.execute(query)

    # Fetch all the results
    data = cursor.fetchall()

    # Close the cursor and database connection
    cursor.close()
    db.close()

    # Render the template with the fetched data
    return render_template('index.html', data=data)

@app.route('/api/data', methods=['GET'])
def get_data():
    # Example query to fetch data
    query = "SELECT * FROM bikelane"
    cursor.execute(query)

    # Fetch all the results
    data = cursor.fetchall()

    # Close the cursor and database connection
    cursor.close()
    db.close()
    return jsonify({"data": data})

if __name__ == '__main__':
    app.run(debug=True)

              
              

HTML CSS & JavaScript as a frontend

Flask provides html_template which is a perfect tool to enable us to integrate frontend into the flask app with a simple line of code return render_template('index.html', data=data)

To be noticed, the files should be placed in a specific structure. The index.html must be placed in a folder named "templates"

Then, we are able to get a simple web page presenting all the data we have in the MySQL database

After working on the HTML, CSS and JavaScript for a while, we finally got this satisfying interface!

All the data with geolocation will pop up on the interactive map. Every time a user refresh the web page, it will synchronize with the MySQL database.