Data-driven web applications basics: Connecting to a database

In the previous post of the data driven web applications basics, we built a basic Flask application that enabled users to register their favorite websites by inputting a URL and receive a confirmation that the URL had been successfully registered. While this was a useful starting point, the application had a significant limitation: it did not store the registered URLs. As a result, the inputted information was not preserved and could not be accessed or used later. Without a mechanism to save this data, the application lacked practical utility—after all, registering a URL is pointless if it can’t be retrieved and used in the future.

Without a system to store the registered URLs, we cannot, for instance, track which URLs users have submitted, determine which sites are most popular, or analyze user preferences. Therefore, to make our application more useful, we need a way to store information persistently. This requires a reliable storage solution that allows data to be saved, retrieved, and managed efficiently.

Initially, using Excel might seem like a convenient solution—especially for small-scale applications or for users familiar with spreadsheet software. For example, when a user registers a URL, the application could write the URL to a new row in an Excel file. However, this method has significant limitations. Excel is not designed to handle large volumes of data or support simultaneous access and modifications by multiple users. In addition, as the number of records grows, performance can degrade significantly, and Excel files have size limitations that can lead to corruption and potential data loss.

To address these limitations, we turn to databases, which are purpose-built for efficient data management. Databases offer faster read and write operations, support concurrent access by multiple users, and ensure data integrity.

Among the various types of databases, relational databases are the most common due to their intuitive data model. They use a well-defined schema organized in a tabular format with rows and columns, simplifying data organization, querying, and management. Relational databases also support the creation of multiple tables and the establishment of relationships between them. This ability to link data across tables is why they are called “relational.”

Relational databases are typically accessed and manipulated using SQL (Structured Query Language), a standardized language designed specifically for interacting with relational data. As a result, relational databases are often referred to as SQL databases.

To overcome the limitation of data persistence, we will enhance our Flask application by integrating a relational database to store the URLs submitted by users. This means that each time a user registers a URL, it will be saved in an SQL database, ensuring the data is preserved, as illustrated in Figure 1.

Figure 1: Storing user-submitted URLs in a relational database

Integrating a relational database into our application will allow us to leverage the data submitted by users. With this addition, we will use the stored data to identify and showcase the top favorite websites, which we will also refer to as the most popular websites. To achieve this, we will add a new page to the application that retrieves and counts the different registered URLs, displaying the ones registered the most times. This process is schematically illustrated in Figure 2.

Moreover, since our application will now feature multiple pages, and as you may have already spot in the previous Figure, we will add a navigation bar to facilitate easy navigation between pages.

Figure 2: Retrieving user-submitted URLs to create a “Top Favorite Websites” page

In summary, building on the previous post, we will add three additional features to our web application:

1. Incorporation of a database to store the URLs registered by our users.

2. A new page displaying the top favorite websites.

3. A navigation bar to facilitate easy navigation between pages.

Ensuring data validity and security

Given that we will be storing user-generated data, it is crucial to ensure that the data is both valid and secure before it is saved. This means that users should not be able to save arbitrary or incorrect data in our database. Ensuring data validity involves checking that the data conforms to the desired format and requirements. For example, if users are supposed to enter a URL, we need to validate that the input is indeed in a valid URL format before accepting it.

In the previous example, we did not implement any format validation for user input. While our goal was to have users submit URLs, there was no enforcement to ensure that users actually entered a proper URL. As a result, users could input any value or format they wished. For instance, if a user typed “hello” and clicked the “Register URL” button, the application would erroneously confirm that the URL was registered, as illustrated in Figure 3.

Figure 3: Registering a text entry instead of a URL

Creating a function to validate URLs

To address this issue, we need to ensure that only valid URLs are registered. We can achieve this by creating a function that verifies whether the user input is a valid URL. This function will return True if the input is a valid URL and False otherwise. We could name this function is_valid_url(), and its behavior can be summarized as follows:

def is_valid_url(url):
  # Given a value for the parameter 'url'
  # Check if it is a valid URL
  # Return True if it is a valid URL
  # Return False if it is not a valid URL

There are several ways to implement this function, including pattern matching or even pinging the website to verify its existence. For our purpose, we will use pattern matching with regular expressions to determine if the input is a valid URL.

Validating the URL using regex

To check if the input value is a valid URL, we can use regular expressions (regex). Typically, URLs start with http:// or https://, followed by www.. However, it’s also common for URLs to omit the http:// or https://, beginning instead with just www., or even without the www. prefix. We can specify all these variations in regex through the following steps:

1. Detecting the protocol (http or https): The initial part of a URL is often the protocol, which is either http or https. In regex, we can use https? to represent this. The s? means that the s is optional, so the URL may start with either http or https. Essentially, the question mark (?) signals that the character before it (in this case, s) may or may not appear in the URL.

