Build URL Shortener with Flask MySQL

Introduction

This project guides you through creating a simple URL shortener service using Flask and MySQL. You'll learn to set up a database, design a web interface, and implement functionality to shorten URLs, search for URLs by tags, and view analytics. The project is beginner-friendly and offers a comprehensive insight into web development with Python and database management.

The project is based on https://github.com/highoncarbs/shorty, which is originally licensed under the MIT license.

👀 Preview

Convert the URL with/without custom suffix, Search URLs by tag, Access the link:

View information about the operating system and platform used to access the link:

🎯 Tasks

In this project, you will learn:

• How to connect a Flask application to a MySQL database
• How to create and manage a MySQL database schema for storing URL information
• How to implement frontend web pages using HTML and CSS to interact with the backend
• How to handle form data and requests in Flask to create short URLs
• How to develop functionality to redirect users from a short URL to the original URL
• How to create a simple analytics feature to track URL usage, including click counts and basic browser/platform information
• How to design user-friendly error handling and custom 404 pages for better user experience

🏆 Achievements

After completing this project, you will be able to:

• Perform basic operations with MySQL, including database and table creation, data insertion, and querying
• Understand the fundamentals of Flask, including routing, request handling, and template rendering
• Work with HTML forms and process data in a Flask application
• Apply basic frontend design principles and use CSS to create a visually appealing web interface
• Implement simple analytics to gather and display data about URL usage
• Implement best practices for error handling in web applications to improve reliability and user experience

Skills Graph

Set Up the Project Environment

First, we need to set up our project environment in terminal. This involves installing the required Python package for MySQL connectivity and starting the MySQL service. Once the service is up, we will create a database and a table to store our URLs.

Install PyMySQL:

pip install PyMySQL==1.1.0

Start the MySQL service and log into the MySQL shell:

sudo service mysql start
mysql -u root

Inside the MySQL shell, execute the following commands to create the database and the table:

Create database:

CREATE DATABASE IF NOT EXISTS SHORTY;
USE SHORTY;

Create table:

CREATE TABLE IF NOT EXISTS WEB_URL
(
    ID             INT AUTO_INCREMENT,
    URL            VARCHAR(512),
    S_URL          VARCHAR(80),
    TAG            VARCHAR(80),
    COUNTER        INT DEFAULT 0,
    CHROME         INT DEFAULT 0,
    FIREFOX        INT DEFAULT 0,
    SAFARI         INT DEFAULT 0,
    OTHER_BROWSER  INT DEFAULT 0,
    ANDROID        INT DEFAULT 0,
    IOS            INT DEFAULT 0,
    WINDOWS        INT DEFAULT 0,
    LINUX          INT DEFAULT 0,
    MAC            INT DEFAULT 0,
    OTHER_PLATFORM INT DEFAULT 0,
    PRIMARY KEY (ID)
);

This SQL statement creates a table named WEB_URL in the SHORTY database, designed to store information related to shortened URLs and some associated analytics.

• CREATE TABLE IF NOT EXISTS WEB_URL: This command creates a new table named WEB_URL only if it doesn't already exist in the database. This helps avoid errors when the script is run multiple times.
• ID INT AUTO_INCREMENT: This column is designated as the primary key for the table and is set to automatically increment with each new entry. This means that every time a new record is added, MySQL will automatically assign it a unique ID, incrementing by 1 from the last.
• URL VARCHAR(512): This column stores the original URLs that are being shortened. The data type VARCHAR(512) means it can hold strings of variable length, up to 512 characters.
• S_URL VARCHAR(80): This is the column for the shortened URL string, with a maximum length of 80 characters.
• TAG VARCHAR(80): This column is intended to store tags associated with the URLs for categorization or search purposes, with a maximum length of 80 characters.
• COUNTER INT DEFAULT 0: This integer column is likely used to track the number of times a shortened URL has been accessed. It defaults to 0 upon the creation of a new record.
  The next several columns are designed to hold analytics data for the shortened URLs:
• CHROME, FIREFOX, SAFARI, OTHER_BROWSER: These columns are used to track the number of visits from different web browsers. Each is an integer column that defaults to 0.
• ANDROID, IOS, WINDOWS, LINUX, MAC, OTHER_PLATFORM: Similar to the browser columns, these are intended to track visits from different operating systems/platforms, with each column defaulting to 0.
• PRIMARY KEY (ID): This part of the statement specifies that the ID column is the primary key of the table. A primary key is a unique identifier for each record in the table, ensuring that no two records have the same ID.

This table structure allows for storing, retrieving, and analyzing data about shortened URLs, including how often they're accessed, from which browsers, and from which operating systems.

To exit the MySQL shell, you can run the following command:

EXIT;

Create the Index Page Template

The index page is where users will interact with the URL shortener. We'll use HTML and Flask's templating language to create dynamic content.

In templates/index.html, add the following HTML code:

<!doctype html>
<html>
  <head>
    <title>Shorty</title>
    <!-- Add Local SVG image when hosting. -->
    <link
      href="https://fonts.googleapis.com/icon?family=Material+Icons"
      rel="stylesheet"
    />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/clipboard.js/1.7.1/clipboard.min.js"></script>
    <link rel="stylesheet" type="text/css" href="../static/skeleton.css" />
    <link rel="stylesheet" type="text/css" href="../static/normalize.css" />
    <link rel="stylesheet" type="text/css" href="../static/main.css" />
    <link
      href="https://fonts.googleapis.com/css?family=Roboto:300,400,700,900"
      rel="stylesheet"
    />
  </head>
  <body>
    <div class="container  main_header">
      <h3 align="left"><a href="{{url_for('index')}}">Shorty</a></h3>
      <p>A very simple URL shortening service.</p>
    </div>

    <div class="u-full-width shorty">
      <div class="container"></div>
    </div>
  </body>
