LAB Assignment

$2$

$$

$$Developing a Flask Application with MySQL Database Integration

Student:

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$\_$

$$

Due Date: Sunday of Week

$6$

Purpose: The purpose of this Lab assignment is to:

$$

$$Develop a Flask application that interacts with a MySQL database

$($

Community Edition

$)$

$$

$$Implement CRUD operations.

References: Read the course

$$

s text

$-$

$$chapter

$5$

$.$

$$This material provides the necessary information that you need to complete the exercises.

$$

Be sure to read the following general instructions carefully:

$-$

$$This assignment must be completed individually by all the students.

$-$

$$You will have to demonstrate your solution in a scheduled lab session and upload the solution on eCentennial through the assignment link.

Exercise #

$1$

: Simple Flask App

In this exercise you will create a Flask application that connects to a MySQL database to add, view, update, and delete data, demonstrating a fundamental understanding of database integration in web applications.

Tasks

$1$

$.$

$$Environment and MySQL Database Setup:

a

$.$

$$Install MySQL Community Edition and set it up on your local machine.

b

$.$

$$Ensure Flask, Flask

$-$

SQLAlchemy, and Flask

$-$

Migrate are installed in your Flask environment.

$2$

$.$

$$Database Configuration in Flask:

a

$.$

$$Configure the Flask app to connect to the MySQL database.

b

$.$

$$Set up the database URI in the Flask configuration to use MySQL

$.$

$3$

$.$

$$Model Creation and Relationships:

a

$.$

$$Define models in Flask to mirror the entities in your MySQL database

$($

e

$.$

g

$.$

$,$

$$User$,$ Post, Product

$)$

$.$

b

$.$

$$Establish relationships between models

$($

one

$-$

to

$-$

many, many

$-$

to

$-$

many, etc.

$)$

$.$

$4$

$.$

$$Implementing Database Migrations:

a

$.$

$$Use Flask

$-$

Migrate to handle migrations for the MySQL database.

b

$.$

$$Create and apply migration scripts for initial database setup and any subsequent schema changes.

$5$

$.$

$$CRUD Operations:

a

$.$

$$Implement CRUD operations in Flask, interacting with the MySQL database.

b

$.$

$$Test database operations

$($

Create

$,$

$$Read$,$ Update, Delete

$)$

$$to ensure they work as expected.

$6$

$.$

$$Integrating CRUD Operations with Flask View Functions:

a

$.$

$$Create routes and views in Flask for each CRUD operation.

b

$.$

$$Design and implement forms for data entry and templates for data display.

$7$

$.$

$$Front

$-$

End Development:

a

$.$

$$Develop a user

$-$

friendly interface with Jinja

$2$

$$templates$.$

b

$.$

$$Ensure forms and tables are properly formatted and styled.

$$

$$Database Context: Network Device Management

o Database Name: network

$\_$

management

$\_$

db

$$

$$Tables and Descriptions:

o Table: devices

$$

$$Description: Contains details about network devices managed by the system.

$$

$$Fields:

$$

$$device

$\_$

id

$($

Primary Key, Integer, Auto Increment

$)$

$$

$$device

$\_$

name

$($

Varchar

$)$

$$

$$ip

$\_$

address

$($

Varchar

$,$

$$Unique

$)$

$$

$$device

$\_$

type

$($

Varchar

$)$

$$

$$e

$.$

g

$.$

$,$

$$Router$,$ Switch, Firewall

$$

$$location

$($

Varchar

$)$

$$

$$status

$($

Varchar

$)$

$$

$$e

$.$

g

$.$

$,$

$$Active$,$ Inactive, Under Maintenance

o Table: network

$\_$

logs

$$

$$Description: Stores logs of network events or changes.

$$

$$Fields:

$$

$$log

$\_$

id

$($

Primary Key, Integer, Auto Increment

$)$

$$

$$device

$\_$

id

$($

Integer

$,$

$$Foreign Key to devices.device

$\_$

id

$)$

$$

$$timestamp

$($

DateTime

$)$

$$

$$log

$\_$

description

$($

Text

$)$

o Table: maintenance

$\_$

records

$$

$$Description: Records maintenance activities for each device.

$$

$$Fields:

$$

$$record

$\_$

id

$($

Primary Key, Integer, Auto Increment

$)$

$$

$$device

$\_$

id

$($

Integer

$,$

$$Foreign Key to devices.device

$\_$

id

$)$

$$

$$maintenance

$\_$

date

$($

Date

$)$

$$

$$details

$($

Text

$)$

$$

$$technician

$\_$

name

$($

Varchar

$)$

o Table: users

o Description: Contains user accounts who can access or modify the network device data.

o Fields:

$$

$$user

$\_$

id

$($

Primary Key, Integer, Auto Increment

$)$

$$

$$username

$($

Varchar

$,$

$$Unique

$)$

$$

$$password

$\_$

hash

$($

Varchar

$)$

$$

$$role

$($

Varchar

$)$

$$

$$e

$.$

g

$.$

$,$

$$Admin$,$ Technician, Viewer

$$

$$email

$($

Varchar

$,$

$$Unique

$)$

o Relationships:

$$

$$devices to network

$\_$

logs: One

$-$

to

$-$

Many

$($

One device can have multiple logs

$)$

$$

$$devices to maintenance

$\_$

records: One

$-$

to

$-$

Many

$($

One device can have multiple maintenance records

$)$

$$

$$CRUD Operations Examples:

o Create: Add new network devices, log entries, maintenance records, or users.

o Read: View device details, logs

$,$

$$maintenance history, or user profiles.

o Update: Modify device information, update maintenance records, or change user roles.

o Delete: Remove devices, logs

$,$

$$maintenance records, or user accounts.

$($

$1$

$0$

$$marks

$)$

Evaluation Criteria:

Fu