2. Colon and Slashes: Following the protocol (http or https), a URL includes a colon and two slashes (://). In regex, the slash (/) is a special, so to match it literally, we need to escape it with a backslash (\) . This results in :\/\/. Therefore, the regex expression to match the protocol and the following colon and slashes can be written in the following way: https?:\/\/.

3. Making the Protocol Optional: As mentioned, https:// or http:// are not mandatory; a URL could start directly with www.. Therefore, we enclose this part in parentheses and add a question mark (?) after it, making the entire segment optional. Our regex expression would now look in the following way: (https?:\/\/)?.

4. Detecting the www. prefix: After the https:// or http:// part of a URL, we often encounter the www. prefix. In regex, the dot (.) is a special character that represents any character, so to match a literal dot, we need to escape it with a backslash (\). Thus, www. is written as www\. in regex. Adding this to our current expression, we get (https?:\/\/)?www\..

5. Making the www. Prefix Optional: The www. part of a URL is not always present, so we need to make it optional. We achieve this by enclosing www\. in parentheses and adding a question mark (?) after it. This indicates that the www. prefix may or may not appear in the URL. Therefore, our regex pattern becomes (https?:\/\/)?(www\.)?.

Following this, we expect the domain name, such as google, facebook, etc. We can specify this with the following steps:

6. Character Set Specification: A domain name can contain lowercase letters (a-z), uppercase letters (A-Z), and even digits (0-9). In regex, we can specify which characters are allowed by placing them inside brackets ([]). Inside these brackets, you can either list specific characters or define ranges with a hyphen (-). For instance, to match any character that is a lowercase letter, an uppercase letter, or a digit, we can use the regex pattern [a-zA-Z0-9]. This pattern covers all characters from a to z, A to Z, and 0 to 9.

7. Requiring Multiple Characters: While the expression [a-zA-Z0-9] matches only a single character, a URL often contains more than one such character. To handle this, we use a plus sign (+) after the character set in regex. The plus sign (+) indicates that one or more occurrences of the specified characters are allowed. Thus, [a-zA-Z0-9]+ matches sequences of one or more characters that are either lowercase letters, uppercase letters, or digits.

After the domain name, there is always a dot followed by the domain extension, such as .com or .net.

8. Matching the Domain Extension: To match domain extensions such as .com or .org, we need a regex pattern that identifies a literal dot followed by one or more alphabetic characters. This can be expressed in regex as \.[a-zA-Z]+. Remember that, in regex, the dot is escaped with a backslash (\) to ensure it is treated as a literal dot rather than a wildcard.

Putting it all together, the regular expression we will use is ^(https?:\/\/)?(www\.)?[a-zA-Z0-9]+\.[a-zA-Z]+$.

Notice that we’ve added two additional symbols: ^ at the beginning and $ at the end. These symbols ensure that the entire string must match the regular expression from start to finish. Specifically, ^ indicates the start of the string, and $ indicates the end.

Beware: This regular expression isn’t perfect

Keep in mind that the regular expression we have just written is not perfect. For example, some URLs may include hyphens in the domain name, subdomains, or other special characters that this basic pattern does not account for. Additionally, this regex does not ensure that the URL actually exists or is reachable; it only checks for a general structure. However, for the sake of this tutorial, this approach is sufficient and serves as a good starting point to highlight how data can be validated.

Integrating the regex pattern into a Python function

Now that we have our regex pattern for detecting URLs, we can put it to use in Python to validate user input.

To do so, we will leverage the re library, which handles regular expression operations in Python. First, we’ll define our pattern and then use the fullmatch() function from the re library to check if the user-provided URL matches the pattern exactly. Note that fullmatch() doesn’t return a simple boolean value; instead, it provides a Match object if there’s a match, or None if there isn’t. To convert this result into a boolean, we can use the bool() function—bool() will return True for any value other than None, and False for None.

Using this approach, we can write our function to validate whether user input follows a correct URL format as follows:

import re  # We need to import the re library

def is_valid_url(url):
    pattern = r"^(https?:\/\/)?(www\.)?[a-zA-Z0-9]+\.[a-zA-Z]+$"
    return bool(re.fullmatch(pattern, url))  # Checks if the URL matches the pattern

Best practice: Adding type hints to strengthen your Python functions

When creating functions, it’s advisable to specify the types of the arguments and the expected return type. For example, in this case, the parameter url should be a string (str), and the output of this function should be a boolean (bool). We can specify this as follows:

import re 

def is_valid_url(url: str) -> bool:
    pattern = r"^(https?:\/\/)?(www\.)?[a-zA-Z0-9]+\.[a-zA-Z]+$"
    return bool(re.fullmatch(pattern, url))

By adding type hints, we introduce an additional layer of verification, making our code more robust and specific.

Modifying the application’s behavior to validate and confirm URLs

We have now incorporated a function to check if a user’s input is a valid URL. With this in place, we can adjust the part of the code found in app.py responsible for rendering the page that displays the URL confirmation.

To recap, here is the code we built for the main route (/) in the previous post:

@app.route("/", methods=['GET', 'POST'])
def home():
    if request.method == 'POST':
        url = request.form.get('urlInput')
        return redirect(url_for('display_url', url=url))
    else:
        return render_template('index.html')

The current code captures the URL submitted by the user through the urlInput form element, which is intended for URL input. However, the code uses the submitted value regardless of its format. As a result, even when an invalid URL (such as “hello”) is entered, as demonstrated in Figure 3, the code still displays a confirmation message.

For this reason, we want to enhance this code by adding a validation step to ensure that the submitted URL is valid before doing anything with it. To accomplish this, we will use the is_valid_url() function that we have just created.

If is_valid_url() returns True, it confirms that the submitted URL is valid, and we will then display a confirmation message to the user, indicating that the URL has been successfully registered. However, if is_valid_url() returns False, an error message will be displayed to inform the user that the URL is invalid.

To implement this, we can modify our code by adding an if-else statement based on the result of is_valid_url(url). If the URL is valid, we will reuse the code we previously had, i.e., redirect the user to a confirmation page using redirect(url_for('display_url', url=url)). Conversely, if the URL is invalid, we will add a return statement with the following message: "The URL you entered is not valid. Please check the format and try again." In Flask, returning a string generates a plain HTML page containing that message. Therefore, our code would now look as displayed below:

@app.route("/", methods=['GET', 'POST'])
def home():
    if request.method == 'POST':
        url = request.form.get('urlInput')
        if is_valid_url(url):
            return redirect(url_for('display_url', url=url))
        else:
            return "The URL you entered is not valid. Please check the format and try again."
    else:
        return render_template('index.html')

In addition, Figure 4 shows the behavior of our application after implementing these changes and entering both an invalid URL (hello) and a valid URL (www.google.com).

Figure 4: Result of the URL validation submitted by the user

This method is quite basic, as it simply shows a plain page with the message “The URL you entered is not valid. Please check the format and try again.” A more effective approach would be to display this message within the confirmation container.

If the URL is valid, a confirmation box will appear with the message: “You have successfully registered the following URL: [url].” If the URL is invalid, the confirmation box will instead display: “The URL you entered is not valid. Please check the format and try again.”

To implement this update, we need to modify the HTML template (index.html), specifically the section related to the confirmation container, which is identified by the ID confirmationContainer.

Previously, we set up the confirmation container as follows:

{% if url %}
  <div id="confirmationContainer">
        <p>You have registered the following URL: {{ url }}</p>  <!-- Display URL registration confirmation -->
  </div>
{% endif %}

In this code, we included a predefined message: “You have registered the following URL:”.

However, we can simplify this by removing the predefined message from the template and passing the entire confirmation text from the backend code, i.e., from our Python code. Therefore, the updated template would look like this:

{% if url %}
  <div id="confirmationContainer">
    <p>{{ url }}</p>  <!-- Display URL registration confirmation -->
  </div>
  </div>
{% endif %}

Now, there is no predefined text inside the container; the full message is provided by the backend.

In addition, we need to update the backend code (app.py) so that it not only sends the URL but also includes a message based on the URL’s validity. If the URL is valid, it should prepend the text “Has entrado la URL:” to the URL. If the URL is invalid, it should display the text “Error URL incorrecta!”:

@app.route("/", methods=['GET', 'POST'])
def home():
    if request.method == 'POST':
        url = request.form.get('urlInput')
        if is_valid_url(url):
            confirmation_message = "You have successfully registered the following URL: " + url
            return redirect(url_for('display_url', url=confirmation_message))
        else:
            confirmation_message = "The URL you entered is not valid. Please check the format and try again."
            return redirect(url_for('display_url', url=confirmation_message))
    else:
        return render_template('index.html')

Next, we need to make the same updates to the /display_url/<path:url> route. This route, as you might remember, is where users are redirected to see the confirmation container after registering a URL. Since users can also register new URLs from this route, it’s important to apply these updates here as well. Below is the revised code for this route:

@app.route("/display_url/<path:url>", methods=['GET', 'POST'])
def display_url(url=None):
    if url:
        if request.method == 'POST':
            url2 = request.form.get('urlInput')
            if is_valid_url(url2):
                confirmation_message = "You have successfully registered the following URL: " + url2
                return redirect(url_for('display_url', url=confirmation_message))
            else:
                confirmation_message = "The URL you entered is not valid. Please check the format and try again."
                return redirect(url_for('display_url', url=confirmation_message))
        else:
            return render_template('index.html', url=url)
    else:
        return redirect(url_for('home'))

After implementing these changes, we can run our updated application and test it by entering an invalid URL, such as hello. When we do this, the confirmation container will display the message, “The URL you entered is not valid. Please check the format and try again.”

After seeing this message, we can directly proceed to enter a valid URL, such as www.google.com. This time, the confirmation container will display the message, “You have successfully registered the following URL: www.google.com,” as shown in Figure 5.

Figure 5: Updated URL validation behavior

Encapsulating code

As our codebase grows, it becomes more complex and harder to read. This often leads to repetitive code in different parts of our application. Recognizing and addressing code repetition allows us to simplify our code by consolidating repetitive logic into functions. This approach, known as encapsulation, helps us avoid duplicating code and makes our application more maintainable. Instead of copying and pasting the same code repeatedly, we can create reusable functions.

For instance, the code we’ve added for both routes can be consolidated into a single function, which we might call process_url. This function will handle the URL processing and return the appropriate confirmation text.

We can define the process_url function as follows:

def process_url(url: str) -> str:
    if is_valid_url(url):
        confirmation_message = "You have successfully registered the following URL: " + url
    else:
        confirmation_message = "The URL you entered is not valid. Please check the format and try again."
    return confirmation_message

By using this function, we can simplify the code for the home and display_url routes as shown below:

@app.route("/", methods=['GET', 'POST'])
def home():
    if request.method == 'POST':
        url = request.form.get('urlInput')
        confirmation_message = process_url(url)
        return redirect(url_for('display_url', url=confirmation_message))
    else:
        return render_template('index.html')

@app.route("/display_url/<path:url>", methods=['GET', 'POST'])
def display_url(url=None):
    if url:
        if request.method == 'POST':
            url2 = request.form.get('urlInput')
            confirmation_message = process_url(url2)
            return redirect(url_for('display_url', url=confirmation_message))
        else:
            return render_template('index.html', url=url)
    else:
        return redirect(url_for('home'))

Setting Up Our Database

When creating a database, you can choose to set it up on your own hardware locally or use a remote database hosted by a cloud service provider like Azure, AWS, or Google Cloud.

Setting up a local database involves installing and managing it on your own computer, a dedicated server, or on-premises infrastructure. This method gives you full control over the environment, allowing you to customize settings, manage security, and optimize performance according to your needs.

The advantages of a local database include complete customization and no ongoing subscription fees. However, you are responsible for running costs such as electricity, hardware maintenance, and software updates. Additionally, scaling a local database can be costly and challenging, often requiring additional hardware. If your local server experiences downtime, your database’s availability may also be compromised unless you implement high-availability solutions, which can increase complexity and expenses.

In contrast, a cloud-based database is hosted on a remote server managed by a cloud service provider. Providers offer a range of options, including SQL and NoSQL databases, allowing you to select the best fit for your needs. Cloud databases simplify management as the provider handles infrastructure, maintenance, and upgrades, letting you focus on utilizing the database.

Cloud databases offer excellent scalability, enabling you to easily adjust resources such as storage and computing power as your needs evolve. They also ensure high availability with built-in redundancy and failover options, and provide global access, which is ideal for distributed teams or global applications. However, cloud databases can be more expensive over time, particularly as your usage grows. They also offer less control over the database environment since the provider manages the infrastructure. Data security in the cloud depends on the provider’s security measures and compliance with relevant regulations. Proper security practices are essential regardless of where the database is hosted.

Configuring Our Database with Supabase

For our purpose, we will use a cloud service, specifically Supabase, https://supabase.com/, which allows us to create dedicated PostgreSQL databases and supporting a free tier. PostgreSQL, also known as Postgres, is an open-source, cross-platform, and highly scalable relational database management system designed to offer advanced features and strong performance in production environments.

Note

If you prefer and are already knowledgeable with other databases or cloud services, you can use alternatives for this tutorial. Whether it’s another managed PostgreSQL provider, a different relational database system, or even a local database setup.

To get started, you need to create a free account on Supabase. Once your account is set up and you’re logged in, you can initiate a new project.

Navigate to the “Projects” section—this is usually the main screen you see after logging in—and click on “New Project,” as highlighted in red in the Figure 6. By following these steps, you will have access to a dedicated PostgreSQL database in the cloud, allowing you to efficiently manage and scale your database needs.

Figure 6: “All Projects” page with “New project” button highlighted

Once you click this button, a menu will appear, as shown in Figure 7. In this menu, you will need to define several parameters. First, enter a unique name for your project. Next, create and enter a password for your database; make sure to remember or securely store this password, as you will need it later. Finally, select a region that is geographically close to you to ensure optimal performance and reduced latency.

Figure 7: Project creation menu

After creating a project, you’ll be redirected to the project view, as shown in Figure 8. You might notice a brief delay in being able to perform any actions; this is indicated by the status icon next to the project name, which will show that the project is being set up (highlighted in red in Figure 8). If you encounter any issues with redirection, you can alternatively access the project by going to the “Projects” section and clicking on the specific project.

Figure 8: Project page after creation

Retrieving the database connection information

Once the project is set up, you will notice that the project view content updates and a green status indicator appears at the top right corner of the page. This green status means your database is available. Next to this status indicator, you’ll find a “Connect” button, which is highlighted in red in Figure 9.

Figure 9: Project page after setup is complete

Clicking this button will open a panel displaying the details required to connect to your database. Be sure to select the “Python” tab, as highlighted in red in Figure 10, to get the specific connection values needed for Python.

Figure 10: Connection panel showcasing python connection details

As shown in Figure 10, in this panel, we can see a series of connection parameters: user=[DB_USER], password=[DB_PASSWORD], host=[DB_HOST], port=[DB_PORT], and dbname=[DB_NAME]. The placeholders inside the square brackets ([]) represent where you should enter your specific account details. Be sure to replace these placeholders with your actual information. Remember, these values are sensitive and should be kept confidential to protect access to your database.

Please note that the password (DB_PASSWORD) should be the one you set when creating the current project.

Connecting our application to our database

Now that we have the required connection parameters to connect to our database, it’s a good practice to keep these configuration values separate from the main code file. Instead of hardcoding them, you should use a .env file.

A .env file is a simple text file used to store environment variables and configuration settings outside of your main codebase. It helps keep sensitive information, like database credentials, secure and makes it easier to manage different settings for development, testing, and production environments.

You should create this .env file and place it in the root directory of your application. With this setup, the file structure for your application will look like this:

- my_flask_app/            (Main folder of the application)
    |
    |- app.py              (Main file for the Flask application)
    |
    |- .env                (File to store environmental variables)
    |
    |- templates/          (Folder to store HTML templates)
    |    |
    |    |- index.html     (HTML template for the main page)
    |
    |- static/             (Folder to store static files such as CSS, JavaScript, images, etc.)
         |
         |- styles         (Folder to store style sheets, such as CSS files)
            |
            |- home.css    (CSS file to style the main page)

The content of this .env file will be as follows:

DB_USER = 'your_username'
DB_PASSWORD = 'your_password'
DB_HOST = 'database_host'
DB_PORT = 'port_number'
DB_NAME = 'database_name'

Since the database connection parameters are defined in a separate .env file, we need to import them into the backend of our application, specifically into app.py. To achieve this, we will use the os module, which provides functionality for interacting with the operating system. One useful feature of the os module is the os.getenv() method. This method retrieves the value of an environment variable if it exists; otherwise, it returns None or a default value if specified.

Moreover, to ensure that these environment variables are properly loaded from the .env file, we will utilize the dotenv library and its load_dotenv() function. The load_dotenv() function reads the .env file and sets the environment variables in the current process.

Without load_dotenv(), the Python code may not automatically recognize or load the .env file, which could lead to missing or incorrect environment variable values. By explicitly calling load_dotenv(), we make sure that these variables are available in the environment before we attempt to retrieve them with os.getenv().

Therefore, we will make three key updates to our app.py file: (1) we will import the os module and the load_dotenv function from the dotenv library, (2) we will call load_dotenv() to ensure the .env file is loaded properly, and (3) we will use the os.getenv() method to retrieve the database connection parameters. These additions will look like this in Python:

# Import the os module
import os
# Import load_dotenv from the dotenv library
from dotenv import load_dotenv

# Make clear that env variables are picked from .env file
load_dotenv()

# Retrieve database connection parameters
db_user = os.getenv('DB_USER')
db_password = os.getenv('DB_PASSWORD')
db_host = os.getenv('DB_HOST')
db_port = os.getenv('DB_PORT')
db_name = os.getenv('DB_NAME')

Now that we have the necessary values to connect to our database available within our app.py file, we can proceed with establishing the connection. To accomplish this, we’ll use the psycopg library, which facilitates connecting to and managing Postgres databases through Python.

First, we need to import the psycopg library. Our next step is to create a connection to the database using the connect function provided by psycopg. This function will use the connection parameters that we have loaded from the .env file. Here’s how you can set up the connection:

# import psycopg library
import psycopg

# Create a connection to our database and store it in a variable called conn
conn = psycopg.connect(
            dbname=db_name,
            user=db_user,
            password=db_password,
            host=db_host,
            port=db_port
        )

To make our code more modular and reusable, we can encapsulate this code within a function. Moreover, we can also add a try-catch statement to handle exceptions that might occur during the connection process and facilitating debugging. You can find the revised implementation below:

import psycopg

def connect_to_database():
    try:
        conn = psycopg.connect(
            dbname=db_name,
            user=db_user,
            password=db_password,
            host=db_host,
            port=db_port
        )
        return conn
    except Exception as e:
        print(e)

Creating our table

Having established the connection to the database, we can proceed to create the table we will be working with. This table only needs to be set up once during the initial setup.

To accomplish this, we will add an additional function to the app.py file, which will create the table only if it does not already exist.

Remember, we are developing a basic application that allows users to register URLs, which helps us track the popularity of these URLs. To support this functionality, we will create a simple table with just two fields: an ID and the URL value.

Here’s how it will work: Each time a user submits a URL, a new entry will be added to the table, consisting of a unique ID and the URL value. This design keeps the table immutable because each URL is recorded as a separate row without modifying existing entries.

Maintaining an immutable table is crucial as it ensures the integrity of our data. By avoiding changes to existing records, we prevent data loss or corruption and ensure a reliable and accurate record of all submitted URLs.

A small note

In this tutorial, we are simplifying the scenario to make it easier to understand compared to more complex real-world situations. This approach helps us focus on how different parts or components of a data-driven application work together in a straightforward way.

For example, in this tutorial, our table will only include two columns: an identifier (ID) and a URL value. In a real-world application, however, the table would likely include additional attributes, such as a user ID. This would require implementing a user identification system to ensure that each user can only register a URL once. We might also include other attributes, such as the date, to provide more detailed information.

To create a table in our database, we will follow these four essential steps:

1. Establish a Connection to the Database: Begin by using the connect_to_database() function to establish a connection to the database.

2. Create a Cursor: Once the connection is established, create a cursor to execute SQL commands. Cursors are temporary objects in SQL that allow us to interact with and manipulate data.

3. Execute SQL Commands and Commit Changes: Use the cursor to execute the necessary SQL commands to create and modify the table. Once these commands have been executed, use the commit() method to save the changes to the database permanently.

4. Close the Database Connection: Finally, once all changes have been made and saved, close the connection to the database.

We can implement these steps in a function called create_table(). The code for this function is provided below:

def create_table():
    try:
        # Connect to the PostgreSQL database using the connect_to_database function
        conn = connect_to_database()

        # Create a cursor to execute SQL commands using the cursor method
        cur = conn.cursor()

        # Execute the command to create the table if it does not already exist
        cur.execute("""
            CREATE TABLE IF NOT EXISTS registered_urls (
                id SERIAL PRIMARY KEY,
                url TEXT
            )
        """)

        # Commit the changes and close the connection
        conn.commit()
        # Print statement confirming correct execution
        print("create_table function executed correctly")
    except Exception as e:
        print(e)
    finally:
        conn.close()

As you can see, in the code above, the first step is to establish a connection to the database using the connect_to_database() function. We store this connection in a variable called conn, which has a method named cursor(). This method allows us to create a cursor for the database, enabling us to execute SQL commands.

Using this method, we create a cursor and assign it to a variable named cur. With this cursor, we can execute SQL code using the execute() method, which we use to execute the following SQL command:

CREATE TABLE IF NOT EXISTS registered_urls (
  id SERIAL PRIMARY KEY,
  url TEXT
)

This command creates a table in our database named registered_urls, but only if it does not already exist. It specifies that the table will have two columns: id, which will be the primary key and will have a serial value, meaning it will be numeric and automatically increment for each new row inserted; and url, which will be of type text and will store the registered URLs.

As previously mentioned, with this table design, we ensure that the values are immutable. This means that once rows are added to the database, they cannot be changed. If a user registers a URL that has already been registered by others, a new row will be created with the same URL but with a different id.

Finally block

In the code above, we use a try-catch statement with a finally block. The try-catch statement helps manage situations where errors might arise while executing code, especially after opening a database connection.

If an error occurs within the try block, the code in the catch block will execute, but the connection might not be closed if the finally block is not used.

To prevent this, we include a finally block, which ensures that the code within it runs regardless of whether an error occurs. This guarantees that the database connection is always closed properly, even if an exception is thrown.

At this stage, it’s important to determine the most appropriate when and how to run our create_table() function. This function is responsible for setting up a database table, but it’s not something that needs to be executed every time we run our Flask application. Running it repeatedly would be inefficient because the table only needs to be created once, or checked occasionally, not on every application start. To avoid unnecessary checks and improve the efficiency of our application, we can create a custom Flask command that allows us to run create_table() only when we specifically want to.

To accomplish this, Flask provides a convenient way to define custom commands using the @app.cli.command decorator. By attaching this decorator to a function, we can register a new command that can be executed through the Flask command-line interface (CLI). This means that instead of embedding the table creation logic directly into our application’s startup process, we can isolate it in a command that can be manually triggered whenever needed.

For instance, we could define a command called init-db-table by using @app.cli.command("init-db-table") to the create_table() function as depicted below:

@app.cli.command("init-db-table")
def create_table():
    try:
        conn = connect_to_database()
        cur = conn.cursor()

        cur.execute("""
            CREATE TABLE IF NOT EXISTS registered_urls (
                id SERIAL PRIMARY KEY,
                url TEXT
            )
        """)

        conn.commit()
        print("create_table function executed correctly")
    except Exception as e:
        print(e)
    finally:
        conn.close()

With these adjustments, our app.py file will look in the following way:

from flask import Flask, request, render_template, redirect, url_for
import os
from dotenv import load_dotenv
import psycopg
import re 

load_dotenv()

db_user = os.getenv('DB_USER')
db_password = os.getenv('DB_PASSWORD')
db_host = os.getenv('DB_HOST')
db_port = os.getenv('DB_PORT')
db_name = os.getenv('DB_NAME')

def connect_to_database():
    try:
        conn = psycopg.connect(
            dbname=db_name,
            user=db_user,
            password=db_password,
            host=db_host,
            port=db_port
        )
        return conn
    except Exception as e:
        print(e)

def is_valid_url(url: str) -> bool:
    pattern = r"^(https?:\/\/)?(www\.)?[a-zA-Z0-9]+\.[a-zA-Z]+$"
    return bool(re.fullmatch(pattern, url))

def process_url(url: str) -> str:
    if is_valid_url(url):
        confirmation_message = "You have successfully registered the following URL: " + url
    else:
        confirmation_message = "The URL you entered is not valid. Please check the format and try again."
    return confirmation_message

app = Flask(__name__)

@app.cli.command("init-db-table")
def create_table():
    try:
        conn = connect_to_database()
        cur = conn.cursor()

        cur.execute("""
            CREATE TABLE IF NOT EXISTS registered_urls (
                id SERIAL PRIMARY KEY,
                url TEXT
            )
        """)

        conn.commit()
        print("create_table function executed correctly")
    except Exception as e:
        print(e)
    finally:
        conn.close()

@app.route("/", methods=['GET', 'POST'])
def home():
    if request.method == 'POST':
        url = request.form.get('urlInput')
        confirmation_message = process_url(url)
        return redirect(url_for('display_url', url=confirmation_message))
    else:
        return render_template('index.html')

@app.route("/display_url/<path:url>", methods=['GET', 'POST'])
def display_url(url=None):
    if url:
        if request.method == 'POST':
            url2 = request.form.get('urlInput')
            confirmation_message = process_url(url2)
            return redirect(url_for('display_url', url=confirmation_message))
        else:
            return render_template('index.html', url=url)
    else:
        return redirect(url_for('home'))

if __name__ == "__main__":
    app.run(debug=True)

Once we have updated our app.py file, we can move on to creating the registered_urls table. To accomplish this, we need to use the new CLI command: flask init-db-table.

To do this, we can open a terminal and navigate to the directory where the app.py file is located. Then, we can execute the command flask init-db-table, as shown below in Figure 11.

Figure 11: Running the flask init-db-table CLI command

Great! The command seems to have executed successfully. Now, we need to ensure that the table has been created correctly. To do this, we will go back to the Supabase project dashboard, where we will select the project we are working on. Once in our project, we will click on the “Table Editor” tab, found in the right-hand panel, as highlighted in red Figure 12.

Figure 12: Navigating to the table editor in a Supabase project

After clicking on “Table Editor,” you’ll see all the tables created in your database. In this view, you should be able to find the table named registered_urls, which is highlighted in red in Figure 13.

Figure 13: Verifying the table creation in Supabase

Additionally, we can click on the table registered_urls to see its contents. Doing so will reveal that the table has two empty columns: id and url. Each column is also labeled with its data type—int4 for id and text for url—as shown in Figure 14.

Figure 14: Checking the registered_urls table

Great! We have successfully created the table and are now ready to start adding values to it!

Storing submitted URLs in our database

Now that we have established a connection with our database and created a table called registered_urls, we can store each URL that a user registers. To achieve this, we will create a new function that stores a URL in our database whenever a user registers one.

We can call this function insert_url_into_database(). This function will operate similarly to the previously created function connect_to_database(). Specifically, it will:

1. Establish a connection to the database.

2. Create a cursor.

3. Use the cursor to execute an SQL command to insert the URL into the registered_urls table. Here is how we can implement this using a simple SQL INSERT statement:

   INSERT INTO registered_urls (url) VALUES (%s)", (url,)

   This SQL query inserts a URL into the registered_urls table by specifying the url column and using a placeholder %s to safely insert the value provided by the url variable.

   Notice that we do not need to specify the value for the id column. This is because we defined the id column with the SERIAL property when we created the table, which automatically generates a unique identifier for each new row.

4. Finally, we need to confirm the changes using the commit method and close the connection.

Therefore, the implementation of the insert_url_into_database() function in Python will look as follows:

def insert_url_into_database(url: str):
    try:
        conn = connect_to_database()
        cur = conn.cursor()
        cur.execute("INSERT INTO registered_urls (url) VALUES (%s)", (url,))
        conn.commit()
    except Exception as e:
        print("Error inserting URL into the database:", e)
    finally:
        conn.close()

The next step is to execute this function every time a valid URL has been submitted. To achieve this, we can modify the process_url() function, which, as you may recall, validates whether a URL is valid and generates the confirmation message to be displayed. Currently, this function looks like this:

def process_url(url: str) -> str:
    if is_valid_url(url):
        confirmation_message = "You have successfully registered the following URL: " + url
    else:
        confirmation_message = "The URL you entered is not valid. Please check the format and try again."
    return confirmation_message

All we need to do now is call our new insert_url_into_database() function within the if statement. In other words, if the URL is valid, we will store it in our database. This will modify the function as follows:

def process_url(url: str) -> str:
    if is_valid_url(url):
        insert_url_into_database(url)
        confirmation_message = "You have successfully registered the following URL: " + url
    else:
        confirmation_message = "The URL you entered is not valid. Please check the format and try again."
    return confirmation_message

As a result, our code in app.py now looks as follows:

from flask import Flask, request, render_template, redirect, url_for
import os
from dotenv import load_dotenv
import psycopg
import re

load_dotenv()

db_user = os.getenv('DB_USER')
db_password = os.getenv('DB_PASSWORD')
db_host = os.getenv('DB_HOST')
db_port = os.getenv('DB_PORT')
db_name = os.getenv('DB_NAME')

def connect_to_database():
    try:
        conn = psycopg.connect(
            dbname=db_name,
            user=db_user,
            password=db_password,
            host=db_host,
            port=db_port
        )
        return conn
    except Exception as e:
        print(e)
        
def is_valid_url(url: str) -> bool:
    pattern = r"^(https?:\/\/)?(www\.)?[a-zA-Z0-9]+\.[a-zA-Z]+$"
    return bool(re.fullmatch(pattern, url))

def insert_url_into_database(url: str):
    try:
        conn = connect_to_database()
        cur = conn.cursor()
        cur.execute("INSERT INTO registered_urls (url) VALUES (%s)", (url,))
        conn.commit()
    except Exception as e:
        print("Error inserting URL into the database:", e)
    finally:
        conn.close()

def process_url(url: str) -> str:
    if is_valid_url(url):
        insert_url_into_database(url)
        confirmation_message = "You have successfully registered the following URL: " + url
    else:
        confirmation_message = "The URL you entered is not valid. Please check the format and try again."
    return confirmation_message


app = Flask(__name__)

@app.cli.command("init-db-table")
def create_table():
    try:
        conn = connect_to_database()
        cur = conn.cursor()

        cur.execute("""
            CREATE TABLE IF NOT EXISTS registered_urls (
                id SERIAL PRIMARY KEY,
                url TEXT
            )
        """)

        conn.commit()
        print("create_table function executed correctly")
    except Exception as e:
        print(e)
    finally:
        conn.close()

@app.route("/", methods=['GET', 'POST'])
def home():
    if request.method == 'POST':
        url = request.form.get('urlInput')
        confirmation_message = process_url(url)
        return redirect(url_for('display_url', url=confirmation_message))
    else:
        return render_template('index.html')

@app.route("/display_url/<path:url>", methods=['GET', 'POST'])
def display_url(url=None):
    if url:
        if request.method == 'POST':
            url2 = request.form.get('urlInput')
            confirmation_message = process_url(url2)
            return redirect(url_for('display_url', url=confirmation_message))
        else:
            return render_template('index.html', url=url)
    else:
        return redirect(url_for('home'))

if __name__ == "__main__":
    app.run(debug=True)

With our code now updated, we can proceed to run our Flask application. At this point, we should verify that URLs registered through the application are correctly stored in the database.

To verify the successful addition of a URL to our dabatase, we can use the Table Editor view in Supabase. First, we should navigate to the Table Editor for our project and locate the registered_urls table, as shown in Figure 13.

Next, open a separate tab with our running application and register a valid URL. Once you receive confirmation that the URL has been successfully registered, return to the Table Editor view to check that the new URL appears correctly in the registered_urls table, as shown in Figure 15. In this way, we can confirm that our application is correctly registering URLs into the registered_urls table.

Figure 15: Verifying that the URLs registered in our application are correctly stored in our database.

Great! Our application is starting to become functional. Users can register URLs, and these URLs are stored in our database, which allows us to access and manage this information.

Next, we can move forward by creating a new feature: a page that displays the top favorite websites to our users. This page will present a list of websites ranked by their registration count, showing which websites have been registered the most.

Creating a new page: Top favorite websites

After updating our application to store submitted URLs and verifying their correct storage in our database, our goal is to leverage this data. We aim to identify the top favorite websites—those registered most frequently—and present them to our users on a dedicated page.

To achieve this, we will need to take two main steps:

1. Extracting URL counts: We will count the frequency of each unique URL in our database, allowing us to identify the most frequently submitted URLs.

2. Creating a page to display top favorite websites: We will then create a page that dynamically populates a table containing the most popular websites (URLs) and their counts.

Extracting URL counts

Let’s delve into the first step: determining the popularity of each URL by counting how often each URL appears in our database.

To achieve this, we will use an SQL query that groups identical URLs, counts their occurrences, and sorts the results in descending order of frequency. To focus on the most popular URLs, we will also limit the results to the top 10. Here’s the SQL query for this process:

SELECT url, COUNT(*) as count 
FROM registered_urls 
GROUP BY url 
ORDER BY count DESC 
LIMIT 10;

Having created this query, we want to execute it through our backend to enable our application to retrieve the relevant information from the database. To accomplish this, and in line with our previous database interactions, we will create a function, which we will call get_top_registered_urls(). This function will connect to the database, execute the previous SQL query, and return the results. The code for this function is shown below:

def get_top_registered_urls():
    try:
        conn = connect_to_database()
        cur = conn.cursor()
        cur.execute("""
            SELECT url, COUNT(*) as count
            FROM registered_urls
            GROUP BY url
            ORDER BY count DESC
            LIMIT 10;
        """)
        top_registered_urls = cur.fetchall()
        return top_registered_urls
    except Exception as e:
        print(f"Error retrieving the URLs: {e}")
    finally:
        if conn:
            conn.close()

In this function, you will notice that, compared to the other functions we have created to interact with our database, we are not using the commit() method. This is because we are not modifying any data in the database; we are merely retrieving it. The commit() method is only necessary when making changes to the database, such as inserting or updating records.

Instead, we use the fetchall() method, which retrieves all rows resulting from the query. This method returns a list of tuples, with each tuple representing a row from the query result. Each tuple contains the URL and its count, showing how many times that URL appears in the database.

For example, the result from fetchall() might look like this: [('www.facebook.com', 1), ('www.google.com', 1)]. This list provides each URL along with its count of occurrences. In this case, the output indicates that there are two URLs in the table—www.facebook.com and www.google.com—each with a registration count of 1.

We are now ready to create a new page in our application to present this data to users in an appealing and user-friendly format.

Creating a page to display the top favorite websites

To display the information retrieved from the get_top_registered_urls() function to users, we will need to set up a new page using an HTML template. This involves creating a new file named popular.html in the templates directory.

We will begin by copying the contents from index.html into this new file. However, unlike index.html, the popular.html template will not contain the form elements for users to submit URLs, such as the text field and button, nor will it have the confirmation container.

The primary focus of this new page will be to present a table of popular URLs along with their counts, without any additional functionalities. Therefore, the starting point for this file will look like this:

<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="{{ url_for('static', filename='styles/popular.css') }}"><!-- Replaced home.css file to popular.css -->
</head>
<body>
    <div class="content">
        <div class="table-container"><!-- Replaced container class to table-container -->
            <h1>Top Favorite Websites</h1> <!-- Updated the title -->
            <!-- Removed all sections related to the form and the confirmation container -->
        </div>
    </div>
</body>
</html>

Note that in the previous code, two additional changes have been made: the link to the CSS file has been updated to a new file specifically for this page, named popular.css, and the class name of the div containing the table has been changed to table-container.

To present the information retrieved by get_top_registered_urls() on this page, we need to consider the best way to display it. This function returns a list of tuples, with each tuple containing a URL and its registration count. To clearly present this data, the best approach is to use a table format. In this table, each row will represent a different URL, with the URL displayed in the first column and its registration count in the second.

Since this data is dynamically generated by our Python backend (app.py), we can use Flask’s templating system to render it. The frontend will receive a list of tuples from the backend, with each tuple containing information about a website. Each tuple can then be used to populate a row in an HTML table, using each entry to populate the columns.

Specifically, we can use a for loop in the template to iterate over the information retrieved by get_top_registered_urls(), which we will call top_registered_urls. For each tuple in top_registered_urls, we extract its values to populate the table columns: the first value renders the URL in the URL column, and the second value renders the count in the tracking count column.

Here is how we can implement this logic into our template:

<table>
  <thead>
    <tr>
      <!-- Column headers -->
      <th>URL</th> <!-- Column for the URL -->
      <th>Tracking Count</th> <!-- Column for the tracking count -->
    </tr>
  </thead>
  <tbody>
    <!-- Loop through each item in the list -->
    {% for top_url in top_registered_urls %}
      <tr>
        <!-- Data cell for the URL -->
        <td>{{ top_url[0] }}</td> <!-- Displays the URL of the current item in top_registered_urls, i.e., the first entry of the tuple -->
        <!-- Data cell for the tracking count -->
        <td>{{ top_url[1] }}</td> <!-- Displays the tracking count of the current item in top_registered_urls, i.e., the second entry of the tuple -->
      </tr>
    {% endfor %}
  </tbody>
</table>

We can integrate this code into our popular.html file, which will now contain the following content:

<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="{{ url_for('static', filename='styles/popular.css') }}">
</head>
<body>
    <div class="content">
        <div class="table-container">
            <h1>Top Favorite Websites</h1>
            <table>
  <thead>
    <tr>
      <th>URL</th>
      <th>Tracking Count</th>
    </tr>
  </thead>
  <tbody>
    {% for top_url in top_registered_urls %}
      <tr>
        <td>{{ top_url[0] }}</td>
        <td>{{ top_url[1] }}</td> 
      </tr>
    {% endfor %}
  </tbody>
</table>
        </div>
    </div>
</body>
</html>

The next step is to create the popular.css file. As with the HTML file for this page, we will start with the existing CSS from the main page, home.css, and make the necessary adjustments to reflect the updates in popular.html.

Since the popular.html file no longer contains the container class, form elements, or the confirmation container, we need to remove all associated classes and IDs from popular.css. Instead, we will introduce new styles specifically for the elements within the table-container.

Our goal is to make the table within the table-container both attractive and easy to read. To achieve this, we will center the table’s title to ensure the header information is well-organized. The table will be set to occupy 50% of the viewport width, which will provide a balanced and visually pleasing layout. We will also merge the table borders into a single continuous line and center the table horizontally for a cleaner look.

To enhance readability, we will add generous padding inside the table cells and use a subtle border to clearly define each cell. The text within the cells will be left-aligned for consistency, while header cells will feature a dark green background with white text to make them stand out.

Additionally, we will enhance user interaction by changing the background color of table rows to a slightly darker shade of gray when hovered over. This subtle color shift will provide a clear visual cue and make the table easier to navigate.

Here is the updated content of popular.css, reflecting all these changes:

body {
    font-family: Arial, sans-serif;
    margin: 0;
    padding: 0;
    height: 100vh; /* Ensure the body takes up the full height of the viewport */
}

.content {
    display: flex;
    align-items: center;
    padding-top: 5vh;  /* Adds padding at the top to avoid overlap with the navbar we will create */
    height: 90.5vh;   /* Sets the height of the container to 90.5% of the viewport height, i.e., area remaining after navbar and top padding */
    background-color: #f0f0f0; /* Light gray background color */
    flex-direction: column; /* Arrange children elements in a column */
    justify-content: center; /* Horizontally centers the content */
}

/* CSS styling for the table format */
.table-container h1 {
    text-align: center; /* Center-aligns the table header */
}

.table-container table {
    width: 50vw; /* Set table width to 50% of the viewport width */
    border-collapse: collapse; /* Collapse borders for a cleaner look */
    margin: auto; /* Center the table horizontally */
}

.table-container th,
.table-container td {
    padding: 12px; /* Add padding for better readability */
    border: 1px solid #ddd; /* Light gray border for table cells */
    text-align: left; /* Left-align text in table cells */
}

.table-container th {
    background-color: #4CAF50; /* Dark green background for header cells */
    color: white; /* White text color for header cells */
}

.table-container tr:nth-child(even) {
    background-color: #f2f2f2; /* Light gray background for even rows */
}

.table-container tr:hover {
    background-color: #ddd; /* Change table row background color on hover for better visibility */
}

With all these changes, our file structure will now look like this:

- my_flask_app/            (Main folder of the application)
    |
    |- app.py              (Main file for the Flask application)
    |
    |- .env                (File to store environmental variables)
    |
    |- templates/          (Folder to store HTML templates)
    |    |
    |    |- index.html     (HTML template for the main page)
    |    |
    |    |- popular.html   (HTML template for the top favorite websites page)
    |
    |- static/             (Folder to store static files such as CSS, JavaScript, images, etc.)
         |
         |- styles         (Folder to store style sheets, such as CSS files)
            |
            |- home.css    (CSS file to style the main page)
            |
            |- popular.css (CSS file to style the top favorite websites page)

Almost everything is set up. We are now retrieving the most frequently registered websites from the database—essentially, the most popular sites. We have created a new page to display these websites along with their counts. All that remains is to pass the retrieved data from the backend to the frontend so it can be rendered on this page.

Populating the top favorite websites table

To define the behavior for the popular page in our application, we need to start by specifying its route. For instance, we can designate the URL path /popular for this page. In our app.py file, we will then outline the necessary steps for handling requests to this route. Specifically,we need to implement the following steps:

1. Retrieve the unique URLs and their counts by executing the get_top_registered_urls() function.

2. Render the popular.html page using the data obtained from get_top_registered_urls() to populate the table.

To accomplish this, we will use similar approach to what we have employed for other routes in our application. In app.py, we will define a route handler function for the /popular route. This function will first gather the necessary data and then render the popular.html template, incorporating the retrieved data into the page. Consequently, the code for this route handler will be as follows:

@app.route("/popular", methods=['GET'])
def tracked_page():
    top_registered_urls = get_top_registered_urls()
    return render_template('popular.html', top_registered_urls=top_registered_urls)

With all these changes implemented, the content of our app.py is now as follows:

from flask import Flask, request, render_template, redirect, url_for
import os
from dotenv import load_dotenv
import psycopg
import re 

load_dotenv()

db_user = os.getenv('DB_USER')
db_password = os.getenv('DB_PASSWORD')
db_host = os.getenv('DB_HOST')
db_port = os.getenv('DB_PORT')
db_name = os.getenv('DB_NAME')

def connect_to_database():
    try:
        conn = psycopg.connect(
            dbname=db_name,
            user=db_user,
            password=db_password,
            host=db_host,
            port=db_port
        )
        return conn
    except Exception as e:
        print(e)

def is_valid_url(url: str) -> bool:
    pattern = r"^(https?:\/\/)?(www\.)?[a-zA-Z0-9]+\.[a-zA-Z]+$"
    return bool(re.fullmatch(pattern, url))

def insert_url_into_database(url: str):
    try:
        conn = connect_to_database()
        cur = conn.cursor()
        cur.execute("INSERT INTO registered_urls (url) VALUES (%s)", (url,))
        conn.commit()
    except Exception as e:
        print("Error inserting URL into the database:", e)
    finally:
        conn.close()

def process_url(url: str) -> str:
    if is_valid_url(url):
        insert_url_into_database(url)
        confirmation_message = "You have successfully registered the following URL: " + url
    else:
        confirmation_message = "The URL you entered is not valid. Please check the format and try again."
    return confirmation_message

def get_top_registered_urls():
    try:
        conn = connect_to_database()
        cur = conn.cursor()
        cur.execute("""
            SELECT url, COUNT(*) as count
            FROM registered_urls
            GROUP BY url
            ORDER BY count DESC
            LIMIT 10;
        """)
        top_registered_urls = cur.fetchall()
        return top_registered_urls
    except Exception as e:
        print(f"Error retrieving the URLs: {e}")
    finally:
        if conn:
            conn.close()

app = Flask(__name__)

@app.cli.command("init-db-table")
def create_table():
    try:
        conn = connect_to_database()
        cur = conn.cursor()

        cur.execute("""
            CREATE TABLE IF NOT EXISTS registered_urls (
                id SERIAL PRIMARY KEY,
                url TEXT
            )
        """)

        conn.commit()
        print("create_table function executed correctly")
    except Exception as e:
        print(e)
    finally:
        conn.close()

@app.route("/", methods=['GET', 'POST'])
def home():
    if request.method == 'POST':
        url = request.form.get('urlInput')
        confirmation_message = process_url(url)
        return redirect(url_for('display_url', url=confirmation_message))
    else:
        return render_template('index.html')

@app.route("/display_url/<path:url>", methods=['GET', 'POST'])
def display_url(url=None):
    if url:
        if request.method == 'POST':
            url2 = request.form.get('urlInput')
            confirmation_message = process_url(url2)
            return redirect(url_for('display_url', url=confirmation_message))
        else:
            return render_template('index.html', url=url)
    else:
        return redirect(url_for('home'))

@app.route("/popular", methods=['GET'])
def tracked_page():
    top_registered_urls = get_top_registered_urls()
    return render_template('popular.html', top_registered_urls=top_registered_urls)

if __name__ == "__main__":
    app.run(debug=True)

After implementing the changes to retrieve and display the top favorite websites in the newly created route, let’s check how these modifications appear in our application.

To do so, first restart the application. Open a terminal or command prompt, navigate to the directory containing our Flask application, and execute the command flask run. Alternatively, you can run the application in debug mode by executing the script directly with python app.py or python3 app.py, depending on your Python version.

Once the application is running, open a web browser and navigate to http://127.0.0.1:5000/. To view the new route, append /popular to the end of the URL, so it becomes http://127.0.0.1:5000/popular. If your application is hosted on a different IP address or port, use the address displayed in your terminal or command prompt, followed by /popular.

On this route, you will see the page we just created displaying a table with the most popular URLs, as shown in Figure 16.

Figure 16: View of the top favorite websites page

Amazing! We can see that the table of the top favorite websites is displayed correctly. Currently, we only see two websites, www.google.com and www.facebook.com, each with a registration count of 1. This is because these are the only websites we’ve added so far.

If you have registered additional URLs or logged these URLs more times, you would see different values reflecting those changes.

However, you might have noticed a potential issue—accessing the popular URLs page is not very intuitive. We had to manually modify the URL by adding /popular to reach this page, which can make it difficult for users to find. Additionally, users might not be aware that this page exists, so they may never visit it.

The simplest solution to this problem is to create a navigation bar. By incorporating a navigation bar into our application, we can make it much easier for users to access various pages, including the popular URLs page.

Creating a navigation bar

As we have just noticed, navigating through the pages of our application is not very straightforward. Whenever we develop applications, we must prioritize user experience (UX), ensuring that users find it comfortable and easy to interact with and use our application. In this case, to improve navigation between pages, what we need is a navigation bar.

We will design this navigation bar to blend seamlessly with our existing theme. Specifically, we will use the same green accent color that characterizes our design to make the navigation bar stand out against the gray background of the pages. The navigation bar will feature the different pages of our application—such as the home page and the top favorite websites—displayed on the right side using white text.

To enhance usability, the currently active tab will be highlighted with a slightly brighter shade of green. This will provide users with clear visual feedback about their location within the application. This approach not only aims to improve navigation but also ensures consistency with our overall design aesthetic. Figure 17 visually illustrates the envisioned design for the navigation bar.

Figure 17: Schematic design for the navigation bar

To begin creating our navigation bar, we will start with HTML. Creating navigation bars with HTML is quite simple. We just need to use the <nav> tag to designate a section of our webpage specifically for navigation links. Within this <nav> section, we typically include an unordered list (<ul>) that contains list items (<li>) with hyperlinks (<a>) pointing to the various pages or sections of the site. For example, we could use the following HTML to create a navigation list containing the home and the top favorite websites (popular) pages:

<nav>
  <!-- We create an unordered list: ul -->
  <ul>
      <!-- We add the routes/links as elements of this list -->
      <li><a href="/">Home</a></li>
      <li><a href="/popular">Popular</a></li>
  </ul>
</nav>

We can save the HTML for this navbar in a file named navbar.html.

Next, we will style the navbar we just created by adding a CSS file that will set its background to green and ensure it stretches across the top of the page. The items within the navbar will be aligned to the right and centered vertically. We’ll also add subtle shadowing to give the navbar a slightly lifted appearance. The links will be styled in white and will change color when hovered over to enhance user interaction.

/* Styles for the navigation bar */
nav {
    background-color: #4CAF50; /* Light green background color */
    color: #fff; /* White text color */
    height: 4.5%; /* Height of the navigation bar */
    display: flex; /* Use flexbox for layout */
    justify-content: flex-end; /* Align items to the right edge */
    align-items: center; /* Center items vertically */
    box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1); /* Add subtle shadow for a lifted effect */
    padding-right: 2%; /* Add padding to the right side */
}