</html>

Here's an overview of its key components:

• Google Material Icons: The <link> tag imports the Google Material Icons library, allowing the use of predefined icons for a more engaging user interface.
• Clipboard.js: The <script> tag includes the Clipboard.js library, a popular JavaScript library for copying content to the clipboard. This can be useful for a URL shortener, allowing users to easily copy shortened URLs.
• Stylesheets: The template links to several CSS files for styling:
  • skeleton.css: A lightweight CSS framework that provides basic styling and a responsive grid system.
  • normalize.css: Resets browser default styles to maintain consistent styling across different browsers.
  • main.css: Contains custom styles specific to the "Shorty" service.
• Google Fonts: Another <link> tag imports the "Roboto" font family from Google Fonts, providing a variety of font weights for typography design.
• Inside the <body>, a div with the class container main_header is used to create a header section, which includes:
  • A heading (<h3>) containing a link (<a>) that redirects to the index page, facilitated by Flask's url_for function, dynamically generating the URL for the 'index' route.
  • A paragraph (<p>) describing the service as "A very simple URL shortening service."

Implement URL Shortening and Tag-Based Search

In this step, we will enhance the web interface by integrating two key features: URL shortening and tag-based URL search. This will allow users to not only shorten URLs but also organize and retrieve them efficiently using tags.

URL Shortening

First, we will create a form on the index page where users can input the URL they wish to shorten. Optional fields for custom suffixes and tags will allow for personalized short URLs and categorization for easier management.

Add the following code to templates/index.html:

<!-- Search URL block -->
<div class="search_url_block">
  <form method="post" action="/search" name="search_tag_block">
    <input type="text" name="search_url" placeholder="Search tags " />
    <button type="submit" class="button-primary search_url_btn" value="Search">
      Search
    </button>
  </form>
</div>
<!-- end block -->

The first block of code adds a form designed for searching URLs by their associated tags:

• search_url_block: This container holds the search form, making it a distinct section on the page for better organization and styling.
• <form>: Defines a form that submits a POST request to the /search endpoint when the search button is clicked. This form is named search_tag_block for identification.
• <input>: A text input field where users can enter tags they want to search for. The placeholder attribute provides a hint to the user about the field's purpose.
• <button>: A submit button that initiates the form submission. The class button-primary likely adds specific styling defined in the CSS.

Tag-Based Search

To complement the URL shortening feature, we will also implement a functionality that allows users to search for URLs by tags. This feature will be integrated into the index template, providing a simple form where users can input tags to find corresponding URLs.

Add the following code to templates/index.html:

<!-- URL Input block -->
<form method="post" action="" name="generate_block">
  <div class="row">
    <input type="text" name="url_input" placeholder="Enter URL" />
    <input type="text" name="url_custom" placeholder="Enter Custom Suffix" />
    <input type="text" name="url_tag" placeholder="Tag URL" />
    <button class="button-primary generate">Generate</button>
  </div>
</form>
<!-- end block -->

The second block of code introduces a form for users to shorten URLs, with additional fields for customization:

• The entire form is wrapped in a <form> element with method="post" and an unspecified action attribute. This means the form data will be sent via POST request to the current URL when the generate button is clicked.
• Inside the form, a <div> with the class row likely helps with layout and alignment, ensuring the input fields and button are properly organized.
• Three <input type="text"> elements are provided for different purposes:
  • The first input field is for users to enter the original URL they wish to shorten.
  • The second allows for an optional custom suffix for the shortened URL, enabling users to personalize their short links.
  • The third field is designated for tags, allowing users to categorize or add descriptive labels to their shortened URLs for easier retrieval and management.
• A <button> with the class button-primary and text "Generate" serves as the form's submission button. Clicking this button sends the data to the server to create a shortened URL with the provided information.

These enhancements significantly improve the user experience by offering more functionality beyond basic URL shortening. The ability to search for URLs by tags allows for efficient management and retrieval of shortened links, while the option to add custom suffixes and tags enables personalization and categorization of URLs. This setup leverages HTML forms to collect user input, which will then be processed by the server-side Flask application to perform the desired actions.

Handle Errors and Display Shortened URLs

In this step, we focus on enhancing user experience by handling error messaging and displaying the shortened URLs.

Handle Errors

Firstly, we add functionality to display error messages to users. This is crucial for providing feedback when something goes wrong, such as when a user submits an invalid URL or a tag that doesn't exist. Proper error handling makes the application more robust and user-friendly.

Add the following code to templates/index.html:

<!-- Error Display block -->
{% if error != '' %}
<p class="error_disp">{{error}}</p>
{% endif %} {% if shorty_url %}
<!-- end block-->

This block is designed to provide feedback to the user if an error occurs during the URL shortening process:

• {% if error != '' %}: This is a conditional statement using Flask's templating syntax, Jinja2. It checks if the error variable is not empty. The error variable is expected to be passed from the Flask backend to the template. If there's an error message, the condition evaluates to True.
• <p class="error_disp">{{error}}</p>: When the condition is True, this paragraph element is rendered, displaying the error message contained in the error variable. The class error_disp is likely used for styling the error message, making it visually distinct to draw the user's attention.
• {% endif %}: This closes the conditional block. If the error variable is empty, nothing from this block is rendered on the page.

Display Shortened URLs

Secondly, after a URL is successfully shortened, we display the shortened URL to the user. This involves creating a section on the index page that dynamically shows the shortened URL after the form submission, complete with a copy-to-clipboard button for easy sharing.

Add the following code to templates/index.html:

