Wikiversity

Tehnologii Web/2022-2023/Laborator 9

< Tehnologii Web/2022-2023

Flask Database Integration edit

In this lesson, we will specify how to connect a Flask application to a database.

Database configuration edit

In this chapter, the SQLite database used for storing the data will be configured. Next, the database will be populated with some sample data. The sqlite3 module will be used to interact with the database, which is readily available in the Python standard library. Data in SQLite is stored in tables and columns, so a table with the required columns must be created. Let's assume we want to make a store of data about blog posts.

First, a .sql file will be created in the flask_app directory, which contains SQL commands to create the posts table with some columns. This schema file will be used to create the database. 

DROP TABLE IF EXISTS posts;

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    title TEXT NOT NULL,
    content TEXT NOT NULL
)

In this schema file, we first delete the table posts if it already exists. This avoids the possibility of having another table called posts, which could lead to confusing behavior. This is not the case here because the table has not been created yet, so the SQL command will not be executed. We should know that this will delete all existing data whenever this schema file is executed.

Then, CREATE TABLE posts is used to create the posts table with the following columns:

  • id: An integer representing a primary key. This key will be assigned a unique value by the database for each entry (i.e. each blog post). AUTOINCREMENT automatically increments post IDs, so the first post will have an ID of 1, and the post added after that will have an ID of 2, and so on. Each post will always have the same ID, even if there exist some posts that are deleted
  • created: the time the blog post was created. NOT NULL means that this column must not be empty, and the DEFAULT value is the CURRENT_TIMESTAMP value, which is when the post was added to the database. Like id, no value needs to be specified for this column as it will be filled in automatically
  • title: the title of the blog post. It cannot be empty
  • content: the content of the blog post. It cannot be empty.


Now, the schema.sql file will be used to create the database. To do this, a Python-type file will be created and it will generate an SQLite database file with a .db extension, based on the schema.sql file. A file called init_db.py is created in the flask_app directory. 

import sqlite3

connection = sqlite3.connect('database.db')


with open('schema.sql') as f:
    connection.executescript(f.read())

current = connection.cursor()

current.execute("INSERT INTO posts (title, content) VALUES (?, ?)",
            ('First Post', 'Content for the first post')
            )

current.execute("INSERT INTO posts (title, content) VALUES (?, ?)",
            ('Second Post', 'Content for the second post')
            )

connection.commit()
connection.close()

First the sqlite3 module is imported. A connection is created to a database file called database.db, which will be created once the Python file is run. The open() function is then used to open the schema.sql file. It then executes its contents using methods that execute multiple SQL statements simultaneously, which will create the posts table. A cursor object is created and it will allow the rows from a database to be processed.

In this case, the cursor's execute() method will be used to execute two INSERT SQL statements, to add two blog posts to the posts table. Finally, the changes will be committed and the connection closed.

Once the file finishes executing, a new file named database.db will appear in the flask_app directory. This means that the database has been successfully configured.

Afterward, a small Flask application will be created. We can access the two posts entered in the database and display them on the index page.


Displaying blog posts edit

In this section, we will create a Flask application with an index page that displays the blog posts we have in our database.

In the app.py file, in the flask_app directory, the following will be entered. 

import sqlite3
from flask import Flask, render_template

app = Flask(__name__)

def get_db_connection():
    conn = sqlite3.connect('database.db')
    conn.row_factory = sqlite3.Row
    return conn

@app.route('/')
def index():
    conn = get_db_connection()
    posts = conn.execute('SELECT * FROM posts').fetchall()
    conn.close()
    return render_template('index.html', posts=posts)

In the code above, the sqlite3 module is first imported to connect to the database. Then the Flask class and the render_template() function from the flask package are imported. A Flask application instance named app is created. A function called get_db_connection() is defined and it opens a connection to the database.db file that was created earlier and sets the row_factory attribute to sqlite3.Row so that columns can be accessed by name. This means that the database connection will return rows that behave like Python dictionaries. Finally, the function returns the connection object that we will use to access the database.

Then use the app.route() decorator to create a Flask view function called index(). Use the get_db_connection() function to open a database connection. Then, run a SQL query to select all entries in the posts table. Use the fetchall() method to fetch all the rows of the query result, this will return a list of the posts we entered into the database in the previous part.

We close the database connection using the close() method and return the result of rendering the index.html template. We also transfer the posts object as an argument, which contains the results we got from the database. This will allow us to access the blog posts in the index.html template.


First, the main, base HTML file named base.html is created. 

<!DOCTYPE html>

<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>{% block title %} {% endblock %}: Simple Flask</title>
    <style>
        .post {
            padding: 10px;
            margin: 5px;
            background-color: #f3f3f3
        }

        nav a {
            color: #d64161;
            font-size: 3em;
            margin-left: 50px;
            text-decoration: none;
        }
    </style>
