How To Make a URL Shortener with Flask and SQLite

The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

Introduction

Flask is a framework for building web applications using Python and SQLite is a database engine that you can use with Python to store application data.

In this tutorial, you will build a URL shortener, a service that takes any URL and generates a shorter, more readable version like bit.ly.

Hashids is a library that generates a short unique ID from integers. For example, you can use it to convert a number like 12 to a unique string like 1XcId. You will use Hashids to generate unique strings for URL IDs.

You can use unique strings to generate IDs for videos on a video-sharing site or IDs for images on a service to upload images. This unique string gives you unpredictable IDs; therefore, if a user can access an image at your_domain/image/J32Fr, they can’t predict the location of other images. This is not possible if you use integer IDs in a URL shortener—for example, your_domain/image/33 would allow users to predict the location of other images. Unpredictable URLs add a form of privacy to your service because they prevent users from working out different URLs shortened by other users.

You will use Flask, SQLite, and the Hashids library to build your URL shortener. Your application will allow users to enter a URL and generate a shorter version, in addition to a statistics page where users can view the number of times a URL has been clicked. You’ll use the Bootstrap toolkit to style your application.

Prerequisites

• A local Python 3 programming environment, follow the tutorial for your distribution in How To Install and Set Up a Local Programming Environment for Python 3 series. In this tutorial, we’ll call our project directory flask_shortener.

• An understanding of basic Flask concepts such as creating routes, rendering HTML templates, and connecting to an SQLite database. Check out How To Make a Web Application Using Flask in Python 3 and How To Use the sqlite3 Module in Python 3 if you are not familiar with these concepts, but it’s not necessary.

Step 1 — Setting Up Dependencies

In this step, you will activate your Python environment and install Flask and the Hashids library using the pip package installer. Then you’ll create the database you will use to store URLs.

First, activate your programming environment if you haven’t already:

source env/bin/activate

Once you have activated your programming environment, install Flask and the Hashids library using the following command:

pip install flask hashids

Then create a database schema file called schema.sql, containing SQL commands to create a urls table. Open a file called schema.sql inside your flask_shortener directory:

nano schema.sql

Type the following SQL commands inside this file:

DROP TABLE IF EXISTS urls;

CREATE TABLE urls (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    original_url TEXT NOT NULL,
    clicks INTEGER NOT NULL DEFAULT 0
);

In the schema file, you first delete the urls table if it already exists. This avoids the possibility of another table named urls existing, which might result in confusing behavior; for example, if it has different columns. Note that this will delete all of the existing data whenever the schema file executes.

You then create the table with the following columns:

• id: The ID of the URL, this will be a unique integer value for each URL entry. You will use it to get the original URL from a hash string.
• created: The date the URL was shortened.
• original_url: The original long URL to which you will redirect users.
• clicks: The number of times a URL has been clicked. The initial value will be 0, which will increment with each redirect.

Save and close the file.

To execute the schema.sql file to create the urls table, open a file named init_db.py inside your flask_shortener directory:

nano init_db.py

Then add the following code:

import sqlite3

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

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

connection.commit()
connection.close()

Here you connect to a file called database.db that your program will create once you execute this program. This file is the database that will hold all of your application’s data. You then open the schema.sql file and run it using the executescript() method that executes multiple SQL statements at once. This will create the urls table. Finally, you commit the changes and close the connection.

Save and close the file.

Run the program:

python init_db.py

After execution, a new file called database.db will appear in your flask_shortener directory.

With this, you’ve installed Flask and the Hashids library, created the database schema, and created the SQLite database with a table called urls to store the URL shortener’s original URLs. Next, you’ll use Flask to create the index page where your users can enter a URL to generate a short URL.

Step 2 — Creating the Index Page for Shortening URLs

In this step, you will create a Flask route for the index page, which will allow users to enter a URL that you then save into the database. Your route will use the ID of the URL to generate a short string hash with the Hashids library, construct the short URL, and then render it as a result.

First, open a file named app.py inside your flask_shortener directory. This is the main Flask application file:

nano app.py

Add the following code to the file:

import sqlite3
from hashids import Hashids
from flask import Flask, render_template, request, flash, redirect, url_for


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

In this code, you first import the sqlite3 module, the Hashids class from the hashids library, and Flask helpers.

The get_db_connection() function opens a connection to the database.db database file and then sets the row_factory attribute to sqlite3.Row. As a result, you can have name-based access to columns; the database connection will return rows that behave like regular Python dictionaries. Lastly, the function returns the conn connection object you’ll be using to access the database.

Next, add the following:

. . .
app = Flask(__name__)
app.config['SECRET_KEY'] = 'this should be a secret random string'

hashids = Hashids(min_length=4, salt=app.config['SECRET_KEY'])

