This is the second part of a five-part guide that shows you how to build a web app to co-ordinate a gift exchange. We’ll use Flask, a Python web framework, and Infobip’s Python SDK.

In this part of the guide, we add a database to our app to store names and phone numbers.

You can follow along with the code at the project repository on GitHub, where each step of this guide corresponds to a branch in the repo.

Defining our database schema

A relational database needs a schema, which defines what tables exist in the database and how they relate to each other. Our database is going be very simple: a single table which stores a phone number, a name, whether or not that person has bought a gift for their giftee, and whether or not that phone number has been verified (along with a code later used in the verification process). Each row also contains a link to another row in the same table, which tells us who each person has to buy a gift for.

To create a database with this structure, we’re going to write a SQL file that lives within our Flask application. Create a file called schema.sql in the top level of your directory, so that your directory structure looks like this:

gift-exchange-app/
    app.py
    requirements.txt
    schema.sql
    templates/
        app.html

Now add the following code to that file:

CREATE TABLE people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    phone_number TEXT NOT NULL,
    name TEXT NOT NULL,
    gift_bought INTEGER,    
    verified INTEGER,
    verification_code TEXT,
    giftee INTEGER REFERENCES people(id)
);

This SQL statement, when applied to a database, will create a new table called people with the structure that we described above. Currently, we don’t have a database to execute this command against, so that’s our next task.

Creating our database

Create a Python file in your app’s directory called create_db.py, and add the following code into it:

import sqlite3
conn = sqlite3.connect('database.db')

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

conn.commit()
conn.close()

This code creates a new database with the line sqlite3.connect('database.db'), and executes the SQL code we just wrote in that database, creating our new table.

Now, in your terminal, run the following command:

python create_db.py

You’ll see that a new file has been created in the same directory called database.db, and your directory structure now looks like this:

gift-exchange-app/
    app.py
    create_db.py
    requirements.txt
    schema.sql
    database.db
    templates/
        app.html

The create_db.py file isn’t needed any more; you can delete it if you like.

Storing data from our app

Now that we have a database, we need to connect to it from within our app’s code. Open up your app.py file, and look at the index method, which looks like this:

@app.route('/', methods=['GET', 'POST'])
def index():
   if request.method == 'GET':
       pass
   else:
       name = request.form.get("name")
       number = request.form.get("number")
       print(f‘You clicked the button with name {name} and number {phone}!’)
   return render_template('app.html')

We’re going to add code into this so that it handles the user input correctly, and inserts suitable data into our database.

First, add the following imports to the top of the file:

import sqlite3
from flask import flash

We use sqlite3 to access our database, and the flash method lets us display messages to the user.

In order to make this work ,our app needs a ‘secret key’ which Flask uses to manage sessions. For our purposes, what this means is that, just above your index method, you’ll need to add the following line:

app.secret_key = "secret key"

Next, change your index method to include the following:

@app.route('/', methods=['GET', 'POST'])
def index():
   if request.method == 'GET':
       pass
   else:
       name = request.form.get("name")
       number = request.form.get("number")
       if not all((name, number)):
           flash("Please enter both a name and a phone number.")
       else:
           print(f"You clicked the button with name {name} and number {number}!")
           add_new_person_to_db(name, number)
           flash(f"You added {name} with number {phone}!")
   return render_template('app.html')

With this code, we check that both pieces of data are present, then add them into the database. The flash method tells Flask that a message needs to be displayed to the user; in order for it to actually happen, we need to edit our HTML.

In your app.html file, add the following code to the beginning of the <body> section:

        {% with msg = get_flashed_messages() %}
            {% if msg %}
                {% for message in msg %}
                    <strong>{{ message }}</strong>
                {% endfor %}
            {% endif %}
        {% endwith %}

Now, when the flash method is called in your Python code with a message, that message will be displayed at the top of the page.

Next, we need to actually write the add_new_person_to_db method that we call in the success scenario. Add this code into your app.py file:

def add_new_person_to_db(name, number):
   conn = sqlite3.connect('database.db')
   cur = conn.cursor
   cur.execute(f"""
      INSERT INTO people (name, phone_number) VALUES
         ({name}, {number})""")
   conn.commit()
   conn.close()

This code opens a connection to our database, and adds the data from the user into it.

NOTE: This is wildly insecure, and in a real-life scenario you would need to do extra input sanitisation to protect against SQL injection attacks. For the purposes of this tutorial, we’re not going to worry about that. If you’d like to learn more about SQL injection and how to protect against it, RealPython has an excellent guide to get you started.

We’ve now created a database and extended our app to allow it to store data from users within that database!

Next time

In the next part, we’ll add functionality to our app so that each participant is required to opt-in and verify their phone number so that the gift exhange can be arranged.