nav ul {
    margin: 0; /* Remove default margin */
    padding: 0; /* Remove default padding */
    list-style-type: none; /* Remove default list bullets */
    display: flex; /* Use flexbox for better alignment */
}

nav ul li {
    margin-left: 20px; /* Add space between list items */
}

nav ul li:first-child {
    margin-left: 0; /* Remove left margin for the first item */
}

nav ul li a {
    padding: 12.5px 20px; /* Adjust padding for better spacing */
    color: #fff; /* White text color for links */
    text-decoration: none; /* Remove underline from links */
    transition: background-color 0.3s; /* Smooth transition for background color on hover */
}

nav ul li a:hover {
    background-color: #3e8e41; /* Change background color on hover */
}

We can save this CSS code in a file named navbar.css. Once we have created this stylesheet, we can link it to our navbar.html file by adding the following line of code within the <head> section:

<link rel="stylesheet" href="{{ url_for('static', filename='styles/navbar.css') }}">

This will apply the styling rules from navbar.css to the navbar in our HTML file, ensuring the design enhancements we have specified are implemented.

Incorporating the navigation bar into our templates

At this point, a question arises: how can we integrate the navigation bar we have just created into our templates? Jinja2 templates allow us to reuse parts of HTML code, similar to how we reuse functions in Python.

