-
Notifications
You must be signed in to change notification settings - Fork 84
Tri 3: Tech Talk Week 8: Markdown Notes using SQL Database
suneelanaidu edited this page May 12, 2022
·
11 revisions
Persistent data for a user is a big key to any Web Application. This tech talk is focused on building notes data that is entered by the user of the application. Input supports markdown and html conversion, allowing many rich features like tables and pictures. In fact, you can cut and paste any of your Time Box or Wiki's directly into the notes for testing. FYI, <image ... links would need to be according to references of file on the Internet.
- Learn about SQL, persistent data, and One-to-many database relationships.
- Learn all the supporting frontend and backend features to work with related tables.
- Perform Hack to add Update and Delete CRUD to this project.
- PBL requirement is to have One-to-many persistent tables in your project.
This project is a build up of many things we have done before. The highlights of things that are new: Markdown supported Notes, Login required, SQL, One-to-many relationships. The code continues to focus on the MVC paradigm of coding.
Markdown or HTML notes are a key feature of the input. Input is all targeted to a logged in user.
- HTML use of user.name from login for identification.
<h2>Notes for {{ user.name }}</h2>
<!-- or <h2> Notes for {{ current_user.name }} </h2> -->- HTML use of textarea for multiple line input
<tr>
<td><textarea rows = "5" cols = "100%" name = "notes" id="notes" required></textarea></td>
<td><input type="submit" value="Add" ></td>
</tr>- Jinja2 use of for loop to display all notes for user
<tbody>
{% for note in notes %}
<tr>
<td>{{ note.note | safe }}</td>
</tr>
{% endfor %}
</tbody>Loading pages and python CRUD to save and retrieve data are keys to the Notes application.
- Python Routes require user login, html is constructed based off of user and a list of their personal notes.
@app_notes.route('/notes')
@login_required
def notes():
### see complete details below
return render_template('notes.html', user=user, notes=list_notes)- Dependency on Python Markdown converts Markdown syntax into HTML.
import markdown - Python CRUD to Read notes from user object (uo) and convert each note from text/markdown to HTML. This is a One-to-Many relationship of the Notes for the current/logged in User.
user = uo.read() # extract user record (Dictionary)
for note in uo.notes: # loop through each user note
note = note.read() # extract note record (Dictionary)
note['note'] = markdown.markdown(note['note']) # convert text, markdown, etc to html
list_notes.append(note) # prepare note list for render_template- SQL Alchemy contains a ForeignKey. This is a foundation SQL and establish relationships between tables. In this case one User has many Notes. The relationship is called One-to-many. Whenever you have this type of relationship a new Table is required.
The relationship between the Users table and the new table Notes is define as follows:
db.relationship("Notes", Cascade='all, delete', backref='users', lazy=True) #lazy defines when SQLAlchemy will load the data
class Users(UserMixin, db.Model):
__tablename__ = 'users'
# Define the Users schema
userID = db.Column(db.Integer, primary_key=True)
#### see below for full definition
# Defines a relationship between User record and Notes table, one-to-many (one user to many notes)
notes = db.relationship("Notes", cascade='all, delete', backref='users', lazy=True) class Notes(db.Model):
__tablename__ = 'notes'
#### see below for full definition
# Defines ForeignKey, in this case userID is the one who created the note
userID = db.Column(db.Integer, db.ForeignKey('users.userID'))""" database dependencies to support Users db examples """
from random import randrange
from __init__ import db
from sqlalchemy.exc import IntegrityError
from werkzeug.security import generate_password_hash, check_password_hash
from flask_login import UserMixin
''' Tutorial: https://www.sqlalchemy.org/library.html#tutorials, try to get into Python shell and follow along '''
# Define the 'Users Notes' table with a relationship to Users within the model
# Relationships are expressed with the relationship() function. However the foreign key has to be separately
declared with the ForeignKey class.
# A Foreign key is a column that creates a relationship between two tables. The purpose of the Foreign key is
to maintain data integrity and allow navigation between two different instances of an entity. It acts as a
cross-reference between two tables as it references the primary key of another table(Users in our case).
Every relationship in the database should be supported by a foreign key.
class Notes(db.Model):
__tablename__ = 'notes'
# Define the Notes schema
id = db.Column(db.Integer, primary_key=True)
note = db.Column(db.Text, unique=False, nullable=False)
# Define a relationship in Notes Schema to userID who originates the note, many-to-one (many notes to one user)
userID = db.Column(db.Integer, db.ForeignKey('users.userID'))
# Constructor of a Notes object, initializes of instance variables within object
def __init__(self, note, userID):
self.note = note
self.userID = userID
# Returns a string representation of the Notes object, similar to java toString()
# returns string
def __repr__(self):
return "Notes(" + str(self.id) + "," + self.note + "," + str(self.userID) + ")"
# CRUD create, adds a new record to the Notes table
# returns the object added or None in case of an error
def create(self):
try:
# creates a Notes object from Notes(db.Model) class, passes initializers
db.session.add(self) # add prepares to persist person object to Notes table
db.session.commit() # SqlAlchemy "unit of work pattern" requires a manual commit
return self
except IntegrityError:
db.session.remove()
return None
# CRUD read, returns dictionary representation of Notes object
# returns dictionary
def read(self):
return {
"id": self.id,
"note": self.note,
"userID": self.userID
}
# Define the Users table within the model
# -- Object Relational Mapping (ORM) is the key concept of SQLAlchemy
# -- a.) db.Model is like an inner layer of the onion in ORM
# -- b.) Users represents data we want to store, something that is built on db.Model
# -- c.) SQLAlchemy ORM is layer on top of SQLAlchemy Core, then SQLAlchemy engine, SQL
class Users(UserMixin, db.Model):
__tablename__ = 'users'
# Define the Users schema
userID = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), unique=False, nullable=False)
email = db.Column(db.String(255), unique=True, nullable=False)
password = db.Column(db.String(255), unique=False, nullable=False)
phone = db.Column(db.String(255), unique=False, nullable=False)
# Defines a relationship between User record and Notes table, one-to-many (one user to many notes)
notes = db.relationship("Notes", cascade='all, delete', backref='users', lazy=True)#lazy defines when
SQLAlchemy will load the data from the database: True (which is the default) means that SQLAlchemy
will load the data as necessary in one go using a standard 'select' statement. import markdown
from flask import Blueprint, render_template, request, redirect, url_for
from flask_login import login_required, current_user
from cruddy.query import user_by_id
from cruddy.model import Notes
# blueprint defaults https://flask.palletsprojects.com/en/2.0.x/api/#blueprint-objects
app_notes = Blueprint('notes', __name__,
url_prefix='/notes',
template_folder='templates/notey/',
static_folder='static',
static_url_path='static')
@app_notes.route('/notes')
@login_required
def notes():
# defaults are empty, in case user data not found
user = ""
list_notes = []
# grab user database object based on current login
uo = user_by_id(current_user.userID)
# if user object is found
if uo is not None:
user = uo.read() # extract user record (Dictionary)
for note in uo.notes: # loop through each user note
note = note.read() # extract note record (Dictionary)
note['note'] = markdown.markdown(note['note']) # convert markdown to html
list_notes.append(note) # prepare note list for render_template
if list_notes is not None:
list_notes.reverse()
# render user and note data in reverse chronological order(display latest notes rec on top)
return render_template('notes.html', user=user, notes=list_notes)
# Notes create/add
@app_notes.route('/create/', methods=["POST"])
@login_required
def create():
"""gets data from form and add to Notes table"""
if request.form:
# construct a Notes object
note_object = Notes(
request.form.get("notes"), current_user.userID
)
# create a record in the Notes table with the Notes object
note_object.create()
return redirect(url_for('notes.notes')){% extends 'layout.html' %}
{% block title %}
<title>Notes</title>
{% endblock %}
{% block content %}
<div class="container py-4">
<header class="pb-3 mb-4 border-bottom border-primary text-dark">
<span class="fs-4">SQL Notes Page</span>
</header>
</div>
<div class="container py-4 text-light bg-success">
<div class="container bg-secondary py-4">
<div class="p-5 mb-4 bg-light text-dark rounded-3">
<h2>Notes for {{ user.name }}</h2>
<hr>
<!--Notes input form, textarea supports multiple lines of input -->
<form method="POST" ID="create" action={{url_for('notes.create')}}>
<table class="table">
<thead>
<tr>
<th><label for="notes">Create a new note:</label></th>
</tr>
</thead>
<tbody>
<tr>
<td><textarea rows = "5" cols = "100%" name = "notes" id="notes" required></textarea></td>
<td><input type="submit" value="Add" ></td>
</tr>
</tbody>
</table>
</form>
<hr>
<!--Notes output area, Jinja lines iterate through notes,
note.note is html converted from markdown -->
<div class="table-responsive">
<table class="table">
<tbody>
{% for note in notes %}
<tr>
<td>{{ note.note | safe }}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
</div>
</div>
{% endblock %}