<!-- URL Generator Display block -->
<div class="gen_block">
  <p class="gen_url">
    Shorty URL is
    <b><a id="short-url" href="{{shorty_url}}">{{shorty_url}}</a></b>
  </p>
  <button
    class="button-primary copy-btn"
    data-clipboard-action="copy"
    data-clipboard-target="#short-url"
  >
    Copy
  </button>
</div>
{% endif %}
<!-- end block -->

This block is shown when a URL has been successfully shortened and provides a way for users to copy the shortened URL easily:

• {% if shorty_url %}: Another conditional statement that checks if the shorty_url variable exists and is not empty. This variable should hold the shortened URL generated by the service.
• <p class="gen_url">: This paragraph displays a message indicating the shortened URL. Within it, <a id="short-url" href="{{shorty_url}}">{{shorty_url}}</a> creates a hyperlink with the shortened URL as both the link text and the href attribute, allowing users to click on it directly.
• <button>: This button is designed to copy the shortened URL to the clipboard for easy sharing. It uses classes for styling (button-primary) and functionality (copy-btn). The data-clipboard-action attribute specifies the action to perform (copying), and data-clipboard-target indicates the target element to copy from (the shortened URL link). This setup assumes the integration of Clipboard.js (or a similar library) to handle the copy functionality.
• {% endif %}: Closes the conditional block for displaying the shortened URL and the copy button. If shorty_url is not set or is empty, this section will not be rendered.

These enhancements contribute significantly to the usability of the URL shortener service. Error messaging provides essential feedback, guiding users to correct mistakes or informing them of issues, while the display of shortened URLs with an easy copy function allows for a seamless experience in creating and sharing shortened links.

List and Manage Shortened URLs

The final step is to create a section that lists all shortened URLs stored in the database, along with their original URLs, the number of clicks they've received, and links to detailed analytics. This feature enables users to view and manage their shortened URLs in one place, making the application more functional and user-friendly.

Add the following code to templates/index.html:

        <!-- URL List Block -->
        {% if not error %}
        {% if not shorty_url %}
        <!-- Add Empty list case -> 'Wow . Such Empty!' -->
        <div style="overflow-x:auto;">
            <div class="table_list u-full-width">
                <table>
                    <thead>
                    <tr>
                        <th>Original URL</th>
                        <th>Short URL</th>
                        <th>Clicks</th>
                        <th>Info</th>
                    </tr>
                    </thead>

                    <tbody>
                    {% for url in table %}
                    <tr>
                        <td style="padding-left: 5px;">{{url[1]}}</td>
                        <td><a href="{{host+url[2]}}">{{ host+url[2]}}</a></td>
                        <td style="text-align: center;">{{url[4]}}</td>
                        <td id="info"><a href=" {{url_for('analytics' ,short_url=url[2])}} "><i class="material-icons">info_outline</i></a>
                        </td>
                    </tr>

                    {% endfor %}
                    </tbody>
                </table>
            </div>
        </div>
        {% endif %}
        {% endif %}
    </div>
</div>
<script type="text/javascript">
    var clipboard = new Clipboard('.copy-btn');
</script>
</html>

This code is designed to list all shortened URLs stored in the database, providing information such as the original URL, the shortened URL, the number of clicks, and links to more detailed analytics. Here's a detailed breakdown of its components:

• The block starts with a conditional {% if not error %} to ensure that the list is only displayed when there is no error. This helps in maintaining a clean user interface, especially when error messages need to be displayed.
• Another conditional {% if not shorty_url %} checks if a new shortened URL has not just been generated. This condition might be used to prevent the list from displaying immediately after a new URL is shortened, focusing the user's attention on the newly created shortened URL instead.
• An overflow-x:auto; style is applied to a div to ensure that the table is scrollable horizontally on smaller screens, enhancing responsiveness and usability.
• The table element is structured with a thead section defining column headers for "Original URL", "Short URL", "Clicks", and "Info". This layout helps users understand the data presented.
• The tbody section is dynamically populated with data using a Flask/Jinja2 loop: {% for url in table %}. This loop iterates over a collection of URLs (table) passed from the Flask backend, displaying each URL's data in a new table row (<tr>).
• Within each row, the original URL is displayed in a cell (<td>) with some padding for aesthetics. The shortened URL is presented as a clickable link (<a>), constructed by appending the shortened path (url[2]) to the host name (host), which should also be passed from the backend.
• The "Clicks" column shows the number of times the shortened URL has been accessed, centered for better readability.
• The "Info" column contains a link to the analytics page for each shortened URL. This link is generated using Flask's url_for function, dynamically creating a URL to the analytics route with the shortened URL as a parameter. An icon (<i class="material-icons">info_outline</i>) from the Google Material Icons set is used to represent the analytics link visually.
• The {% endfor %} tag closes the loop, ensuring that a row is created for each URL in the table collection.
• The conditional blocks are closed with {% endif %} tags.
• At the bottom, a <script> tag initializes Clipboard.js for the .copy-btn class, which might have been used in the previous sections (e.g., for copying the shortened URL). This script ensures that any element with the copy-btn class will have the copy-to-clipboard functionality enabled, leveraging the Clipboard.js library.

This final step rounds out the functionality of the "Shorty" URL shortener service by providing users with a comprehensive overview of their shortened URLs, making it easier to manage and analyze their links. The integration of dynamic data rendering with Flask/Jinja2, combined with thoughtful UI design, ensures a user-friendly experience.

Create the Search Page Template

To allow users to search for URLs by tags, we'll need a search page.

In templates/search.html, add the following HTML code:

