MAD I Summary Session Week 5

Main Ideas and Lessons

• Purpose of the session: The speaker (ma’am) couldn’t attend the original Week 5 session, so they shared recordings/YouTube links from the instructor’s Week 5 content and then summarized what was covered.

Course progression recap (context)

• Week 1–3: Basic app + HTML/CSS + templating (dynamic pages).
• Week 4: Flask basics and using render_template to show templates with data.
• Week 5: Introduces the database/model layer and how to integrate it with Flask using MVC concepts.

Client–server & MVC/MVP mental model

• The browser acts as the client; app.py runs as the server.
• Routes/endpoints map URLs → functions/business logic.
• Templates are the views.
• Database tables are the model.
• Controller code in app.py contains business logic.

Why ORM (Object Relational Mapper) is used

• SQLAlchemy/ORM lets developers write Pythonic class/object code instead of raw SQL.
• ORM converts class definitions/queries into table/CRUD operations.

Key conceptual mapping:

• Python class → database table
• Class attributes → table columns
• Class instances/objects → table rows/records

CRUD operations (with SQLAlchemy + Flask context)

• Create schema, then perform Create/Read/Update/Delete via ORM.
• Important practical detail: When creating tables in the Python shell, you must manage application context using:

python app.app_context().push()

This prevents the error: “working outside of application context”.

Relationships in databases

One-to-many (parent–child): Role → Users

• A role can have multiple users.
• Each user belongs to only one role.
• Implemented using a foreign key in the child table (e.g., user.role_id).
• Also uses db.relationship(...) for convenient navigation, including “backref” behavior (shortcuts between parent/child objects).

Many-to-many (e.g., Users ↔ Roles)

• Direct foreign keys can create “dependency contradiction” (both sides should be independent).
• Solved using an association table (join table) storing pairs of foreign keys.
• Implemented with an additional model like Association containing:
  • foreign key to User
  • foreign key to Role
  • relationship configuration using ORM secondary=...

Access patterns:

• user.roles → list of roles
• role.users → list of users
• Appending to relationship collections creates association entries.

Integrating ORM into Flask routes and templates

• Instead of CRUD only in Python shell, define Flask endpoints:
  • Display lists (READ) and render HTML tables using Jinja2 loops
  • Create forms for CREATE/UPDATE
  • Use dynamic URLs + converters (e.g., <int:id>) for UPDATE/DELETE specific records

Jinja2 features used:

• for loops to render table rows
• loop.index for serial numbers (keeps numbering sequential even if primary keys have gaps)
• Conditional rendering:
  • show “No users found…” if the list is empty

---

Methodology / Instructions (Detailed)

A) Week 5 database setup using Flask + ORM (SQLAlchemy)

• Install dependencies:

  • pip install flask_sqlalchemy (speaker wording: “flasksql”)

• Initialize ORM in Flask app:

  • Import ORM (speaker wording approximate):
    • from ... import SQLAlchemy
  • Create app object (implied from earlier weeks):
    • app = Flask(__name__)
  • Create DB/ORM object:
    • db = SQLAlchemy(app) (or bind DB then attach to app)
  • Configure database URI:
    • app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///your_db_name.sqlite3'

• Application context requirement (very important for shell commands):

  • When running commands in Python shell (like db.create_all()), ensure:
    • app.app_context().push()
  • Without pushing context, db.create_all() can raise:
    • “working outside of application context”

---

B) Define models (classes) to create tables

• Create a model class for each table, e.g.:

  • class User(db.Model):

• Map table columns from class attributes:

  • id = db.Column(db.Integer, primary_key=True, ...)
  • username = db.Column(db.String, nullable=False, unique=True, ...)
  • password = db.Column(db.String, nullable=False, ...)

• Rules described:

  • Primary key:
    • auto-incremented integer
    • not nullable
  • Unique constraint:
    • e.g., username or role_name

---

C) Create tables / schema

• In Python shell:
  • from app import *
• Push context:
  • app.app_context().push()
• Create schema:
  • db.create_all()

---

D) Perform CRUD operations in Python shell

1) Create (C)

• Create an instance of the model:
  • user_1 = User(username=..., password=...)
