How To Create A Web Application Database

Tabby Mungai,

Can making a website make money? There answer is yes. However, it is important to note that making money is not limited to the web development skill set, as website can increase the revenue brought to a business by having a better understanding of the customer base.

On may ask why database is important? A business can make a website to make money particularly when it creates a web application database to collect crucial information to improve service delivery for its users. We live in an information age where data has become they new currency, consequently having information of the various user can be instrumental in providing businesses with the information they require to provide crucial services.

In this blog we will set up the SQLite database to store data and use sqlite3 module to interact with the database available within the standard Python library.

  • In the folder flask1_app create a file schema.sql
  • In the .sql file create a table called posts having the necessary columns with code provided
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
);

The code in the app.py file below works to set up the database connection in addition to creating a single Flask route to the apply to that particular connection.

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)

To display the post the in within the database created it is vital to undertake the following steps.

  • Create a directory named templates
  • Create an index.html and base.html file

The code that will go into base.html file

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>{% block title %} {% endblock %}- FlaskApp</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') }}">FlaskApp</a>
        <a href="#">About</a>
    </nav>
    <hr>
    <div class="content">
        {% block content %} {% endblock %}
    </div>
</body>
</html>

This code goes to the index.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 %}

Having an activated virtual environment within the flask1_app directory the FLASK_APP environment variable is used to tell Flask about the application in this case app.py.

Then set the FLASK_ENV environment variable to development to run the application in development mode and access the debugger.

The picture displays posts in the database within the index page that allows user to add new posts.

In the next steps creates a new route for adding posts.

Create

Add a new route to the Flask application allowing users to add an innovative blog post to the databased that would appear on the index page. Open the app.py file.

Import few things from the flask package

  • The global request object to access submitted data
  • The url_for() function to general URLs
  • The flash() function to flash a message when the request is invalid
  • The redirect() function to redirect used to index page after adding the posts to the database
from flask import Flask, render_template, request, url_for, flash, redirect

# ...

Add the following route at the end of the app.py file 

# ...

@app.route('/create/', methods=('GET', 'POST'))
def create():
    return render_template('create.html')

The output of the /create route can be seen below: http: //127.0.0.1:5000/create

If the form is filled the and you click the submit button nothing happens because the POST request was not handle on the /create route. The app.py file ought to be edited to look like this:

# ...

@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')

The POST request is handle within the if request.method == ‘POST’

The title and content is extracted by the user by submitting the request.form object.

If the title is empty the flash() function is useful to flash the message Title is required! The base.html file ought to be edited to look like the code below 

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>{% block title %} {% endblock %} - FlaskApp</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') }}">FlaskApp</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>

Programming

Related Posts