Overview of SQLite and how it differs from other databases

Introduction to Flask and SQLite

Flask is a lightweight web framework for Python, which makes it easy to develop web applications. SQLite is a self-contained, serverless, and zero-configuration database engine that can be easily integrated into Flask applications. By combining Flask and SQLite, you can create web applications with efficient data storage and manipulation capabilities.

Setting up a Flask Application with SQLite

Install Flask and Flask-SQLAlchemy

pip install Flask Flask-SQLAlchemy

Create a new python file, name it anything you want, and add the following code:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///school.db'
db = SQLAlchemy(app)

class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)
    age = db.Column(db.Integer, nullable=False)

    def __repr__(self):
        return f'<Student {self.name}>'

In this code, we first import the necessary modules and create a Flask application. We then configure the database URI to use SQLite and create an instance of SQLAlchemy with the Flask app.

The Student class represents the "students" table in our SQLite database. We define the columns "id", "name", and "age" as class attributes, specifying their types and constraints.

Data Storage and Manipulation with Flask and SQLite

Creating a table:

To create a table, we define a class that inherits from db.Model, as shown in the Student class example. When the application starts, SQLAlchemy will create the table if it doesn't already exist.

Inserting data:

To insert a new student into the "students" table, you can create a new instance of the Student class and add it to the database session:

new_student = Student(name='John Doe', age=25)
db.session.add(new_student)
db.session.commit()

Querying data:

To retrieve all students from the "students" table, you can use the query attribute of the Student class:

students = Student.query.all()

To filter students by a specific criterion, you can use the filter_by method:

students = Student.query.filter_by(age=25).all()

Updating data:

To update a student's age in the "students" table, you can first query the student, modify the age attribute, and then commit the changes:

student = Student.query.get(1)
student.age = 26
db.session.commit()

Deleting data:

To delete a student from the "students" table, you can first query the student, remove it from the session, and then commit the changes:

student = Student.query.get(1)
db.session.delete(student)
db.session.commit()

Implementing Basic Routes with Flask and SQLite

To demonstrate data storage and manipulation in a Flask application, we can create simple routes for each operation.

Add the necessary imports and routes to app.py:

Here is how to add a student to the database

from flask import request, redirect, url_for

@app.route('/add_student', methods=['POST'])
def add_student():
    name = request.form['name']
    age = int(request.form['age'])
    new_student = Student(name=name, age=age)
    db.session.add(new_student)
    db.session.commit()
    return redirect(url_for('index'))

Displaying students:

To create a route that displays all students in the "students" table, you can implement an index route:

from flask import render_template

@app.route('/')
def index():
    students = Student.query.all()
    return render_template('index.html', students=students)

Updating a student's age:

To create a route that updates a student's age in the "students" table, you can implement an update_student route:

@app.route('/update_student/<int:student_id>', methods=['POST'])
def update_student(student_id):
    new_age = int(request.form['age'])
    student = Student.query.get(student_id)
    student.age = new_age
    db.session.commit()
    return redirect(url_for('index'))

Deleting a student:

To create a route that deletes a student from the "students" table, you can implement a delete_student route:

@app.route('/delete_student/<int:student_id>')
def delete_student(student_id):
    student = Student.query.get(student_id)
    db.session.delete(student)
    db.session.commit()
    return redirect(url_for('index'))

Homework - Creating a Template

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Students</title>
</head>
<body>
    <h1>Students</h1>
    <table>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Age</th>
            <th>Actions</th>
        </tr>
        {% for student in students %}
        <tr>
            <td>{{ student.id }}</td>
            <td>{{ student.name }}</td>
            <td>{{ student.age }}</td>
            <td>
                <form action="{{ url_for('update_student', student_id=student.id) }}" method="post">
                    <input type="number" name="age" value="{{ student.age }}">
                    <input type="submit" value="Update">
                </form>
                <a href="{{ url_for('delete_student', student_id=student.id) }}">Delete</a>
            </td>
        </tr>
        {% endfor %}
    </table>
    <h2>Add Student</h2>
    <form action="{{ url_for('add_student') }}" method="post">
        <label for="name">Name:</label>
        <input type="text" name="name">
        <label for="age">Age:</label>
        <input type="number" name="age">
        <input type="submit" value="Add">
    </form>
</body>
</html>
``

Conclusion

WE COVERED: I. Introduction to Flask and SQLite II. Setting up a Flask Application with SQLite III. Data Storage and Manipulation with Flask and SQLite IV. Implementing Basic Routes with Flask and SQLite V. Creating Templates

This lesson provides a basic understanding of how to set up and use a Flask web application with an SQLite database. Students should now be familiar with data storage and manipulation concepts, including creating tables, inserting data, querying data, updating data, and deleting data.

Now I'm going to hand it off to Samit and Martin