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.

$(10$ marks$)$

Evaluation Criteria:

Functionality: All features must work correctly with the MySQL database.

$40\%$

Database Integration: Effective use of Flask$-$SQLAlchemy and Flask$-$Migrate with MySQL $20\%$

Front$-$End: All forms and pages

$20\%$

Code Quality: Code should be clean, well$-$organized, and commented, following Python standards.

$10\%$

Friendliness of UI: The web interface should be intuitive and user$-$friendly. $10\%$

Total $100\%$

You must name your VS or PyCharm Code project according to the following rule:

yourfullname$\_$SWS$212$Labnumber$\_$Exercisenumber.

Example: JohnSmith$\_$SWS$212$Lab$2\_$Ex$1$

Submission rules:

Submit the VS Code project named according to the following rule:

yourfullname$\_$SWS$212$Labnumber$\_$Exercisenumber.zip

Example: JohnSmith$\_$SWS$212$Lab$2\_$Ex$1.$zip