<!DOCTYPE html>
<html>
<head>
    <title>Shorty</title>
    <link href="https://fonts.googleapis.com/icon?family=Material+Icons"
          rel="stylesheet">
    <link rel="stylesheet" type="text/css" href="../static/skeleton.css">
    <link rel="stylesheet" type="text/css" href="../static/normalize.css">
    <link rel="stylesheet" type="text/css" href="../static/main.css">
    <link href="https://fonts.googleapis.com/css?family=Roboto:300,400,700,900" rel="stylesheet">
</head>
<body>
<div class="container main_header">
    <h3 align="left"><a href="{{url_for('index')}}">Shorty</a></h3>
    <p>A dead simple URL shortener service.</p>
</div>
<!-- Search results  -->
<div class=" container search_header">
    <h4>Search Results for : <b> {{ search_tag }} <b></h4>

    <div style="overflow-x:auto;">
        <div class="table_list u-full-width">
            <table>
                <thead>
                <tr>
                    <th>Original URL</th>
                    <th>Short URL</th>
                    <th>Clicks</th>
                    <th>Info</th>

                </tr>
                </thead>

                <tbody>
                {% for url in table %}
                <tr>
                    <td style="padding-left: 5px;">{{url[1]}}</td>
                    <td><a href="{{ host+url[2]}}">{{host+url[2]}}</a></td>
                    <td style="text-align: center;">{{url[4]}}</td>
                    <td id="info"><a href=" {{url_for('analytics' ,short_url=url[2])}} "><i class="material-icons">info_outline</i></a>
                    </td>
                </tr>
                {% endfor %}
                </tbody>
            </table>
        </div>
    </div>
</div>
</body>
</html>

This code creates a template for the search results page in the "Shorty" URL shortener service. This page is designed to display the results of a user's search for URLs by tags. Here's a detailed breakdown of the template:

• Within the <body>, the header section (<div class="container main_header">) mirrors the index page, providing a consistent look and feel. It includes the service name ("Shorty") as a clickable link that redirects to the index page ({{url_for('index')}}) and a brief description of the service.
• The <div class="container search_header"> section introduces the search results area, starting with a header (<h4>) that dynamically displays the tag used for the search ({{ search_tag }}).
• The results are presented in a table within a div styled for horizontal scrolling (overflow-x:auto;), ensuring accessibility on devices with smaller screens.
• The table structure (<table>) consists of a header (<thead>) and body (<tbody>) sections. The header defines columns for "Original URL", "Short URL", "Clicks", and "Info", similar to the list displayed on the index page.
• The body of the table is populated dynamically using a loop ({% for url in table %}), iterating over a collection of URLs (table) passed from the Flask backend. Each iteration creates a new row (<tr>) in the table for a URL:
  • The original URL is displayed with some padding for better readability.
  • The shortened URL is presented as a clickable link, constructed by appending the shortened path to the host name, both of which are dynamically inserted using Flask's templating syntax.
  • The "Clicks" column shows how many times the shortened URL has been accessed, with the text centered for clarity.
  • The "Info" column provides a link to detailed analytics for each URL, using Flask's url_for function to generate the URL dynamically and an icon (<i class="material-icons">info_outline</i>) for visual representation.

This search results template enhances the "Shorty" service by allowing users to effectively find and manage URLs based on tags, providing a seamless and intuitive user experience. The consistent use of styles and layout elements from the index page ensures a cohesive design throughout the application.

Create the Analytics Page Template

The analytics page will display detailed information about the usage of a short URL, including clicks, browser, and platform statistics.

In templates/data.html, add the following HTML code:

<!doctype html>
<html>
  <head>
    <title>Shorty</title>

    <!-- Add Local SVG image when hosting. -->
    <link
      href="https://fonts.googleapis.com/icon?family=Material+Icons"
      rel="stylesheet"
    />

    <link rel="stylesheet" type="text/css" href="../static/skeleton.css" />
    <link rel="stylesheet" type="text/css" href="../static/normalize.css" />
    <link rel="stylesheet" type="text/css" href="../static/main.css" />
    <link
      href="https://fonts.googleapis.com/css?family=Roboto:300,400,700,900"
      rel="stylesheet"
    />
  </head>
  <body>
    <div class="container  main_header">
      <h3 align="left"><a href="{{url_for('index')}}">Shorty</a></h3>
      <p>A dead simple URL shortener service.</p>
    </div>
    <div class=" container modal-content">
      <!-- Array index -> 
      broswer : 
          CHROME ,
          FIREFOX,
          SAFARI,
          OTHER_BROWSER,
      platform:
          ANDROID,
          IOS,
          WINDOWS,
          LINUX ,
          MAC,
          OTHER_PLATFORM
       -->
      <div class="url_info">
        <h4>
          Analytics data for :
          <a href="{{host+info[1]}}">{{'localhost/'+info[1]}}</a>
        </h4>
        <p>
          Original URL :
          <a style="text-decoration: none" href="{{info[0]}}">{{info[0]}}</a>
        </p>
      </div>
      <div class="data_block">
        <div class="browser_list">
          <h4>Browser</h4>
          <table>
            <thead>
              <tr>
                <th>Chrome</th>
                <th>Firefox</th>
                <th>Safari</th>
                <th>Other Broswers</th>
              </tr>
            </thead>

            <tbody>
              <tr>
                <td style="padding-left: 5px;">{{browser[0]}}</td>
                <td>{{browser[1]}}</td>
                <td>{{browser[2]}}</td>
                <td>{{browser[3]}}</td>
              </tr>
            </tbody>
          </table>
        </div>
        <div class="platform_list">
          <h4>Platform</h4>
          <table>
            <thead>
              <tr>
                <th>Android</th>
                <th>IOS</th>
                <th>Windows</th>
                <th>Linux</th>
                <th>Mac</th>
                <th>Other Platforms</th>
              </tr>
            </thead>
            <tbody>
              <tr>
                <td style="padding-left: 5px;">{{platform[0]}}</td>
                <td>{{platform[1]}}</td>
                <td>{{platform[2]}}</td>
                <td>{{platform[3]}}</td>
                <td>{{platform[4]}}</td>
                <td>{{platform[5]}}</td>
              </tr>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </body>