By applying this principle, we can not only add our navigation bar to different pages but also make our templates more elegant and maintainable. For example, both the homepage (index.html) and the top favorite websites page (popular.html) share several common elements. To avoid repetition and streamline our code, we can create a base template, which we’ll call base_template.html.

This base template will serve as a foundation for all our pages, allowing us to define shared components like the navigation bar, footer, or any other recurring elements in one place. Individual pages can then extend this base template, adding or overriding specific content as needed. This approach not only improves code organization but also makes updates easier—if you need to change the navigation bar, you only need to do it once in the base template, and the changes will be reflected across all pages that use it.

In our case, we could create the base_template.html using the following code:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>{% block title %}{% endblock %}</title>
    <link rel="stylesheet" href="{{ url_for('static', filename='/styles/base_template.css')}}">
    {% block styles %}{% endblock %}
</head>
<body>
  {% include 'navbar.html' %}
  <div class="content">
    {% block content %}{% endblock %}
  </div>
</body>
</html>

In this template, we use several Jinja2 features, including blocks and includes, to structure our code effectively.

Blocks allow child templates to insert or override specific content. They are defined using the {% block %} syntax and serve as content placeholders, allowing child templates to override or add content to these sections. For example, the <title>{% block title %}{% endblock %}</title> block allows child templates of base_template.html to define their own titles.