</head>
<body>
    <nav>
        <a href="{{ url_for('index') }}">Simple Flask</a>
        <a href="#">About</a>
    </nav>
    <hr>
    <div class="content">
        {% block content %} {% endblock %}
    </div>
</body>
</html>

After this, the index.html file will also be created, which will inherit from the base.html file. 

{% extends 'base.html' %}

{% block content %}
    <h1>{% block title %} Posts {% endblock %}</h1>
    {% for post in posts %}
        <div class='post'>
            <p>{{ post['created'] }}</p>
            <h2>{{ post['title'] }}</h2>
            <p>{{ post['content'] }}</p>
        </div>
    {% endfor %}
{% endblock %}

In the code above, it extends the base.html template and replaces the contents of the content block.

A Jinja for loop is used in the {% for post in posts %} line to loop through each post in the posts list. Access the creation date using {{ post['created'] }}, the title using {{ post['title'] }}, and the post content using {{ post['content'] }}.

Creating blog posts edit

An HTML file named create.html is created. 

{% block content %}
    <h1>{% block title %} Add a New Post {% endblock %}</h1>
    <form method="post">
        <label for="title">Title</label>
        <br>
        <input type="text" name="title"
               placeholder="Post title"
               value="{{ request.form['title'] }}"></input>
        <br>

        <label for="content">Post Content</label>
        <br>
        <textarea name="content"
                  placeholder="Post content"
                  rows="15"
                  cols="60"
                  >{{ request.form['content'] }}</textarea>
        <br>
        <button type="submit">Submit</button>
    </form>
{% endblock %}

Extend the base template, set a title with title, and use a <form> tag with the attribute method set to post to indicate that the form will send a POST request. There is a text field called title, which will be used to access the title data from the /create path. Set the text field value to request.form['title'] which is either empty or a saved version of the title if the form is invalid, so the title isn't lost when things go wrong. After the title text field, a text area called content with the value is added {{ request.form['content'] }} to restore the post content if the form is invalid. Finally, there is also a Submit button at the end of the form. Now with the development server running, use the browser to navigate to the /create path. Next, we add the following lines of code to the app.py file. 

@app.route('/create/', methods=('GET', 'POST'))
def create():
    if request.method == 'POST':
        title = request.form['title']
        content = request.form['content']

        if not title:
            flash('Title is required!')
        elif not content:
            flash('Content is required!')
        else:
            conn = get_db_connection()
            conn.execute('INSERT INTO posts (title, content) VALUES (?, ?)',
                         (title, content))
            conn.commit()
            conn.close()
            return redirect(url_for('index'))

    return render_template('create.html')

Handle POST requests under the condition if request.method == "POST". Extract the title and content that the user submits from the request.form object. If the title is empty, use the flash() function to display the message Title is required!. The same will be done for empty content.

If both title and content are provided, open a connection to the database using the get_db_connection() function. Use the execute() method to execute an INSERT INTO SQL statement to add a new post to the posts table with the title and content that the user submits. It is used ? as a placeholder to insert the data into the table safely. Commit the transaction and close the connection. Finally, it redirects the user to the index page where they can see their new post below the existing posts.

Afterward, the form is filled out and submitted.

We will be redirected to the index page where we will see the new post.

If we submit a form with no title or no content, the post will not be added to the database and we will not be redirected to the index page and receive no feedback. This will happen because we have not configured the flashing messages yet, to be displayed.

Open base.html to add a link to the create.html page in the navigation bar and flash messages to it.

The base.html file should look like this: 

<!DOCTYPE html>

<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>{% block title %} {% endblock %}: Simple Flask </title>
    <style>
        .post {
            padding: 10px;
            margin: 5px;
            background-color: #f3f3f3
        }

        nav a {
            color: #d64161;
            font-size: 3em;
            margin-left: 50px;
            text-decoration: none;
        }

        .alert {
            padding: 20px;
            margin: 5px;
            color: #970020;
            background-color: #ffd5de;
        }
    </style>
</head>
<body>
    <nav>
        <a href="{{ url_for('index') }}">Simple Flask</a>
        <a href="{{ url_for('create') }}">Create</a>
        <a href="#">About</a>
    </nav>
    <hr>
    <div class="content">
        {% for message in get_flashed_messages() %}
            <div class="alert">{{ message }}</div>
        {% endfor %}

        {% block content %} {% endblock %}
    </div>
</body>
</html>
Retrieved from "https://en.wikiversity.org/w/index.php?title=Tehnologii_Web/2022-2023/Laborator_9&oldid=2455711"