</html>

This code includes information about the number of clicks, browser usage, and platform distribution. Here's a breakdown of its structure and components:

• A div with the classes container modal-content encapsulates the analytics information, styled as a modal or a distinct content block within the page.
• The url_info div presents:
  • An <h4> heading indicating that the following data pertains to analytics for a specific shortened URL. The shortened URL is displayed as a clickable link, constructed using the host and info[1] variables, where info[1] contains the shortened path.
  • A paragraph (<p>) showing the original URL as a clickable link, facilitating access to the original content.
• Two separate sections, browser_list and platform_list, each contain a table displaying the usage statistics for browsers and platforms, respectively:
  • The browser_list section includes a table with headers for Chrome, Firefox, Safari, and Other Browsers. The <tbody> section displays the corresponding statistics, which are pulled from the browser array variable passed to the template.
  • Similarly, the platform_list section has a table for platform usage, with headers for Android, iOS, Windows, Linux, Mac, and Other Platforms. Usage statistics are shown in the table body, sourced from the platform array variable.
• The tables use <thead> for headers and <tbody> for the actual data, ensuring semantic HTML and facilitating styling and accessibility.
• Data cells (<td>) within the tables display the respective counts for each browser and platform, aligned and styled for readability.

This template effectively communicates the analytics data to the user, offering insights into how the shortened URL is being accessed, including the browsers and platforms used by the audience. The clean layout, combined with the clear separation of data into distinct sections, makes it easy for users to understand and interpret their URL's performance.

Create the 404 Page Template

A custom 404 page improves the user experience by providing a more helpful error message when a short URL is not found.

In templates/404.html, add the following HTML code:

<!doctype html>
<html>
  <head>
    <title>Shorty</title>

    <!-- Add Local SVG image when hosting. -->
    <link
      href="https://fonts.googleapis.com/icon?family=Material+Icons"
      rel="stylesheet"
    />

    <link rel="stylesheet" type="text/css" href="../static/skeleton.css" />
    <link rel="stylesheet" type="text/css" href="../static/normalize.css" />
    <link rel="stylesheet" type="text/css" href="../static/main.css" />
    <link
      href="https://fonts.googleapis.com/css?family=Roboto:300,400,700,900"
      rel="stylesheet"
    />
  </head>
  <body>
    <div class="lost">
      <h2>Oi , chap you seem lost !</h2>
    </div>
  </body>
</html>

This custom 404 page template effectively communicates to users that the page they're looking for doesn't exist, while maintaining a light-hearted tone.

Design the Web Interface

Next, we'll design the web interface. Start by creating the main CSS file to style our application.

In static/main.css, add the following CSS code to style the HTML elements:

html {
  border-top: 5px solid #d9edf7;
}
body {
  font-family: "Roboto", sans-serif;
  margin-top: 50px;
  margin-bottom: 0;
}
h3 {
  padding: 0;
  margin: 0;
}
h3 a {
  font-weight: 700;
  text-decoration: none;
  color: black;
}
h3 a:hover {
  color: grey;
  transition: 0.2s all;
}
/** Main Header */
.main_header {
  margin-bottom: 20px;
}
/* Search Block */
.search_url_block {
  padding: 15px;
  background-color: #d9edf7;
  color: #31708f;
  border-radius: 5px;
  margin-bottom: 10px;
}
form {
  margin: 0;
}

.search_header {
  margin-top: 20px;
}

.material-icons {
  padding: 5px;
  padding-top: 7px;
  opacity: 0.7;
}

.material-icons:hover {
  opacity: 1;
}
/* Gen URL block */

.gen_block {
  margin-top: 10px;
  padding: 15px;
  background-color: #dff0d8;
  color: #2b542c;
  border-radius: 5px;
  width: auto;
  font-size: 20px;
}
/* Error Disp */
.error_disp {
  padding: 15px;
  border-radius: 5px;
  background-color: #fcf8e3;
  color: #b84442;
  width: auto;
  font-size: 20px;
  margin-top: 10px;
}

/* Table Display Block*/

.table_list {
  padding-top: 10px;
  margin-top: 40px;
  border-top: 2px solid lightgrey;
}
table {
  font-size: 20px;
  width: 100%;
}
thead {
  font-weight: 700;
  padding: 2px;
}
tbody {
  font-weight: 400;
}
th {
  padding: 5px;
}
td {
  padding: 5px;
}
tr {
  padding: 5px;
}
tbody tr:hover {
  background-color: #f5f5f5;
  transition: 0.1s all ease-out;
}

/* Analytics block*/
.url_info {
  margin-top: 10px;
  padding: 15px;
  background-color: #d9edf7;
  color: #31708f;
  border-radius: 5px;
  margin-bottom: 10px;
}
.url_info h4,
p {
  margin: 0;
  padding: 0;
}
.data_block {
  margin-top: 20px;
}

/* 404 . Lost*/
.lost {
  margin-top: 20px;
}
.lost h2 {
  font-weight: 700;
  font-size: 40px;
  text-align: center;

  color: #31708f;
}
.lost p {
  font-weight: 400;
  font-size: 20px;
  text-align: center;
}