Another example is the {% block styles %}{% endblock %} block, which is used for adding custom styles. In this way, child templates can add their own CSS files within this block. Similarly, the {% block content %}{% endblock %} block acts as a placeholder for the main content of any page that extends this template.

Includes enable the reuse of common components, like a navigation bar, across multiple pages. Basically, they are used to insert content from one template into another. They are defined using the {% include %} statement. For example, in our template, we use {% include 'navbar.html' %} to insert the navigation bar from navbar.html. This promotes reusability and maintainability. If the navigation bar needs updating, you only need to modify the navbar.html file, and the changes will be reflected across all pages that extend this template or include the navigation bar from navbar.html.

Additionally, we have linked a CSS file named base_template.css to this template, in order to establish a consistent look and feel across all our pages. This file will include the shared styles that are common to all pages, ensuring uniformity in elements such as layout, typography, and color schemes throughout the site. Below, you can find the code for this CSS file:

body {
    font-family: Arial, sans-serif;
    margin: 0;
    padding: 0;
    height: 100vh;
}

.content {
    display: flex;
    align-items: center;
    padding-top: 5vh;  /* Adds padding at the top to avoid overlap with the navbar */
    height: 90.5vh;   /* Sets the height of the container to 90.5% of the viewport height, i.e., area remaining after navbar and top padding */
    background-color: #f0f0f0;
    flex-direction: column;
    justify-content: center; /* Horizontally centers the content */
}

