PROGRAMMING FOR THE WEB: SQLITE
TASK 4 - OBJECT-RELATIONAL MAPPING

Introduction to Object-Relational Mapping (ORM)

Before we dive into the activities, let's understand what an ORM is and why it's useful.

Object-Relational Mapping (ORM) is a programming technique that allows developers to interact with a database using object-oriented programming languages. Instead of writing raw SQL queries, you work with Python objects that represent database tables and records.

Key benefits of using an ORM:

Abstraction: ORMs abstract away the database system, allowing you to switch between different databases with minimal code changes.
Security: ORMs often provide built-in protection against SQL injection attacks.
DRY (Don't Repeat Yourself): ORMs reduce boilerplate code for common database operations.
Object-Oriented Approach: Database tables are represented as classes, and rows as instances of those classes, aligning well with OOP principles.

Comparing ORM to raw SQL:

Instead of CREATE TABLE movies (...), we define a Movie class.
Instead of INSERT INTO movies VALUES (...), we create and save a Movie object.
Instead of SELECT * FROM movies WHERE..., we use methods like Movie.query.filter(...).

ORMs relate closely to OOP concepts you've learned:

Database tables become Python classes (like creating a blueprint).
Table rows become instances of these classes (like creating objects from the blueprint).
Table columns become attributes of the class.

Now, let's see how this works in practice with Flask-SQLAlchemy!

Activity 1 - Setting Up Flask-SQLAlchemy

In this activity, we will set up Flask-SQLAlchemy in our existing Flask application. We'll see how our database structure is defined using a Python class instead of SQL commands.

app.py

from flask import Flask, render_template, request, redirect, url_for

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///movies.db'

db = SQLAlchemy(app)

class Movie(db.Model):

id = db.Column(db.Integer, primary_key=True)

title = db.Column(db.String(100), nullable=False)

director = db.Column(db.String(100))

year = db.Column(db.Integer)

rating = db.Column(db.Float)

with app.app_context():

db.create_all()

@app.route('/')

def index():

movies = Movie.query.all()

return render_template('index.html', movies=movies)

if __name__ == '__main__':

app.run(debug=True)

❓ Explanation:

Instead of writing an SQL CREATE TABLE statement, we define a Movie class that inherits from db.Model.
Each class attribute represents a column in our database.
This approach aligns with OOP principles, treating each movie as an object with properties.

🚀 Try It Out:

Install Flask-SQLAlchemy using pip: pip install flask-sqlalchemy
Replace your existing app.py with this new code. index.html can remain the same as last lesson!
Perhaps delete or rename your old movies.db file.
Run the Flask application and visit http://localhost:5000

Activity 2 - Adding Movies with Flask-SQLAlchemy

In this activity, we will create a route to add new movies using our Movie model. We'll see how creating database entries becomes as simple as creating Python objects.

@app.route('/add', methods=['GET', 'POST'])

def add_movie():

if request.method == 'POST':

new_movie = Movie(

title=request.form['title'],

director=request.form['director'],

year=int(request.form['year']),

rating=float(request.form['rating'])

)

db.session.add(new_movie)

db.session.commit()

return redirect(url_for('index'))

return render_template('add.html')

❓ Explanation:

Instead of constructing an SQL INSERT statement, we create a Movie object and add it to the session. This object-oriented approach is more intuitive and less error-prone.

Compare this to our previous SQL approach:

cursor.execute('''

INSERT INTO movies (title, director, year, rating)

VALUES (?, ?, ?, ?)

''', (title, director, year, rating))

The ORM method is more Pythonic and provides better type safety.

🚀 Try It Out:

Add this new route to your app.py file
Once again, just use add.html from the previous lesson
Visit http://localhost:5000/add and try adding a new movie
Check if the new movie appears on the main page
Try adding a movie with invalid data (e.g., non-numeric year). Notice how the ORM handles type conversions and validation.

Activity 3 - Updating Movies with Flask-SQLAlchemy

In this activity, we will create a route to update existing movies and add a new template for editing. This will demonstrate how updating database entries is as simple as modifying Python object attributes.

app.py

@app.route('/edit/<int:id>', methods=['GET', 'POST'])

def edit_movie(id):

movie = Movie.query.get_or_404(id)

if request.method == 'POST':

movie.title = request.form['title']

movie.director = request.form['director']

movie.year = int(request.form['year'])

movie.rating = float(request.form['rating'])

db.session.commit()

return redirect(url_for('index'))

return render_template('edit.html', movie=movie)

edit.html

<!DOCTYPE html>

<head>

<title>Edit Movie</title>

</head>

<body>

<h1>Edit Movie</h1>

<label for="title">Title:</label>

<label for="director">Director:</label>

<label for="rating">Rating:</label>

</form>

<a href="{{ url_for('index') }}">Back to Movie List</a>

</body>

</html>

Lastly, we need to add edit links to our index.html file. Add this line within the loop that displays the movies:

❓ Explanation:

Instead of writing an SQL UPDATE statement, we simply modify the attributes of our Movie object. This approach is more intuitive and mirrors how we would update any Python object's attributes.

Compare this to our previous SQL approach:

cursor.execute('''

UPDATE movies

SET title = ?, director = ?, year = ?, rating = ?

WHERE id = ?

''', (title, director, year, rating, id))

The ORM method feels more natural in Python and reduces the risk of SQL syntax errors.

🚀 Try It Out:

Add this new route to your app.py file
Add the edit links to your index.html file
Try editing a movie and see if the changes are reflected on the main page
Observe how the ORM handles the retrieval and updating of the movie object

Activity 4 - Deleting Movies with Flask-SQLAlchemy

In this activity, we will create a route to delete movies from our database. We'll see how removing database entries is as simple as removing Python objects.

@app.route('/delete/<int:id>')

def delete_movie(id):

movie = Movie.query.get_or_404(id)

db.session.delete(movie)

db.session.commit()

return redirect(url_for('index'))

❓ Explanation:

Instead of writing an SQL DELETE statement, we simply remove the Movie object from the session. This approach aligns well with how we would remove items from a Python list or dictionary.

Compare this to our previous SQL approach:

cursor.execute('DELETE FROM movies WHERE id = ?', (id,))

The ORM method is more consistent with Python's object-oriented nature and reduces the need to remember SQL syntax.

🚀 Try It Out:

Add this new route to your app.py file
Add delete links to your index.html file: <a href="{{ url_for('delete_movie', id=movie.id) }}" onclick="return confirm('Are you sure?')">Delete</a>
Try deleting a movie and check if it's removed from the main page
Observe how the ORM handles the retrieval and deletion of the movie object

Final Challenge: Implementing a Search Feature with Flask-SQLAlchemy

Now that you've seen how ORMs simplify database operations, your challenge is to implement a search feature using Flask-SQLAlchemy. This will demonstrate how ORMs can make complex queries more intuitive.

Here's a starter template for the search route:

@app.route('/search')

def search_movies():

query = request.args.get('query', '')

# TODO: Use Flask-SQLAlchemy to search for movies

# Hint: You can use Movie.query.filter() with SQLAlchemy's like() method

movies = [] # Replace this with your query

return render_template('search.html', movies=movies, query=query)

🚀 To complete this challenge:

Implement the search functionality using Flask-SQLAlchemy's querying methods
Create a search form on your index page that submits to this new route
Display the search results in a table
Compare your ORM-based search implementation to how you might have done it with raw SQL. Which do you find more readable and maintainable?

Remember to commit and push your code when done! Here are the terminal commands to do so:

Commit your changes with a meaningful message:
git commit -am "Your message here"
Note: The -a flag automatically stages all modified files before committing, -m is to add a message.
Push your changes to GitHub:
git push origin main

Page updated

Report abuse

PROGRAMMING FOR THE WEB: SQLITETASK 4 - OBJECT-RELATIONAL MAPPING

Introduction to Object-Relational Mapping (ORM)

Key benefits of using an ORM:

Comparing ORM to raw SQL:

ORMs relate closely to OOP concepts you've learned:

Activity 1 - Setting Up Flask-SQLAlchemy

❓ Explanation:

🚀 Try It Out:

Activity 2 - Adding Movies with Flask-SQLAlchemy

❓ Explanation:

🚀 Try It Out:

Activity 3 - Updating Movies with Flask-SQLAlchemy

❓ Explanation:

🚀 Try It Out:

Activity 4 - Deleting Movies with Flask-SQLAlchemy

❓ Explanation:

🚀 Try It Out:

Final Challenge: Implementing a Search Feature with Flask-SQLAlchemy

🚀 To complete this challenge:

PROGRAMMING FOR THE WEB: SQLITE
TASK 4 - OBJECT-RELATIONAL MAPPING