The overall design uses a combination of background colors, text colors, and padding to create a clean, modern interface that's easy to navigate. The use of border-radius across various elements gives the interface a softer, more approachable feel, while hover effects enhance interactivity. The consistent use of the Roboto font maintains a cohesive look throughout the application.

Database Connection and Operate Functions

Utility functions will handle database connections, URL validations, update browser and platform counters, and generating random tokens for short URLs.

Database Connection

First, you'll set up the database connection using pymysql. This is crucial for enabling your Flask application to interact with the MySQL database.

In utils.py, add the following Python code:

from urllib.parse import urlparse
import random
import string

import pymysql


db_config = {
    "host": "localhost",
    "user": "root",
    "password": "",
    "db": "SHORTY"
}


def get_db_connection() -> pymysql.Connection:
    """Create and return a new database connection."""
    return pymysql.connect(**db_config)

• The pymysql library is used for connecting to the MySQL database. This library allows Python applications to interact with MySQL databases using a simple API.
• The db_config dictionary contains database connection parameters such as host, user, password, and database name. These are imported from a separate config module, presumably for better security and modularity. Keeping configuration settings separate allows for easier adjustments without modifying the main codebase.
• The get_db_connection() function creates and returns a new connection to the database using the pymysql.connect(**db_config) function. The **db_config syntax is used to unpack the dictionary into keyword arguments.

Database Operate Functions

After establishing a database connection, the next step involves creating functions that perform operations on the database. These include listing data for a given short URL and updating counters based on browser and platform usage.

In utils.py, add the following Python code:

def list_data(shorty_url: str) -> tuple:
    """
    Takes short_url for input.
    Returns counter , browser , platform ticks.
    """
    with get_db_connection() as conn, conn.cursor() as cursor:

        su = [shorty_url]
        info_sql = "SELECT URL , S_URL ,TAG FROM WEB_URL WHERE S_URL= %s; "
        counter_sql = "SELECT COUNTER FROM WEB_URL WHERE S_URL= %s; "
        browser_sql = "SELECT CHROME , FIREFOX , SAFARI, OTHER_BROWSER FROM WEB_URL WHERE S_URL =%s;"
        platform_sql = "SELECT ANDROID , IOS , WINDOWS, LINUX , MAC , OTHER_PLATFORM FROM WEB_URL WHERE S_URL = %s;"

        cursor.execute(info_sql, su)
        info_fetch = cursor.fetchone()
        cursor.execute(counter_sql, su)
        counter_fetch = cursor.fetchone()
        cursor.execute(browser_sql, su)
        browser_fetch = cursor.fetchone()
        cursor.execute(platform_sql, su)
        platform_fetch = cursor.fetchone()

    return info_fetch, counter_fetch, browser_fetch, platform_fetch

def update_counters(cursor: pymysql.Connection, short_url: str, browser_dict: dict, platform_dict: dict) -> None:
    """Update browser and platform counters in the database for the given short_url."""
    counter_sql = """UPDATE WEB_URL SET COUNTER = COUNTER + 1,
                     CHROME = CHROME + %s, FIREFOX = FIREFOX + %s, SAFARI = SAFARI + %s, OTHER_BROWSER = OTHER_BROWSER + %s,
                     ANDROID = ANDROID + %s, IOS = IOS + %s, WINDOWS = WINDOWS + %s, LINUX = LINUX + %s, MAC = MAC + %s, OTHER_PLATFORM = OTHER_PLATFORM + %s
                     WHERE S_URL = %s;"""
    cursor.execute(counter_sql, (browser_dict['chrome'], browser_dict['firefox'], browser_dict['safari'], browser_dict['other'],
                                 platform_dict['android'], platform_dict['iphone'], platform_dict[
                                     'windows'], platform_dict['linux'], platform_dict['macos'], platform_dict['other'],
                                 short_url))

• The list_data(shorty_url) function is designed to retrieve various pieces of information from the WEB_URL table for a given short URL (shorty_url). It establishes a connection to the database and executes four SQL queries to fetch the original URL, the associated tag, the access counter, browser, and platform analytics. Each query is executed separately, and the results are fetched and returned.
• The update_counters(cursor, short_url, browser_dict, platform_dict) function updates the access counter, browser, and platform counts for a given short URL in the database. This function takes a database cursor, the short URL, and two dictionaries containing counts for each browser and platform as arguments. It constructs an UPDATE SQL statement to increment the counters for the short URL based on the provided data. The function assumes that the browser and platform information has been determined prior to calling this function, and that this information is passed in as dictionaries (browser_dict and platform_dict), with keys corresponding to the columns in the WEB_URL table.

This setup is a typical pattern for web applications using Flask and MySQL, where utility functions are defined for common database operations. These functions can then be imported and used throughout the application to interact with the database, abstracting away the direct database access and SQL queries from the main application logic.

Implement Non-Database Utility Functions

Lastly, implement utility functions that do not interact with the database. These include generating random tokens for short URLs and validating URLs.

In utils.py, add the following Python code:

def random_token(size: int = 6) -> str:
    """
    Generates a random string of 6 chars , use size argument
    to change the size of token.
    Returns a valid token of desired size ,
    *default is 6 chars
    """
    BASE_LIST = string.digits + string.ascii_letters

    token = ''.join((random.choice(BASE_LIST)) for char in range(size))
    return token


def url_check(url: str) -> bool:
    """
    Expects a string as argument.
    Retruns True , if URL is valid else False.
    For detailed docs look into urlparse.
    """
    try:
        result = urlparse(url)
        if all([result.scheme, result.netloc]):
            return True
        else:
            return False
    except:
        return False