We can now rewrite our two main templates—the homepage (index.html) and the popular URLs page (popular.html)—as children of the base template, meaning that these templates will inherit the structure and layout defined in base_template.html, simplifying their code.

Here’s how the index.html template will look after using the base template:

{% extends "base_template.html" %}

{% block title %}Home{% endblock %}

{% block content %}
    <div class="container">
         <h1>What is your favorite website?</h1>
          <form method="post">  <!-- Specify method="post" for form submission -->
              <input type="text" name="urlInput" placeholder="Enter URL" class="form-text-input" required>
              <br>
              <button type="submit" class="submit-btn">Register URL</button>
          </form>
          {% if url %}
            <div id="confirmationContainer">
                <p>{{ url }}</p>  <!-- Display URL registration confirmation -->
            </div>
          {% endif %}
      </div>
{% endblock %}

{% block styles %}
<link rel="stylesheet" href="{{ url_for('static', filename='/styles/home.css')}}">
{% endblock %}

In this updated version of the index.html template, we have removed all the elements already included in base_template.html and added the {% extends "base_template.html" %} statement to indicate that this template inherits from the base template.

The {% block title %}Home{% endblock %} block sets the title for this page, which will appear in the browser tab.

The {% block content %}{% endblock %} section specifies the HTML content to be placed within the main content area defined by the base template. This includes the main elements for the homepage, such as the URL checking form.