You create the Flask application object and set a secret key to secure sessions. Since the secret key is a secret random string, you’ll also use it to specify a salt for the Hashids library; this will ensure the hashes are unpredictable since every time the salt changes, the hashes also change.

You create a hashids object specifying that a hash should be at least 4 characters long by passing a value to the min_length parameter. You use the application’s secret key as a salt.

Next, add the following code to the end of your file:

. . .
@app.route('/', methods=('GET', 'POST'))
def index():
    conn = get_db_connection()

    if request.method == 'POST':
        url = request.form['url']

        if not url:
            flash('The URL is required!')
            return redirect(url_for('index'))

        url_data = conn.execute('INSERT INTO urls (original_url) VALUES (?)',
                                (url,))
        conn.commit()
        conn.close()

        url_id = url_data.lastrowid
        hashid = hashids.encode(url_id)
        short_url = request.host_url + hashid

        return render_template('index.html', short_url=short_url)

    return render_template('index.html')

The index() functions is a Flask view function, which is a function decorated using the special @app.route decorator. Its return value gets converted into an HTTP response that an HTTP client, such as a web browser, displays.

Inside the index() view function, you accept both GET and POST requests by passing methods=('GET', 'POST') to the app.route() decorator. You open a database connection.

Then if the request is a GET request, it skips the if request.method == 'POST' condition until the last line. This is where you render a template called index.html, which will contain a form for users to enter a URL to shorten.

If the request is a POST request, the if request.method == 'POST' condition is true, which means a user has submitted a URL. You store the URL in the url variable; if the user has submitted an empty form, you flash the message The URL is required! and redirect to the index page.

If the user has submitted a URL, you use the INSERT INTO SQL statement to store the submitted URL in the urls table. You include the ? placeholder in the execute() method and pass a tuple containing the submitted URL to insert data safely into the database. Then you commit the transaction and close the connection.

In a variable called url_id, you store the ID of the URL you inserted into the database. You can access the ID of the URL using the lastrowid attribute, which provides the row ID of the last inserted row.

You construct a hash using the hashids.encode() method, passing it the URL ID; you save the result in a variable called hashid. As an example, the call hashids.encode(1) might result in a unique hash like KJ34 depending on the salt you use.

You then construct the short URL using request.host_url, which is an attribute that Flask’s request object provides to access the URL of the application’s host. This will be http://127.0.0.1:5000/ in a development environment and your_domain if you deploy your application. For example, the short_url variable will have a value like http://127.0.0.1:5000/KJ34, which is the short URL that will redirect your users to the original URL stored in the database with the ID that matches the hash KJ34.

Lastly, you render the index.html template passing the short_url variable to it.

After all the additions, the file will be as follows:

import sqlite3
from hashids import Hashids
from flask import Flask, render_template, request, flash, redirect, url_for


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


app = Flask(__name__)
app.config['SECRET_KEY'] = 'this should be a secret random string'

hashids = Hashids(min_length=4, salt=app.config['SECRET_KEY'])


@app.route('/', methods=('GET', 'POST'))
def index():
    conn = get_db_connection()

    if request.method == 'POST':
        url = request.form['url']

        if not url:
            flash('The URL is required!')
            return redirect(url_for('index'))

        url_data = conn.execute('INSERT INTO urls (original_url) VALUES (?)',
                                (url,))
        conn.commit()
        conn.close()

        url_id = url_data.lastrowid
        hashid = hashids.encode(url_id)
        short_url = request.host_url + hashid

        return render_template('index.html', short_url=short_url)

    return render_template('index.html')

Save and close the file.

Next, you’ll create a base template and the index.html template file.

In your flask_shortener directory, create a templates directory and open a file called base.html inside it:

mkdir templates
nano templates/base.html

Add the following code inside base.html. Note that, for styling, you’re using Bootstrap here too. If you are not familiar with HTML templates in Flask, see Step 3 of How To Make a Web Application Using Flask in Python 3:

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

    <title>{% block title %} {% endblock %}</title>
  </head>
  <body>
    <nav class="navbar navbar-expand-md navbar-light bg-light">
        <a class="navbar-brand" href="{{ url_for('index')}}">FlaskShortener</a>
        <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
            <span class="navbar-toggler-icon"></span>
        </button>
        <div class="collapse navbar-collapse" id="navbarNav">
            <ul class="navbar-nav">
            <li class="nav-item active">
                <a class="nav-link" href="#">About</a>
            </li>
            </ul>
        </div>
    </nav>
    <div class="container">
        {% for message in get_flashed_messages() %}
            <div class="alert alert-danger">{{ message }}</div>
        {% endfor %}
        {% block content %} {% endblock %}
    </div>

    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
  </body>
</html>

