My Code Choices: Episode Two | Using SQLite Instead Of A Dict

In “My Code Choices: Episode Two” I continue discussing the code choices I used in my web apps by explaining why I used SQLite in “Where Should I Go Eat?” instead of a Python Dictionary (or Dict for short).

A Quick Explanation About SQLite

SQLite, according to its website, is a “C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.” It is also the most used database in the world, and works well in smartphones and many computers.

The application has some prominent bragging points:

• The developers will keep SQLite backwards compatible until 2050
• The application transfers rich content via containers between computers
• There are over 1 trillion SQLite databases in use

Finally, the application’s source code is in the public domain, and available for anyone to use for free!

A Quick Explanation About Python Dicts

Dictionaries are a built-in data type in Python programming language. Instead of using numbers as an index, Dicts use key-value pairs. Each key-value pair must be unique with a Dict. Those pairs can be any of the following:

• Strings
• Tuples (only if they’re immutable)
• Numbers

The main reason to use a Dict is to keep an unchanging running record of data. A great example is an address book. A person’s name, email address, and telephone numbers rarely change. So using a Dictionary to contain that information in the code would be a good choice.

My Code Choices: Episode Two | Using SQLite Instead Of A Dict

While coding one version of Where Should I Go Eat? I wanted to give the user the ability to click on the randomly-chosen restaurant and go to that restaurant’s website to order food online. I knew I had to create a connection between each restaurant and its specific URL. Thus, I could use a data type like a Dictionary or a database.

The database option soon won out because I had more experience using them. Yes, I had experience using Dictionaries too, but I really wanted to use SQLite again.

I created a file called database.py creates a database connection to the SQLite database:

    try:
        connection = sqlite3.connect("/static/files/restaurant_urls")
        return connection
    except Exception as err:
        print(err)

    return connection

Inisde the file is the database table which resides in the restaurant_urls.db. The latter contains the restaurant’s name and its URL for online ordering:

    database = r"/static/files/restaurant_urls.db"

    sql_create_restaurant_urls_table = """
    CREATE TABLE IF NOT EXISTS restaurant_urls (
        restaurant_id INTEGER PIMARY KEY, 
        restaurant_name TEXT NOT NULL, 
        restaurant_url TEXT NOT NULL)
    """

If the table isn’t present I wrote the code to have one created:

def create_table(connection, create_table_sql):
    """
    This function creats a table from the create_table_sql statement
    :param connection: The connection object
    :param create_table_sql: The CREATE TABLE statement
    :return:
    """

    try:
        db_cursor = connection.cursor()
        db_cursor.execute(create_table_sql)
    except Exception as err:
        print(err)
    
# Create the database connection
    conn = create_connection(database)

    # Create the tables
    if conn is not None:
        # Create the restaurant_urls table
        create_table(conn, sql_create_restaurant_urls_table)
    else:
        print("Cannot create the database connection.")

If there’s an error with the database connection then I’ll get an error in the logs. This would happen if someone the database file becoming missing on the web server.

How This All Connects Together

This all connects together in the main.py file. In the main function I open and read the restaurant.txt file, loop over it reading each line, and then randomly choose a restaurant from that file using the built-in “random.choice” function.

Then I connect to the database I created above to query the database using the randomly-selected restaurant to get its URL for online ordering. Then I render that all on the index.html page. Here’s the code:

def main():
    """
    This function opens and read the restaurant.txt file which is located in the /static/files directory.
    Then a loop runs over the entire file, reading each line. The loop collects all the lines as a list, and then pulls
    out a restaurant randomly using the random.choice function.

    :return: The function returns a random restaurant to the index.html template, which displays it
    on the website.
    """
    # Create the database connection
    db_connection = sqlite3.connect("static/files/restaurant_urls")

    # Created the cursor
    db_cursor = db_connection.cursor()

    with open("static/files/restaurant.txt", "r") as restaurant:
        restaurant_list = restaurant.readlines()

    chosen_restaurant = random.choice(restaurant_list)

    # Removed the newline character at the end of the variable so the database query is successful
    chosen_restaurant = chosen_restaurant.strip() 

    db_cursor.execute("SELECT restaurant_url FROM restaurant_urls WHERE restaurant_name = ?", (chosen_restaurant,))
    search_restaurant_link = db_cursor.fetchone()[0]

    # Close the database connection
    db_connection.close()  

    return render_template("index.html", restaurant=chosen_restaurant, restaurant_link=search_restaurant_link)

Getting this code to work was a little tricky because I discovered after some debugging the randomly-selected restaurant has a newline character at the end. Thus, the database query would always fail. That’s why I use the built-in “strip” function.