Lastly, the {% block styles %}{% endblock %} block is used to include an additional CSS file, home.css, to apply specific styles to this page.

We then update home.css by removing any styles that are already covered in base_template.css. The updated home.css will look like this:

.container {
    text-align: center; /* Centers the text inside the container */
    margin-bottom: 20px; /* Adds bottom margin to separate the form from the result */
}

.form-text-input {
    width: 300px; /* Sets the width of the URL input field */
    padding: 10px; /* Adds padding around the input field */
    font-size: 16px; /* Sets the font size of the input field */
    border: 1px solid #ccc; /* Sets a 1px solid border with a light gray color */
    border-radius: 5px; /* Applies rounded corners to the input field */
    margin-bottom: 20px; /* Adds bottom margin to separate the input field from the button */
}

.submit-btn {
    background-color: #4CAF50; /* Sets the background color of the submit button */
    color: white; /* Sets the text color of the submit button */
    padding: 10px 20px; /* Adds padding around the button text */
    font-size: 16px; /* Sets the font size of the button text */
    border: none; /* Removes the border from the submit button */
    border-radius: 5px; /* Applies rounded corners to the submit button */
    cursor: pointer; /* Changes the cursor to a pointer when hovering over the button */
    transition: background-color 0.3s; /* Adds a smooth transition to the button's background color */
}

.submit-btn:hover {
    background-color: #3e8e41; /* Changes the button's background color when hovering over it */
}

#confirmationContainer {
    padding: 20px; /* Adds padding around the content of the result container */
    background-color: #f0eaea; /* Sets the background color of the result container */
    border: 1px solid #ccc; /* Sets a 1px solid border with a light gray color */
    border-radius: 5px; /* Applies rounded corners to the result container */
    box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1); /* Adds a shadow effect to the result container */
    margin-top: 20px; /* Adds top margin to visually separate the confirmation container from the form */
}

Similarly, for the top favorite websites page (popular.html and popular.css), we follow the same approach: we remove the elements already defined in the base template and extend it. You will find the code for these files in the “Final Code” section (link to the updated code for popular.html and popular.css).

With these changes, our application will look as shown in Figure 18, and as you can see, navigating between different pages is much easier and more convenient!

Figure 18: Using the application with the new navigation bar

Final code

Final file structure

- my_flask_app/                    (Main folder of the application)
    |
    |- app.py                      (Main file for the Flask application)
    |
    |- .env                        (File to store environmental variables)
    |
    |- templates/                  (Folder to store HTML templates)
    |    |
    |    |- base_template.html     (HTML template for the base template)
    |    |
    |    |- index.html             (HTML template for the main page)
    |    |
    |    |- navbar.html            (HTML template for the navigation bar)
    |    |
    |    |- popular.html           (HTML template for the top favorite websites page)
    |
    |- static/                     (Folder to store static files such as CSS, JavaScript, images, etc.)
         |
         |- styles                 (Folder to store style sheets, such as CSS files)
            |
            |- base_template.css   (CSS file to style the base template elements)
            |
            |- home.css            (CSS file to style the main page)
            |
            |- navbar.css          (CSS file to style the navigation)
            |
            |- popular.css         (CSS file to style the top favorite websites page)

Python - app.py

from flask import Flask, request, render_template, redirect, url_for
import os
from dotenv import load_dotenv
import psycopg
import re 

load_dotenv()

db_user = os.getenv('DB_USER')
db_password = os.getenv('DB_PASSWORD')
db_host = os.getenv('DB_HOST')
db_port = os.getenv('DB_PORT')
db_name = os.getenv('DB_NAME')

def connect_to_database():
    try:
        conn = psycopg.connect(
            dbname=db_name,
            user=db_user,
            password=db_password,
            host=db_host,
            port=db_port
        )
        return conn
    except Exception as e:
        print(e)

def is_valid_url(url: str) -> bool:
    pattern = r"^(https?:\/\/)?(www\.)?[a-zA-Z0-9]+\.[a-zA-Z]+$"
    return bool(re.fullmatch(pattern, url))

def insert_url_into_database(url: str):
    try:
        conn = connect_to_database()
        cur = conn.cursor()
        cur.execute("INSERT INTO registered_urls (url) VALUES (%s)", (url,))
        conn.commit()
    except Exception as e:
        print("Error inserting URL into the database:", e)
    finally:
        conn.close()

def process_url(url: str) -> str:
    if is_valid_url(url):
        insert_url_into_database(url)
        confirmation_message = "You have successfully registered the following URL: " + url
    else:
        confirmation_message = "The URL you entered is not valid. Please check the format and try again."
    return confirmation_message