Most of the code in the preceding block is standard HTML and code required for Bootstrap. The <meta> tags provide information for the web browser, the <link> tag links the Bootstrap CSS files, and the <script> tags are links to JavaScript code that allows some additional Bootstrap features. Check out the Bootstrap documentation for more information.

The <title>{% block title %} {% endblock %}</title> tag allows the inheriting templates to define a custom title. You use the for message in get_flashed_messages() loop to display the flashed messages (warnings, alerts, and so on). The {% block content %} {% endblock %} placeholder is where inheriting templates place the content so that all templates have access to this base template, which avoids repetition.

Save and close the file.

Next, create the index.html file that will extend this base.html file:

nano templates/index.html

Add the following code to it:

{% extends 'base.html' %}

{% block content %}
    <h1>{% block title %} Welcome to FlaskShortener {% endblock %}</h1>
    <form method="post">
    <div class="form-group">
        <label for="url">URL</label>
        <input type="text" name="url"
               placeholder="URL to shorten" class="form-control"
               value="{{ request.form['url'] }}" autofocus></input>
    </div>

    <div class="form-group">
        <button type="submit" class="btn btn-primary">Submit</button>
    </div>
    </form>

    {% if short_url %}
    <hr>
    <span>{{ short_url }}</span>
    {% endif %}
{% endblock %}

Here you extend base.html, define a title, and create a form with an input named url. The url input will allow users to enter URLs to shorten. It has a value of request.form['url'], which stores data in cases of submission failure; that is if the user provides no URL. You also add a submit button.

Then you check if the short_url variable has any value—this is true if the form submits and the short URL generates successfully. If the condition is true, you display the short URL under the form.

Set the environment variables Flask needs and run the application using the following commands:

export FLASK_APP=app
export FLASK_ENV=development
flask run

The FLASK_APP environment variable specifies the application you want to run (the app.py file). The FLASK_ENV environment variable specifies the mode. development means that the application will run in development mode with the debugger running. Remember to avoid using this mode in production. You run the application using the flask run command.

Open a browser and type in the URL http://127.0.0.1:5000/. You will find a Welcome to FlaskShortener page.

Submit a URL, and you will receive a short URL.

You created a Flask application with a page that accepts URLs and generates shorter ones, but the URLs don’t do anything yet. In the next step, you’ll add a route that extracts the hash from the short URL, finds the original URL, and redirects users to it.

Step 3 — Adding the Redirect Route

In this step, you will add a new route that takes the short hash the application generates and decodes the hash into its integer value, which is the original URL’s ID. Your new route will also use the integer ID to fetch the original URL and increment the clicks value. Finally, you will redirect users to the original URL.

First, open the app.py to add a new route:

nano app.py

Add the following code to the end of the file:

. . .

@app.route('/<id>')
def url_redirect(id):
    conn = get_db_connection()

    original_id = hashids.decode(id)
    if original_id:
        original_id = original_id[0]
        url_data = conn.execute('SELECT original_url, clicks FROM urls'
                                ' WHERE id = (?)', (original_id,)
                                ).fetchone()
        original_url = url_data['original_url']
        clicks = url_data['clicks']

        conn.execute('UPDATE urls SET clicks = ? WHERE id = ?',
                     (clicks+1, original_id))

        conn.commit()
        conn.close()
        return redirect(original_url)
    else:
        flash('Invalid URL')
        return redirect(url_for('index'))

This new route accepts a value id through the URL and passes it to the url_redirect() view function. For example, visiting http://127.0.0.1:5000/KJ34 would pass the string 'KJ34' to the id parameter.

Inside the view function, you first open a database connection. Then you use the decode() method of the hashids object to convert the hash to its original integer value and store it in the original_id variable. You check that the original_id has a value—meaning decoding the hash was successful. If it has a value, you extract the ID from it. As the decode() method returns a tuple, you fetch the first value in the tuple with original_id[0], which is the original ID.

You then use the SELECT SQL statement to fetch the original URL and its number of clicks from the urls table, where the ID of the URL matches the original ID you extracted from the hash. You fetch the URL data with the fetchone() method. Next, you extract the data into the two original_url and clicks variables.

You then increment the number of clicks of the URL with the UPDATE SQL statement.

You commit the transaction and close the connection, and redirect to the original URL using the redirect() Flask helper function.

If decoding the hash fails, you flash a message to inform the user that the URL is invalid, and redirect them to the index page.

Save and close the file.

Run your development server:

flask run

Use your browser to go to http://127.0.0.1:5000/. Enter a URL and visit the resulting short URL; your application will redirect you to the original URL.

You created a new route that redirects users from the short URL to the original URL. Next, you’ll add a page to show how many times each URL has been visited.

Step 4 — Adding a Statistics Page

In this step, you’ll add a new route for a statistics page that displays how many times each URL has been clicked. You’ll also add a button that links to the page on the navigation bar.