• The random_token function generates a random string, which serves as a token for creating short URLs. By default, it creates a token of 6 characters but can be adjusted with the size argument.
  • It uses a combination of digits (string.digits) and letters (string.ascii_letters) as the base list for generating the token.
  • The token is generated by randomly selecting characters from the BASE_LIST for the number of times specified by the size parameter. This is done using a list comprehension and random.choice(), which selects a random element from the specified sequence.
  • The generated token is then returned as a string. This token can be used as the short URL identifier.
• The url_check function validates a given URL to ensure it's in a proper format.
  • It uses the urlparse function from the urllib.parse module to parse the given URL into components.
  • The function checks if the URL has both a scheme (like http, https) and a netloc (network location, e.g., www.example.com). Both are essential components for a URL to be considered valid.
  • If both components are present, the function returns True, indicating that the URL is valid. If either is missing, or if an exception occurs during parsing (for example, if the input is not a string), the function returns False.
  • This validation is important to ensure that only valid URLs are processed and stored by the application.

These utility functions are integral to the URL shortening service, providing essential capabilities for generating unique identifiers for shortened URLs and ensuring that only valid URLs are accepted by the system.

Init and Implement Index Route

Finally, we'll build the core of our URL shortener—the Flask application.

Start by setting up the index route. This route will serve the main page and handle both GET and POST requests. On a GET request, it displays all shortened URLs. On a POST request, it accepts a new URL to shorten, generates a unique token, and saves it to the database.

In app.py, add the following Python code:

from flask import Flask, request, redirect, render_template, make_response

from utils import get_db_connection, list_data, random_token, update_counters, url_check


app = Flask(__name__)


@app.route('/', methods=['GET', 'POST'])
def index():
    with get_db_connection() as conn, conn.cursor() as cursor:
        ## Fetch all data to display on index
        cursor.execute("SELECT * FROM WEB_URL;")
        result_all_fetch = cursor.fetchall()

        if request.method == 'POST':
            og_url = request.form.get('url_input')
            custom_suff = request.form.get('url_custom', '')
            tag_url = request.form.get('url_tag', '')

            token_string = random_token() if not custom_suff else custom_suff

            if og_url and url_check(og_url):
                cursor.execute(
                    "SELECT S_URL FROM WEB_URL WHERE S_URL = %s FOR UPDATE", (token_string,))
                if cursor.fetchone() is None:
                    cursor.execute(
                        "INSERT INTO WEB_URL(URL, S_URL, TAG) VALUES(%s, %s, %s)", (og_url, token_string, tag_url))
                    conn.commit()
                    return render_template('index.html', shorty_url=f"{shorty_host}{token_string}")
                else:
                    error = "The custom suffix already exists. Please use another suffix or leave it blank for a random one."
            else:
                error = "Invalid URL provided. Please enter a valid URL."

            return render_template('index.html', table=result_all_fetch, host=shorty_host, error=error)

        return render_template('index.html', table=result_all_fetch, host=shorty_host)

The index function within app.py sets up the core functionality of the URL shortener service using Flask, a micro web framework written in Python. This function defines how the index route (/) of the application behaves and processes both GET and POST requests. Here's a detailed explanation of the code:

Flask Setup

• The Flask application is initialized with app = Flask(__name__).
• The @app.route('/', methods=['GET', 'POST']) decorator specifies that the index function handles requests to the root URL (/) and accepts both GET and POST methods.

Handling GET Requests

• When the function receives a GET request, it connects to the database using the get_db_connection() utility function and fetches all records from the WEB_URL table. This is to display all shortened URLs on the index page.
• The fetched records are passed to the render_template function, along with the host URL (shorty_host), to render the index.html template. This template likely includes a table or list to display each shortened URL and its details.

Handling POST Requests

• For POST requests, typically submitted from a form on the index page, the function extracts the original URL (og_url), an optional custom suffix (custom_suff), and an optional tag (tag_url) from the form data.
• It then generates a random token for the shortened URL using random_token() unless a custom suffix is provided. This token (or custom suffix) serves as the unique identifier for the shortened URL.
• Before proceeding, the function validates the original URL using url_check(og_url). If the URL is valid and the token/suffix is unique (not already in the database), a new record is inserted into the WEB_URL table with the original URL, the token/suffix as the short URL, and the tag.
• If the operation is successful, the function re-renders the index.html template, displaying the newly shortened URL (shorty_url) along with all existing records.
• If the token/suffix already exists in the database or the original URL is invalid, an error message is set, and the index.html template is rendered with the error message and existing records.

Error Handling and Template Rendering

• The render_template function is used extensively to render the index.html template, passing in various parameters like the list of all records (table), the base host URL for the shortened links (host), the newly shortened URL (shorty_url), and any error messages (error). This function facilitates dynamic content rendering in Flask by interpolating Python variables into HTML templates.

This Flask application serves as the backend for the URL shortener service, handling database interactions, URL validation, and token generation, while also dynamically generating web pages based on user interactions and database contents.

Implement Redirection Route and Analytics Route

Redirection Route

Next, create a route to handle redirection from a short URL to the original URL. This route captures any short URL, looks it up in the database, and redirects the user to the original URL.

Add the following code to app.py:

@app.route('/<short_url>')
def reroute(short_url):
    with get_db_connection() as conn, conn.cursor() as cursor:
        platform = request.user_agent.platform or 'other'
        browser = request.user_agent.browser or 'other'
        browser_dict = {'firefox': 0, 'chrome': 0, 'safari': 0, 'other': 0}
        platform_dict = {'windows': 0, 'iphone': 0,
                         'android': 0, 'linux': 0, 'macos': 0, 'other': 0}

        ## Increment browser and platform counters
        browser_dict[browser] = browser_dict.get(browser, 0) + 1
        platform_dict[platform] = platform_dict.get(platform, 0) + 1

        cursor.execute(
            "SELECT URL FROM WEB_URL WHERE S_URL = %s;", (short_url,))
        try:
            new_url = cursor.fetchone()[0]
            update_counters(cursor, short_url, browser_dict, platform_dict)
            conn.commit()
            return redirect(new_url)
        except Exception:
            return render_template('404.html'), 404