• Add to session:
  • db.session.add(user_1)
• Commit:
  • db.session.commit()

2) Read (R)

• If you know primary key:
  • user = User.query.get(<id>)
• If filtering by a column:
  • users = User.query.filter_by(username=<value>).all()
  • or:
    • user = User.query.filter_by(username=<value>).first()

Data types emphasized:

• .get() / .first() → single object
• .all() → list of objects

3) Update (U)

• Retrieve object first
• Modify attribute:
  • user.password = <new value>
• Commit:
  • db.session.commit()

4) Delete (D)

• Retrieve target object via .first()
• Delete:
  • db.session.delete(user_obj)
• Commit:
  • db.session.commit() (implied)

---

Relationship Methodologies

E) One-to-many: Role → Users

Semantics

• One role can have many users.
• Each user belongs to exactly one role.

Implementation steps

• Create Role model (parent):
  • class Role(db.Model):
  • columns: id, role_name
• Update User model (child) with a foreign key:
  • role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False)

ORM relationship for navigation

• In Role:
  • users = db.relationship('User', backref=...) (speaker explains backref)
• In User:
  • access role using backref:
    • user.role

---

F) Many-to-many: Users ↔ Roles via Association table

Example semantics

• One user can have multiple roles.
• One role can have multiple users.

Why association table is needed

• Direct foreign keys can create circular dependency/conflicting “parent/child” logic.

Implementation steps

• Keep User and Role independent (no direct role_id in user for this case).
• Create Association model (join table):
  • class Association(db.Model):
  • foreign keys:
    • user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    • role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False)

Configure relationships using secondary

• In User:
  • roles = db.relationship('Role', secondary=Association, backref='users')
• In Role:
  • access role.users via the backref

Populate relationships

• Either:
  • create association entries explicitly (Association(...))
• Or use ORM collection operations:
  • role_obj.users.append(user_obj)
  • user_obj.roles.append(role_obj)
• Persist changes:
  • db.session.commit()

Behavior when association is empty

• user.roles / role.users returns an empty list until association rows exist.
• Relationship exists logically, but entries depend on association table data.

---

Flask Integration Methodology (Routes + Templates)

G) Structure: models in models.py, app in app.py

• Move models to models.py:
  • define DB = ..., models like User, Role, Association
• In app.py:
  • import DB and models
  • ensure binding between app and DB:
    • db.init_app(app)
• Set DB filename/URI and context bridging as needed.

---

H) Implement endpoints and templates for CRUD

1) READ list endpoint

• Query all objects:
  • roles = Role.query.all()
• Render template:
  • return render_template('index.html', roles=roles)
• In index.html:
  • loop through roles
  • display role.id, role.role_name
  • use loop.index for serial numbering

2) UPDATE endpoint using dynamic URL

• In HTML:
  • edit link:
    • href="/update_role/<role.id>"
• In Flask:
  • route with converter:
    • /update_role/<int:id>
• In update form:
  • method POST
  • include input fields (speaker’s example: name="ro_name")
  • pre-fill with current object values
• On POST:
  • fetch object by id
  • update fields from request.form[...]
  • commit
  • redirect back to base URL

3) DELETE endpoint

• In HTML:
  • delete link:
    • href="/delete_role/<role.id>"
• In Flask:
  • define delete route (speaker mentions often using GET for simplicity)
  • fetch object, delete, redirect back

4) CREATE endpoint

• Route renders a create form:
  • method POST
  • include input field(s) like role_name
• On submit:
  • create model instance using request.form.get(...)
  • db.session.add(...)
  • db.session.commit()
  • redirect back

---

I) Access related records in templates (using relationships)

• From a Role page click “administrator” (example navigation):
  • route like /users/<role_name> (conceptually; could be <id> depending on implementation)
• In the route:
  • fetch the Role object
  • use relationship:
    • users = role_object.users
  • render role_users.html
• In template:
  • loop through users
  • conditional rendering:
    • if list is empty → “No users found…”

---

Speakers / Sources Featured

• Primary speaker (in video): “ma’am” (session host/summarizer; name not provided in subtitles)
• Other speaker referenced: “sir” (instructor who previously delivered Week 5; name not provided in subtitles)