Allowing users to see the number of visits each shortened link has received will provide visibility into each URL’s popularity, which is useful for projects, like marketing ad campaigns. You can also use this workflow as an example of adding a feature to an existing Flask application.

Open app.py to add a new route for a statistics page:

nano app.py

Add the following code to the end of the file:

. . .

@app.route('/stats')
def stats():
    conn = get_db_connection()
    db_urls = conn.execute('SELECT id, created, original_url, clicks FROM urls'
                           ).fetchall()
    conn.close()

    urls = []
    for url in db_urls:
        url = dict(url)
        url['short_url'] = request.host_url + hashids.encode(url['id'])
        urls.append(url)

    return render_template('stats.html', urls=urls)

In this view function, you open a database connection. Then you fetch the ID, the creation date, the original URL, and the number of clicks for all of the entries in the urls table. You use the fetchall() method to get a list of all the rows. You then save this data in the db_urls variable and close the connection.

To display the short URL for each entry, you will need to construct it and add it to each item in the list of the URLs you fetched from the database (db_urls). You create an empty list called urls and loop through the db_urls list with for url in db_urls.

You use the dict() Python function to convert the sqlite3.Row object to a dictionary to allow assignment. You add a new key called short_url to the dictionary with the value request.host_url + hashids.encode(url['id']), which is what you used before to construct short URLs in the index view function. You append this dictionary to the urls list.

Finally, you render a template file called stats.html, passing the urls list to it.

Save and close the file.

Next, create the new stats.html template file:

nano templates/stats.html

Type the following code into it:

{% extends 'base.html' %}

{% block content %}
    <h1>{% block title %} FlaskShortener Statistics {% endblock %}</h1>
    <table class="table">
        <thead>
            <tr>
            <th scope="col">#</th>
            <th scope="col">Short</th>
            <th scope="col">Original</th>
            <th scope="col">Clicks</th>
            <th scope="col">Creation Date</th>
            </tr>
        </thead>
        <tbody>
            {% for url in urls %}
                <tr>
                    <th scope="row">{{ url['id'] }}</th>
                    <td>{{ url['short_url'] }}</td>
                    <td>{{ url['original_url'] }}</td>
                    <td>{{ url['clicks'] }}</td>
                    <td>{{ url['created'] }}</td>
                </tr>
            {% endfor %}
        </tbody>
    </table>

{% endblock %}

Here you extend the base.html base template by specifying a title and defining a table with the following columns:

• #: The ID of the URL.
• Short: The short URL.
• Original: The original URL.
• Clicks: The number of times a short URL has been visited.
• Creation Date: The creation date of the short URL.

Each row is filled using a for loop that goes through the urls list and displays the value of each column for each URL.

Run the development server with the following:

flask run

Use your browser to go to http://127.0.0.1:5000/stats. You will find all the URLs in a table.

Next, add a Stats button to the navigation bar. Open the base.html file:

nano templates/base.html

Edit the file as per the following highlighted lines:

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

    <title>{% block title %} {% endblock %}</title>
  </head>
  <body>
    <nav class="navbar navbar-expand-md navbar-light bg-light">
        <a class="navbar-brand" href="{{ url_for('index')}}">FlaskTodo</a>
        <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
            <span class="navbar-toggler-icon"></span>
        </button>
        <div class="collapse navbar-collapse" id="navbarNav">
            <ul class="navbar-nav">
            <li class="nav-item active">
                <a class="nav-link" href="#">About</a>
            </li>

            <li class="nav-item active">
                <a class="nav-link" href="{{ url_for('stats')}}">Stats</a>
            </li>
            </ul>
        </div>
    </nav>
    <div class="container">
        {% for message in get_flashed_messages() %}
            <div class="alert alert-danger">{{ message }}</div>
        {% endfor %}
        {% block content %} {% endblock %}
    </div>

    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
  </body>
</html>

Here you incorporate a new <li> item to the navigation bar. You use the url_for() function to link to the stats() view function. You can now access the statistics page from the navigation bar.

Your statistics page shows information about each URL, including its shorter equivalent and how many times it has been visited.

You can reuse this code for monitoring number of clicks in other contexts, such as keeping track of how many times a post has been liked or updated on a social media site or how many times a photo/video has been viewed.

You can access the full code for the application from this repository.

Conclusion

You have created a Flask application that allows users to enter a long URL and generate a shorter version. You have transformed integers into short string hashes, redirected users from one link to another, and set up a page for statistics so you can monitor shortened URLs. For further projects and tutorials on working with Flask, check out the following tutorials:

• How To Use One-to-Many Database Relationships with Flask and SQLite.
• How To Modify Items in a One-to-Many Database Relationship with Flask and SQLite.
• How To Use Many-to-Many Database Relationships with Flask and SQLite.
• How To Add Authentication to Your App with Flask-Login.