• The @app.route('/<short_url>') decorator creates a dynamic route that matches any path segment following the root URL. This segment (short_url) is passed to the reroute function as an argument.
• Inside the function, it establishes a database connection and initializes dictionaries to track the browser and platform from which the request originated. Flask's request.user_agent is used to determine the browser and platform.
• It then executes a SQL query to find the original URL associated with the given short_url. If found, it updates the counters for the browser and platform in the database using the update_counters function.
• After committing the changes to the database, the function uses Flask's redirect function to send the user to the original URL.
• If the short_url is not found in the database, or any other exception occurs, the function renders a 404 error page using render_template('404.html').

Analytics Route

Create an analytics route that displays detailed information about a specific short URL, such as the number of times it was accessed and from which platforms and browsers.

Add the following code to app.py:

@app.route('/analytics/<short_url>')
def analytics(short_url):
    info_fetch, counter_fetch, browser_fetch, platform_fetch = list_data(
        short_url)
    return render_template("data.html", host=shorty_host, info=info_fetch, counter=counter_fetch, browser=browser_fetch, platform=platform_fetch)

• The @app.route('/analytics/<short_url>') decorator defines a route for accessing analytics about a specific short URL. The short_url is captured from the URL path and passed to the analytics function.
• The function calls list_data(short_url), which queries the database for information about the short URL, including the number of times it has been accessed and the distribution of access across different browsers and platforms.
• The fetched data (info_fetch, counter_fetch, browser_fetch, platform_fetch) is then passed to the render_template function along with the host URL (shorty_host) to render the data.html template. This template likely presents the analytics data in a user-friendly format, such as tables or charts.

These routes expand the functionality of the URL shortener service, enabling it not only to redirect users from short URLs to their original destinations but also to provide insights into how those short URLs are being used, including access counts and the types of devices and browsers from which the links are accessed. This information can be valuable for understanding the reach and impact of shared URLs.

Implement Search Route and Run Application

Search Route

Set up a search route to allow users to find shortened URLs by tags. This route handles both GET and POST requests, displaying a search form and processing search queries, respectively.

Add the following code to app.py:

@app.route('/search', methods=['GET', 'POST'])
def search():
    s_tag = request.form.get('search_url', '')
    if not s_tag:
        return render_template('index.html', error="Please enter a search term.")

    with get_db_connection() as conn, conn.cursor() as cursor:
        cursor.execute("SELECT * FROM WEB_URL WHERE TAG = %s", (s_tag,))
        search_tag_fetch = cursor.fetchall()
        return render_template('search.html', host=shorty_host, search_tag=s_tag, table=search_tag_fetch)

• The @app.route('/search', methods=['GET', 'POST']) decorator sets up the route at /search, handling both GET and POST requests. While the GET request could be used to display a search form, the POST request processes the form data submitted by users.
• Inside the search function, request.form.get('search_url', '') attempts to retrieve the search term from the submitted form data. The second parameter ('') is the default value if 'search_url' is not found, effectively making the search term an empty string in such cases.
• If no search term is provided (not s_tag), the function redirects the user to the index page with an error message indicating that a search term is required.
• If a search term is provided, the function connects to the database and executes a SQL query to find all records in the WEB_URL table where the TAG column matches the provided search term. This allows users to find all shortened URLs associated with a particular tag.
• The results of this query (search_tag_fetch) are then passed to the render_template function, along with the host URL (shorty_host) and the search term (s_tag), to render the search.html template. This template likely displays the results in a list or table format, allowing users to see all matching records and possibly interact with them (e.g., visit the original URLs, view analytics).

Run Application

Finally, add the code to run your Flask application. This includes setting the host and port for the application to listen on, as well as specifying the environment variables.

Add the following code to the bottom of app.py:

shorty_host = "https://****.labex.io/"

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8080)

Note: You should replace the URL in shorty_host with the URL of the current environment. You can find it by switching to the Web 8080 tab, and the URL should end with /.

Now, you can run the project using the following command:

python app.py

Switch to the "Web 8080" tab and reload the web page to see the following effects.

Convert the URL with/without custom suffix, Search URLs by tag, Access the link:

View information about the operating system and platform used to access the link:

Summary

Congratulations on completing the URL shortener project! This tool simplifies sharing long URLs by creating shorter, more manageable links. However, there are a couple of important considerations to keep in mind:

1. Base URL Length: If the base URL of the shortener itself is long, the resulting shortened URLs may not be as short as expected (as in the project). The total length of the shortened URL includes the base URL, which can limit the effectiveness of the shortener. To achieve the most concise URLs, consider using a shorter base URL or domain name for your service.

2. Website Embedding Restrictions: You might encounter issues like "Refused to display 'https://scholar.google.com/' in a frame because it set 'X-Frame-Options' to 'sameorigin'." This error occurs because some websites use the X-Frame-Options HTTP header to prevent their content from being displayed in iframes on other domains, enhancing security against "clickjacking" attacks. When a site sets this header to sameorigin, it restricts embedding to iframes that share the same origin. This isn't a limitation of the URL shortener but a security measure implemented by the destination website. For seamless redirection, it's best to use the shortened URLs as direct links rather than embedding them in iframes.

These considerations highlight the balance between convenience and security in web development and the importance of user awareness for optimal tool usage.