Fortune #

In this lab we will create a server for a fortune teller. We will delve deeper into design constraints and functionalities of an API.

[0] Set Up #

💻 First, clone the repository in your unit03_networking folder. Be sure to change yourgithubusername to your actual Github username.

cd ~/desktop/making_with_code/unit03_networking/
git clone https://github.com/the-isf-academy/lab_fortune_yourgithubusername
cd lab_fortuneyourgithubusername

💻 Get the necessary packages

poetry install

💻 Enter the Poetry shell

poetry shell

[1] Add new fortunes #

👀 Look at the definition of the SQL database table in fortunes.sql

Notice the data types of each column

CREATE TABLE IF NOT EXISTS fortunes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    last_updated DATETIME DEFAULT  (STRFTIME('%Y-%m-%d %H:%M:%S', 'now', '+8 hours')),
    statement TEXT NOT NULL,
    is_happy BOOLEAN NOT NULL,
    num_updates INTEGER DEFAULT 0,
    archive BOOLEAN DEFAULT 0
);

💻 Open the database file to add a few fortunes: open database.db.

💻 Add 3 new fortunes using the DB Browser, then save with ⌘+s. It’s important to have a variety of testing data to ensure the api works as expected.

Notice how booleans are represented as a 0 or a 1

[2] Running the Fortune Server #

💻 Start your local server.

python api.py

💻 Test the server using HTTPie: 127.0.0.1:5000/fortune

👀 Look at the JSON response in the help key and make requests to all of the endpoints.

{
  "help": {
    "GET /all": {
      "description": "returns all fortunes",
      "payload": ""
    },
    "POST /new": {
      "description": "adds new fortune to database",
      "payload": "statement: string, is_happy: boolean"
    }
  },
  "overview": "This is the fortune server."
}

[3] API #

When building an API, it is important to consider how users will interact with it. It is the responsiblity of the developer to design an API with user interaction in mind.

We want users to be able to:

filter by the is_happy column
add on to each other’s fortunes
delete fortunes if user has the admin key

💻 Open the api.py file. This server has 2 endpoints:

/new
/all

For each each feature, you will write a helper function to run the SQL commands and then write the endpoint.*

💻 It is up to you to write the add the following features:

/is_happy - filters riddles by is_happy payload
/update_statement - add to the end of an exisitng fortune
/delete - delete a fortune with a specific id if the key is valid

`/is_happy` #

💻 In helpers.py write the get_all_ishappy() function. It should use SQL to fetch all riddles filtered on the is_happy parameter.

parameter: is_happy: boolean

✅ CHECKPOINT:
💻 Test the function in bottom of helpers.py
Be sure to test with False and True. Look at database.db to confirm it is working.
all_fortunes = get_all_ishappy(False) 
for fortune in all_fortunes:
  print(fortune['id'])

💻 In api.py write the /is_happy endpoint. You should get_all_ishappy() function.

HTTP method: GET
Params/Payload: is_happy

✅ CHECKPOINT:

💻 Test the endpoint in the HTTPie desktop app

http://127.0.0.1:5000/fortune/is_happy is_happy=True

✔️ It should return json like:

{
  "fortunes": [
    {
      "fortune": "you will win money everyday",
      "id": 1,
      "is_happy": true,
      "last_updated": "2025-09-15 04:06:14",
      "num_updates": 1
    },

`/update` #

💻 In helpers.py write the update_fortune() function.

parameter: id: int, update_string: string
It should
- add update_string to the end of the existing staetment
- increase num_updates by 1
- updated last_updated to the current datetime
- return the updated fortune

hints ↕

Remove extra spaces after string

statement = "hello, it is monday "
statement.strip() # returns -> "hello it is monday"

Update more than one column

UPDATE fortunes
SET 
  num_updates = num_updates + 1, 
  statement = "updated statement"
WHERE id = 4

Get the current datetime in HKT

(STRFTIME('%Y-%m-%d %H:%M:%S', 'now', '+8 hours')). 
);

✅ CHECKPOINT:
💻 Test the function in bottom of helpers.py
Refresh the database.db file to confirm the fortune is properly updated.
updated_fortune = update_fortune(1, 'money')
print(updated_fortune['statement'], updated_fortune['num_updates'], updated_fortune['last_updated'] )

solution ↕

def update_fortune(id, update_string):
    '''Returns all riddles from the database'''

    conn = get_db_connection()

    fortune = conn.execute(
        """
        SELECT *
        FROM fortunes
        WHERE id=?
        """,(id,)).fetchone()  
    

    new_fortune = fortune['statement'].strip() + " " + update_string

    conn.execute(
        f"""
        UPDATE fortunes
        SET 
            statement = "{new_fortune}",
            num_updates = num_updates + 1,
            last_updated = (STRFTIME('%Y-%m-%d %H:%M:%S', 'now', '+8 hours'))
        WHERE id = {id}
        """,
       
    )
    conn.commit()

    updated_fortune = conn.execute(
        """
        SELECT *
        FROM fortunes
        WHERE id=?
        """,(id,)).fetchone()  

    return updated_fortune

💻 In api.py write the /update endpoint.

HTTP method: PUT
Payload/args: id:integer, update_text:string
if the fortune exists
- call the update_fortune() function
else
- a helpful error message communicating the fortune does not exist

✅ CHECKPOINT:

💻 Test the endpoint in the HTTPie desktop app

http://127.0.0.1:5000/fortune/update id=6 update_text=non-stop

✔️ It should return json like:

{
  "message": "Fortune updated successfully",
  "question": {
    "fortune": "tomorrow will rain non-stop",
    "id": 5,
    "is_happy": false,
    "last_updated": "2025-09-16 14:47:33",
    "num_updates": 2
  }
}

`/search` #

💻 In helpers.py write the serach() function.

parameter: keyword: string
return the fortunes from the database that contains the keyword

hint ↕

Which SQL function is useful to search for a keyword?

You may want to reference this guide.

✅ CHECKPOINT:
💻 Test the function in bottom of helpers.py
all_fortunes = search("win")
for fortune in all_fortunes:
  print(fortune['statement'])

solution ↕

def search(keyword):
    '''Returns all riddles from the database'''

    conn = get_db_connection()

    keyword_fortunes = conn.execute(
        f"""
        SELECT *
        FROM fortunes
        WHERE statement LIKE '%{keyword}%'
        """).fetchall()  
    
    conn.close()

    return keyword_fortunes

💻 In apy.py write the /search endpoint.

HTTP method: get
Payload/args: keyword
It should return all fortunes that contain the keyword.
If no fortunes exist, provide a helpful error message

🤔 Consider:

Which existing endpoint is similar?
How should you format the json that you return?
What should you return to the user if no fortunes match their search term?

✅ CHECKPOINT:

💻 Test the endpoint in the HTTPie desktop app

http://127.0.0.1:5000/fortune/search keyword="surprise"

✔️ It should return json like:

{
  "fortunes": [
    {
      "id": 1,
      "statement": "A surprise pizza is coming",
      "num_updates": 1,
      "is_happy": true,
      "last_updated": "2025-09-15 14:06:14",
    },
    {
      "id": 6,
      "statement": "A surprise typhoon day is in your future",
      "num_updates": 1,
      "is_happy": true, 
      "last_updated": "2025-09-13 15:20:13",
    }
    ]
}

`API documentation` #

When designing an API, it is important to write helpful documentation so others can use it properly.

💻 Look at the current help section by making a GET request to / endpoint in the HTTPie desktop app

{
  "help": {
    "GET /all": {
      "description": "returns all fortunes",
      "payload": ""
    },
    "GET /new": {
      "description": "adds new fortune to database",
      "payload": "statement: string, is_happy: boolean"
    }
  },
  "overview": "This is the fortune server."
}

💻 Add /is_happy, /update, and /search to the "help" section of the JSON.

✅ CHECKPOINT:

💻 Test the endpoint in the HTTPie desktop app

✔️ It should return json like:

{
  "help": {
    "GET /all": {
      "description": "returns all fortunes",
      "payload": ""
    },
    "POST /new": {
      "description": "adds new fortune to database",
      "payload": "statement: string, is_happy: boolean"
    },
     "GET /is_happy": {
      "description": "returns all fortunes filtered by is_happy",
      "payload": "is_happy: boolean"
    },
     "PUT /update": {
      "description": "adds on to an existing fortune statement",
      "payload": "id: integer, update_text: string"
    },
     "GET /search": {
      "description": "returns all fortunes filtered by a keyword in the statement",
      "payload": "keyword: string"
    }
  },
  "overview": "This is the fortune server."
}

[4] Deliverables #

⚡✨
Once you’ve successfully completed the worksheet be sure to fill out this Google form.
💻 Push your work to Github:
git status
git add -A
git status
git commit -m “describe your code and your process here”
be sure to customize this message, do not copy and paste this line
git push

[5] Extensions #

Error Handling #

💻 Try to break your server.

💻 Now, add in proper error handling so the server never crashes, but instead provides helpful error messages.

Archive #

Currently there is no feature to delete a fortune. It can be risky to permanently delete an item from the database, so instead let’s utilize the archive column.

💻 Write a method toggle_archive() that toggles the archive field to True or False, depending on its current state.

💻 Write a new route /change_archive to change the archive field of a fortune with a given id.

💻 Change your exisitng routes (/all, /search, and /all/is_happy) to only return fortunes with archive set to True.

Relational Database #

A relational database connects two databaes together with specific relationships. Learn more HERE.

Here are two example files for creating a many-to-one relationship between tutors and students. Each tutor, can have multiple students.

📖 Example database.sql file. Notice how the student is linked to the tutor by the FOREIGN KEY.

CREATE TABLE tutor (
    tutor_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

CREATE TABLE student (
    student_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    tutor_id INTEGER,
    FOREIGN KEY (tutor_id) REFERENCES tutor(tutor_id)
);

📖 Example init_db.py file. Notice how when a student is created it is explictly linked to a tutor via the tutor_id.

import sqlite3

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

# open database.sql
with open('database.sql') as file:
    connection.executescript(file.read())

# create connection to database
conn = connection.cursor()

# inserts new tutor
conn.execute("INSERT INTO tutor (name) VALUES (?)", ("Emma",))

# inserts 2 students
conn.execute("INSERT INTO student (name, tutor_id) VALUES (?, ?)", ("Kris", 1))
conn.execute("INSERT INTO student (name, tutor_id) VALUES (?, ?)", ("Heidi", 1))

# save changes to db
connection.commit()

# close database
connection.close()

print("-- database initalized --")

💻 Incorporate a many-to-one relational database in the fortune lab. Consider what relationship would make sense in the context of the feature of this API.

Fortune #

[0] Set Up #

[1] Add new fortunes #

[2] Running the Fortune Server #

[3] API #

/is_happy #

/update #

/search #

API documentation #

[4] Deliverables #

[5] Extensions #

Error Handling #

Archive #

Relational Database #

`/is_happy` #

`/update` #

`/search` #

`API documentation` #