def get_top_registered_urls():
    try:
        conn = connect_to_database()
        cur = conn.cursor()
        cur.execute("""
            SELECT url, COUNT(*) as count
            FROM registered_urls
            GROUP BY url
            ORDER BY count DESC
            LIMIT 10;
        """)
        top_registered_urls = cur.fetchall()
        return top_registered_urls
    except Exception as e:
        print(f"Error retrieving the URLs: {e}")
    finally:
        if conn:
            conn.close()

app = Flask(__name__)

@app.cli.command("init-db-table")
def create_table():
    try:
        conn = connect_to_database()
        cur = conn.cursor()

        cur.execute("""
            CREATE TABLE IF NOT EXISTS registered_urls (
                id SERIAL PRIMARY KEY,
                url TEXT
            )
        """)

        conn.commit()
        print("create_table function executed correctly")
    except Exception as e:
        print(e)
    finally:
        conn.close()

@app.route("/", methods=['GET', 'POST'])
def home():
    if request.method == 'POST':
        url = request.form.get('urlInput')
        confirmation_message = process_url(url)
        return redirect(url_for('display_url', url=confirmation_message))
    else:
        return render_template('index.html')

@app.route("/display_url/<path:url>", methods=['GET', 'POST'])
def display_url(url=None):
    if url:
        if request.method == 'POST':
            url2 = request.form.get('urlInput')
            confirmation_message = process_url(url2)
            return redirect(url_for('display_url', url=confirmation_message))
        else:
            return render_template('index.html', url=url)
    else:
        return redirect(url_for('home'))

@app.route("/popular", methods=['GET'])
def tracked_page():
    top_registered_urls = get_top_registered_urls()
    return render_template('popular.html', top_registered_urls=top_registered_urls)

if __name__ == "__main__":
    app.run(debug=True)

Environmental variables file - .env

DB_USER = 'your_username'
DB_PASSWORD = 'your_password'
DB_HOST = 'database_host'
DB_PORT = 'port_number'
DB_NAME = 'database_name'

Base Template

HTML - base_template.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>{% block title %}{% endblock %}</title>
    <link rel="stylesheet" href="{{ url_for('static', filename='/styles/base_template.css')}}">
    {% block styles %}{% endblock %}
</head>
<body>
  {% include 'navbar.html' %}
  <div class="content">
    {% block content %}{% endblock %}
  </div>
</body>
</html>

CSS - base_template.css

body {
    font-family: Arial, sans-serif;
    margin: 0;
    padding: 0;
    height: 100vh;
}

.content {
    display: flex;
    align-items: center;
    padding-top: 5vh;  /* Adds padding at the top to avoid overlap with the navbar */
    height: 90.5vh;   /* Sets the height of the container to 90.5% of the viewport height, i.e., area remaining after navbar and top padding */
    background-color: #f0f0f0;
    flex-direction: column;
    justify-content: center; /* Horizontally centers the content */
}

Navigation bar

HTML - navbar.html

<nav>
  <ul>
      <li><a href="/">Home</a></li>
      <li><a href="/popular">Popular</a></li>
  </ul>
</nav>

<link rel="stylesheet" href="{{ url_for('static', filename='/styles/navbar.css')}}">

CSS - navbar.css

/* Styles for the navigation bar */
nav {
    background-color: #4CAF50; /* Light green background color */
    color: #fff; /* White text color */
    height: 4.5%; /* Height of the navigation bar */
    display: flex; /* Use flexbox for layout */
    justify-content: flex-end; /* Align items to the right edge */
    align-items: center; /* Center items vertically */
    box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1); /* Add subtle shadow for a lifted effect */
    padding-right: 2%; /* Add padding to the right side */
}

nav ul {
    margin: 0; /* Remove default margin */
    padding: 0; /* Remove default padding */
    list-style-type: none; /* Remove default list bullets */
    display: flex; /* Use flexbox for better alignment */
}

nav ul li {
    margin-left: 20px; /* Add space between list items */
}

nav ul li:first-child {
    margin-left: 0; /* Remove left margin for the first item */
}

nav ul li a {
    padding: 12.5px 20px; /* Adjust padding for better spacing */
    color: #fff; /* White text color for links */
    text-decoration: none; /* Remove underline from links */
    transition: background-color 0.3s; /* Smooth transition for background color on hover */
}

nav ul li a:hover {
    background-color: #3e8e41; /* Change background color on hover */
}

Home page

HTML - index.html

{% extends "base_template.html" %}

{% block title %}Home{% endblock %}

{% block content %}
    <div class="container">
         <h1>What is your favorite website?</h1>
          <form method="post">  <!-- Specify method="post" for form submission -->
              <input type="text" name="urlInput" placeholder="Enter URL" class="form-text-input" required>
              <br>
              <button type="submit" class="submit-btn">Register URL</button>
          </form>
          
          {% if url %}
            <div id="confirmationContainer">
                <p>{{ url }}</p>  <!-- Display URL registration confirmation -->
            </div>
          {% endif %}
      </div>
{% endblock %}

{% block styles %}
<link rel="stylesheet" href="{{ url_for('static', filename='/styles/home.css')}}">
{% endblock %}

CSS - home.css

.container {
    text-align: center; /* Centers the text inside the container */
    margin-bottom: 20px; /* Adds bottom margin to separate the form from the result */
}

.form-text-input {
    width: 300px; /* Sets the width of the URL input field */
    padding: 10px; /* Adds padding around the input field */
    font-size: 16px; /* Sets the font size of the input field */
    border: 1px solid #ccc; /* Sets a 1px solid border with a light gray color */
    border-radius: 5px; /* Applies rounded corners to the input field */
    margin-bottom: 20px; /* Adds bottom margin to separate the input field from the button */
}

.submit-btn {
    background-color: #4CAF50; /* Sets the background color of the submit button */
    color: white; /* Sets the text color of the submit button */
    padding: 10px 20px; /* Adds padding around the button text */
    font-size: 16px; /* Sets the font size of the button text */
    border: none; /* Removes the border from the submit button */
    border-radius: 5px; /* Applies rounded corners to the submit button */
    cursor: pointer; /* Changes the cursor to a pointer when hovering over the button */
    transition: background-color 0.3s; /* Adds a smooth transition to the button's background color */
}

.submit-btn:hover {
    background-color: #3e8e41; /* Changes the button's background color when hovering over it */
}

#confirmationContainer {
    padding: 20px; /* Adds padding around the content of the result container */
    background-color: #f0eaea; /* Sets the background color of the result container */
    border: 1px solid #ccc; /* Sets a 1px solid border with a light gray color */
    border-radius: 5px; /* Applies rounded corners to the result container */
    box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1); /* Adds a shadow effect to the result container */
    margin-top: 20px; /* Adds top margin to visually separate the confirmation container from the form */

}

Top favorite Websites pages

HTML - popular.html

{% extends "base_template.html" %}

{% block title %}Popular Websites{% endblock %}

{% block content %}
  <div class="table-container">
      <h1>Top Favorite Websites</h1>
      <table>
  <thead>
    <tr>
<th>URL</th>
<th>Tracking Count</th>
    </tr>
  </thead>
  <tbody>
    {% for top_url in top_registered_urls %}
<tr>
  <td>{{ top_url[0] }}</td>
  <td>{{ top_url[1] }}</td> 
</tr>
    {% endfor %}
  </tbody>
</table>
    </div>
  </div>
{% endblock %}

{% block styles %}
<link rel="stylesheet" href="{{ url_for('static', filename='/styles/popular.css')}}">
{% endblock %}

CSS - popular.css

body {
    font-family: Arial, sans-serif;
    margin: 0;
    padding: 0;
    height: 100vh; /* Ensure the body takes up the full height of the viewport */
}

/* CSS styling for the table format */
.table-container h1 {
    text-align: center; /* Center-aligns the table header */
}

.table-container table {
    width: 50vw; /* Set table width to 50% of the viewport width */
    border-collapse: collapse; /* Collapse borders for a cleaner look */
    margin: auto; /* Center the table horizontally */
}

.table-container th,
.table-container td {
    padding: 12px; /* Add padding for better readability */
    border: 1px solid #ddd; /* Light gray border for table cells */
    text-align: left; /* Left-align text in table cells */
}

.table-container th {
    background-color: #4CAF50; /* Dark green background for header cells */
    color: white; /* White text color for header cells */
}

.table-container tr:nth-child(even) {
    background-color: #f2f2f2; /* Light gray background for even rows */
}

.table-container tr:hover {
    background-color: #ddd; /* Change table row background color on hover for better visibility */
}

Summary

In this second post of the data-driven web applications basics, we have expanded our application by integrating a database to store user-registered URLs. This enhancement allows us to track and identify the most popular URLs. As a result, we have added a new page to our application that displays the top favorite websites—those that have been registered the most frequently—in a table format.

We have also covered several best practices for developing such applications. For instance, we emphasized the importance of validating user data before processing or storing it to ensure data integrity and security. We used .env files for storing connection and setup details rather than hardcoding them into the main code, which helps in managing configurations more securely and flexibly.

Additionally, we discussed the benefits of encapsulating functions to improve code organization and maintenance, employing design patterns to enhance code structure, and considering the overall UX to ensure a positive interaction with the application.