Adding in a database

While using dictionaries to store data is fine for small projects, it is not a good idea for larger projects. We normally want our data to be persistent and not lost when the server is restarted. This is where databases come in. Databases are used to store data in a structured way and can be queried to retrieve data.

We will be using SQLite for these resources but any SQL database can be used. SQLite is a lightweight database that is easy to use and is perfect for small projects.

Setting up the database

First, we need to create a database and a table to store our data. We will be storing the data in a table called posts with the following columns:

id: The unique identifier for each post
title: The title of the post
content: The content of the post

We will be using the sqlite3 module to interact with the database. The sqlite3 module is included with Python so you don’t need to install anything.

Create a new file called database.py and add the following code:

import sqlite3

# Create a new database file
conn = sqlite3.connect('blog.db')

# Create a new table called posts
conn.execute('''
CREATE TABLE posts
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL
)
''')

# Commit the changes
conn.commit()

# Close the connection
conn.close()

This code creates a new database file called blog.db and creates a new table called posts with the required columns. The id column is the primary key and will auto-increment for each new post.

Run the database.py file to create the database and table:

python database.py

Using our database with flask

Now that we have our database set up, we can start using it in our Flask application. We will be using the sqlite3 module to interact with the database.

import sqlite3
from flask import g, Flask

app = Flask(__name__)

def get_db():
   db = getattr(g, "_database", None)
   if db is None:
       db = g._database = sqlite3.connect("blog.db")
   return db

@app.teardown_appcontext
def close_connection(exception):
   db = getattr(g, "_database", None)
   if db is not None:
       db.close()


@app.route("/")
def index():
   db = get_db()
   posts = db.execute("SELECT * FROM posts").fetchall()
   return posts

We first define a get_db function that returns a connection to the database.

We use the g object to store the connection so that it can be reused across requests. This function will return the connection if it already exists, otherwise, it will create a new connection to the database.
We define a close_connection function that closes the connection to the database when the request is finished.

We use the teardown_appcontext decorator to register this function so that when the app closes the connection is closed.
We define a route called / that fetches all the posts from the database and returns them.

We use the execute method to execute an SQL query and the fetchall method to fetch all the results.

Using SQLAlchemy

While using the sqlite3 module directly is fine for small projects, it can be cumbersome for larger projects. SQLAlchemy is a popular ORM (Object-Relational Mapping) library that makes it easy to interact with databases.

To use SQLAlchemy, you need to install the flask_sqlalchemy package:

pip install flask_sqlalchemy

Configuring SQLAlchemy

You can then use the SQLAlchemy class to create a new database connection:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///blog.db"

db = SQLAlchemy(app)

Defining a model

You can then define a model for the posts table using the db.Model class:

class Post(db.Model):
   id = db.Column(db.Integer, primary_key=True)
   title = db.Column(db.String(100), nullable=False)
   content = db.Column(db.Text, nullable=False)

Querying the database

You can then use the query method to query the database:

@app.route("/")
def index():
    posts = Post.query.all()
    return [{"title": post.title, "content": post.content} for post in posts]

This would be equivalent to the following SQL query:

SELECT * FROM posts

Inputting data into the database

You can then use the add method to add data to the database:

@app.route("/add_post")
def add_post():
    post = Post(title="Hello World", content="This is my first post")
    db.session.add(post)
    db.session.commit()
    return "Post added"

Instead of having to write raw SQL queries, you can use SQLAlchemy to interact with the database in a more object-oriented way. Using raw SQL queries we would have to convert it out of the dictionary format and into a string format to be able to insert it into the database. With SQLAlchemy, we can just pass the object directly to the add method.

@app.route("/add_post")
def add_post():
    post = {title:"Hello World", content:"This is my first post"}
    db.execute("INSERT INTO posts (title, content) VALUES (?, ?)", (post["title"], post["content"]))
    db.commit()
    return "Post added"

Extending SQLAlchemy

While the above example is a simple example of using SQLAlchemy, it can be extended to include more advanced features such as relationships, migrations, and more.

Relationships

When we have multiple tables in our database, we can define relationships between them using SQLAlchemy. For example, we can define a User model and a Post model and define a relationship between them:

class User(db.Model):
   id = db.Column(db.Integer, primary_key=True)
   username = db.Column(db.String(100), nullable=False)
   posts = db.relationship("Post", backref="user", lazy=True)

class Post(db.Model):
   id = db.Column(db.Integer, primary_key=True)
   title = db.Column(db.String(100), nullable=False)
   content = db.Column(db.Text, nullable=False)

We can then query the database to get all the posts for a user:

@app.route("/user/<int:user_id>")
def user_posts(user_id):
   user = User.query.get(user_id)
   posts = user.posts
   return posts

Updating and Deleting Data

We can also update and delete data from the database using SQLAlchemy. For example, we can update a post with a specific id:

post = Post.query.get(1)
post.title = "New Title"
db.session.commit()

We can also delete a post with a specific id:

post = Post.query.get(1)
db.session.delete(post)
db.session.commit()

Using SQLAlchemy to setup the database

We can also use SQLAlchemy to create the database and tables for us. We can use the create_all method to create the tables. If the tables already exist, this method will not do anything.

with app.app_context():
    db.create_all()