COMP0034 2023-24 Week 2 coding activities
This assumes you have already forked the coursework repository and cloned the resulting repository to your IDE.
- Create and activate a virtual environment
- Install the requirements
pip install -r requirements.txt
- Run the app
flask --app paralympics run --debug
- Open a browser and go to https://127.0.0.1:5000
- Try it again with https://127.0.0.1:5000/name (replace name with your name)
- You should see the variable route for the homepage (the final activity from last week)
- Stop the app using
CTRL+C
Consider installing the VS Code extension SQLite Viewer to allow you to view the content of a database through the VS Code interface.
If you are using PyCharm Professional then you can already view database files. You cannot do this in PyCharm Community edition which is why Professional is recommended.
When you install from requirements.txt this included Flask-SQLAlchemy. The SQLAlchemy package will also be installed as it is a dependency for Flask-SQLAlchemy. Together they provide functionality that lets you more easily create Python classes that map to database tables; and handles the database interaction, i.e. SQL queries, using Python functions. This follows a design pattern called ORM, Object Relational Mapper. An ORM encapsulates, or wraps, data stored in a database into an object that can be used in Python.
Flask-SQLAlchemy works with many database formats but will not work directly with .csv/.xlsx file. You will use a SQLite database which stores the tables and data in a single file which is convenient for the coursework.
Create a function that allows you to create a Flask app and then enable that app to use extensions such as Flask-SQLAlchemy and to add configuration parameters.
This is an application factory pattern. Like a factory production line, you create the app, then you pass it along a production line adding extra 'features' to it as needed.
-
Open
paralympics/__init__.py
-
The following is based on the
create_app()
function from the Flask tutorial:import os from flask import Flask def create_app(test_config=None): # create the Flask app app = Flask(__name__, instance_relative_config=True) # configure the Flask app (see later notes on how to generate your own SECRET_KEY) app.config.from_mapping( SECRET_KEY='dev', # Set the location of the database file called paralympics.sqlite which will be in the app's instance folder SQLALCHEMY_DATABASE_URI= "sqlite:https:///" + os.path.join(app.instance_path, 'paralympics.sqlite'), ) if test_config is None: # load the instance config, if it exists, when not testing app.config.from_pyfile('config.py', silent=True) else: # load the test config if passed in app.config.from_mapping(test_config) # ensure the instance folder exists try: os.makedirs(app.instance_path) except OSError: pass return app
-
Create your own unique
SECRET_KEY
.SECRET_KEY
is used by Flask and extensions to keep data safe. It’s set to 'dev' to provide a convenient value during development, but it should be overridden with a random value when deploying.SQLALCHEMY_DATABASE_URI
is the path where the SQLite database file will be saved. It's under app.instance_path, which is the path that Flask has chosen for the instance folder.You can generate a secret key from the Terminal command line. Type
python3
orpython
and press enter. At the>>>
prompt typeimport secrets
and press enter. Then typesecrets.token_urlsafe(16)
and press enter. You should see a string of 16 characters. Copy this and use it to replace the word 'dev' in the SECRET_KEY line in thecreate_app()
function. -
Now that the app is created in the
create_ap()
function, you need to modifyparalympics.py
app to use this.Use the Flask
current_app
object to access the configured app.Replace the contents on
paralympics.py
with the following:from flask import current_app as app @app.route('/') def hello(): return f"Hello!"
-
Return to the
create_app()
function and now let the app know about the routes that are defined inparalympics.py
.# Put the following code inside the create_app function after the code to ensure the instance folder exists # This lis likely to be circa line 40. with app.app_context(): # Register the routes with the app in the context from paralympics import paralympics
NB: Consider renaming
paralympics.py
to ``routes.py orcontrollers.py
to avoid confusion between the paralympics package and the paralympics module within that package. -
Check that you can run the app
flask --app paralympics run --debug
. Flask recognises thecreate_app()
function.
Return to __init__.py
and add the following code to before the create_app()
function to initialise the SQLAlchemy
object.
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase
class Base(DeclarativeBase):
pass
db = SQLAlchemy(model_class=Base)
Create a python file called models.py
. This will contain classes that map to your database tables. Create a python
file. This is often named models.py
but doesn't have to be.
The syntax for a class that maps to a database table is given in the Flask-SQLAlchemy documentation . The table is defined as follows:
- Define the class with an appropriate name.
- The tablename should match the tablename in the database.
- The column names should match the column names used in the database.
- The column datatypes should match the data types used in the database.
- The classes inherit the Flask-SQLAlchemy Model class. This automatically gives the class access to functions that will
handle the constructor so you don't need to define it. You can access the instance of SQLAlchemy, called
db
, that you just created in__init__.py
.
At some point the paralympics app will have authentication and so needs a table to hold user details. Add the following
class to models.py
:
from sqlalchemy import Integer, String
from sqlalchemy.orm import Mapped, mapped_column
from paralympics import db
class User(db.Model):
id: Mapped[int] = mapped_column(db.Integer, primary_key=True)
email: Mapped[str] = mapped_column(db.Text, unique=True, nullable=False)
password: Mapped[str] = mapped_column(db.Text, unique=True, nullable=False)
Add the following code to create two classes that represents the tables in the database, Region and Event.
# Adapted from https://flask-sqlalchemy.palletsprojects.com/en/3.1.x/quickstart/#define-models
from typing import List
from sqlalchemy import Integer, String, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from paralympics import db
# This uses the latest syntax for SQLAlchemy, older tutorials will show different syntax
# SQLAlchemy provide an __init__ method for each model, so you do not need to declare this in your code
class Region(db.Model):
__tablename__ = "region"
NOC: Mapped[str] = mapped_column(db.Text, primary_key=True)
region: Mapped[str] = mapped_column(db.Text, nullable=False)
notes: Mapped[str] = mapped_column(db.Text, nullable=True)
# one-to-many relationship with Event, the relationship in Event is called 'region'
# https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#one-to-many
events: Mapped[List["Event"]] = relationship(back_populates="region")
class Event(db.Model):
__tablename__ = "event"
id: Mapped[int] = mapped_column(db.Integer, primary_key=True)
type: Mapped[str] = mapped_column(db.Text, nullable=False)
year: Mapped[int] = mapped_column(db.Integer, nullable=False)
country: Mapped[str] = mapped_column(db.Text, nullable=False)
host: Mapped[str] = mapped_column(db.Text, nullable=False)
NOC: Mapped[str] = mapped_column(ForeignKey("region.NOC"))
# add relationship to the parent table, Region, which has a relationship called 'events'
region: Mapped["Region"] = relationship("Region", back_populates="events")
start: Mapped[str] = mapped_column(db.Text, nullable=True)
end: Mapped[str] = mapped_column(db.Text, nullable=True)
duration: Mapped[int] = mapped_column(db.Integer, nullable=True)
disabilities_included: Mapped[str] = mapped_column(db.Text, nullable=True)
countries: Mapped[str] = mapped_column(db.Text, nullable=True)
events: Mapped[int] = mapped_column(db.Integer, nullable=True)
athletes: Mapped[int] = mapped_column(db.Integer, nullable=True)
sports: Mapped[int] = mapped_column(db.Integer, nullable=True)
participants_m: Mapped[int] = mapped_column(db.Integer, nullable=True)
participants_f: Mapped[int] = mapped_column(db.Integer, nullable=True)
participants: Mapped[int] = mapped_column(db.Integer, nullable=True)
highlights: Mapped[str] = mapped_column(db.Text, nullable=True)
class User(db.Model):
id: Mapped[int] = mapped_column(db.Integer, primary_key=True)
email: Mapped[str] = mapped_column(db.Text, unique=True, nullable=False)
password: Mapped[str] = mapped_column(db.Text, unique=True, nullable=False)
def __init__(self, email: str, password: str):
"""
Create a new User object using hashing the plain text password.
:type password_string: str
:type email: str
:returns None
"""
self.email = email
self.password = password
The relationship between the two tables is defined used the primary and foreign keys with the
relationship` function
as follows:
from typing import List
from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from paralympics import db
# non-Key/relations`hip column details have been omitted from the classes below for brevity
# one-to-many relationship from Region to Event
# https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#one-to-many
class Region(db.Model):
__tablename__ = "region"
# Primary key attribute
NOC: Mapped[str] = mapped_column(db.Text, primary_key=True)
# Add a relationship to Event. The Region then has a record of the Events associated with it.
# This references the relationship 'region' in the Event table.
events: Mapped[List["Event"]] = relationship(back_populates="region")
class Event(db.Model):
__tablename__ = "event"
# add ForeignKey that maps to the primary key of the Region table
NOC: Mapped[str] = mapped_column(ForeignKey("region.NOC"))
# add relationship to Region, this references the relationship 'events' that is in the Region table
region: Mapped["Region"] = relationship(back_populates="events")
Add a line of code to the init.py in the paralympic_app package to import the models. To avoid circular imports, put this after the app is created; so NOT at the top of the file where you would usually place imports.
If you are using a linter you will need to ignore the warnings about placing the import at the top of the file.
To create the tables for User, Region and Event in the database use a Flask-SQLAlchemy function db.create_all()
. This
will create the tables if they do not already exist. Add this line after importing the models.
def create_app(test_config=None):
app = Flask(__name__, instance_relative_config=True)
app.config.from_mapping(
SECRET_KEY='l-tirPCf1S44mWAGoWqWlA',
SQLALCHEMY_DATABASE_URI="sqlite:https:///" + os.path.join(app.instance_path, 'paralympics.sqlite'),
)
if test_config is None:
app.config.from_pyfile('config.py', silent=True)
else:
app.config.from_mapping(test_config)
try:
os.makedirs(app.instance_path)
except OSError:
pass
# Initialise Flask with the SQLAlchemy database extension
db.init_app(app)
# Models are defined in the models module, so you must import them before calling create_all, otherwise SQLAlchemy
# will not know about them.
from paralympics.models import User, Region, Event
# Create the tables in the database
# create_all does not update tables if they are already in the database.
with app.app_context():
db.create_all()
# Register the routes with the app in the context
from paralympics import paralympics
return app
Run the app flask --app paralympics run --debug
.
As the database does not exist it will be created. You can check this by looking in the instance folder. You should see
a file called paralympics.sqlite
.
There are many ways to add data to a database using Python.
This method assumes you created database as above and are then going to add the data from the .csv files to the existing tables using SQLAlchemy. The code will be called every time the app runs.
- Add the following code to the end of
__init__.py
:
import csv
from pathlib import Path
def add_data_from_csv():
"""Adds data to the database if it does not already exist."""
# Add import here and not at the top of the file to avoid circular import issues
from paralympics.models import Region, Event
# If there are no regions in the database, then add them
first_region = db.session.execute(db.select(Region)).first()
if not first_region:
print("Start adding region data to the database")
noc_file = Path(__file__).parent.parent.joinpath("data", "noc_regions.csv")
with open(noc_file, 'r') as file:
csv_reader = csv.reader(file)
next(csv_reader) # Skip header row
for row in csv_reader:
# row[0] is the first column, row[1] is the second column
r = Region(NOC=row[0], region=row[1], notes=row[2])
db.session.add(r)
db.session.commit()
# If there are no Events, then add them
first_event = db.session.execute(db.select(Event)).first()
if not first_event:
print("Start adding event data to the database")
event_file = Path(__file__).parent.parent.joinpath("data", "paralympic_events.csv")
with open(event_file, 'r') as file:
csv_reader = csv.reader(file)
next(csv_reader) # Skip header row
for row in csv_reader:
# row[0] is the first column, row[1] is the second column etc
# For int data types, if there is no value, set to None rather than ''
e = Event(type=row[0],
year=row[1],
country=row[2],
host=row[3],
NOC=row[4],
start=row[5],
end=row[6],
duration=row[7] or None,
disabilities_included=row[8],
countries=row[9] or None,
events=row[10] or None,
sports=row[11] or None,
participants_m=row[12] or None,
participants_f=row[13] or None,
participants=row[14] or None,
highlights=row[15])
db.session.add(e)
db.session.commit()
- Update the
create_app()
function to call theadd_data_from_csv()
function after the tables are created
def create_app(test_config=None):
# ... CODE OMITTED FOR BREVITY HERE ...
with app.app_context():
# Create the database and tables if they don't already exist
db.create_all()
# Add the data to the database if not already added
add_data_from_csv()
# ... CODE OMITTED FOR BREVITY HERE ...
return app
Another approach would be to create the database using Python code as a one-off action. This may be preferable if there is a lot of data to load and the code takes a while to execute.
The file data\create_db_add_data
contains an example of this approach. You do not need this for this activity, it is
included in case you want to take this approach for your coursework.
These are not the only options; you will find blog posts and tutorials that offer other approaches that you could use instead.
There are many aspects not covered in this tutorial that you could investigate.
- Track database changes: If you change a model's columns, use a migration library like Alembic with Flask-Alembic or Flask-Migrate to generate migrations that update the database schema.
- Alternative Python class definitions using Python Dataclasses
with SQLAlchemy
MappedAsDataclass
) - Reflecting tables can be used if you have a database with the data already in.
- Python sqlite3 tutorial may be useful if you create the database and add data separately from the Flask application code.