# Building the parts we already know

Some of this application will be similar to what we've already built in the past, so instead of boring you by repeating the same stuff, I'll just give you the code.

This app is about creating and voting on polls, so you already know what the application does.

In the database, we will have these tables:

polls, to keep track of poll name and who created it.
options, to keep track of the different options in each poll.
votes, to keep track of who voted for which option.

The polls table looks like this:

id SERIAL PRIMARY KEY	title TEXT	owner TEXT

The options table looks like this:

id SERIAL PRIMARY KEY	option_text TEXT	poll_id INTEGER

The votes table looks like this:

username TEXT	option_id INTEGER

# `app.py`

Here's the app.py code. Take your time looking through it!

A couple new things:

To calculate the percentage of votes for each option, we will use the database to divide the votes in each option by the total number of votes. If nobody has voted in a poll yet, this raises a DivisionByZero error since it tries to calculate 0/0. That's why we catch it and show the appropriate message instead.
We ask users for the DATABASE_URI they want to connect to, but if they don't provide one we'll use the .env file.
The code to access menu options is structured a bit differently. More information on this is given below the code shown.

import os
import psycopg2
from psycopg2.errors import DivisionByZero
from dotenv import load_dotenv
import database


DATABASE_PROMPT = "Enter the DATABASE_URI value or leave empty to load from .env file: "
MENU_PROMPT = """-- Menu --

1) Create new poll
2) List open polls
3) Vote on a poll
4) Show poll votes
5) Select a random winner from a poll option
6) Exit

Enter your choice: """
NEW_OPTION_PROMPT = "Enter new option text (or leave empty to stop adding options): "


def prompt_create_poll(connection):
    poll_title = input("Enter poll title: ")
    poll_owner = input("Enter poll owner: ")
    options = []

    while (new_option := input(NEW_OPTION_PROMPT)):
        options.append(new_option)

    database.create_poll(connection, poll_title, poll_owner, options)


def list_open_polls(connection):
    polls = database.get_polls(connection)

    for _id, title, owner in polls:
        print(f"{_id}: {title} (created by {owner})")


def prompt_vote_poll(connection):
    poll_id = int(input("Enter poll would you like to vote on: "))

    poll_options = database.get_poll_details(connection, poll_id)
    _print_poll_options(poll_options)

    option_id = int(input("Enter option you'd like to vote for: "))
    username = input("Enter the username you'd like to vote as: ")
    database.add_poll_vote(connection, username, option_id)


def _print_poll_options(poll_with_options):
    for option in poll_with_options:
        print(f"{option[3]}: {option[4]}")


def show_poll_votes(connection):
    poll_id = int(input("Enter poll you would like to see votes for: "))
    try:
        # This gives us count and percentage of votes for each option in a poll
        poll_and_votes = database.get_poll_and_vote_results(connection, poll_id)
    except DivisionByZero:
        print("No votes yet cast for this poll.")
    else:
        for _id, option_text, count, percentage in poll_and_votes:
            print(f"{option_text} got {count} votes ({percentage:.2f}% of total)")


def randomize_poll_winner(connection):
    poll_id = int(input("Enter poll you'd like to pick a winner for: "))
    poll_options = database.get_poll_details(connection, poll_id)
    _print_poll_options(poll_options)

    option_id = int(input("Enter which is the winning option, we'll pick a random winner from voters: "))
    winner = database.get_random_poll_vote(connection, option_id)
    print(f"The randomly selected winner is {winner[0]}.")


MENU_OPTIONS = {
    "1": prompt_create_poll,
    "2": list_open_polls,
    "3": prompt_vote_poll,
    "4": show_poll_votes,
    "5": randomize_poll_winner
}


def menu():
    database_uri = input(DATABASE_PROMPT)
    if not database_uri:
        load_dotenv()
        database_uri = os.environ["DATABASE_URI"]

    connection = psycopg2.connect(database_uri)
    database.create_tables(connection)

    while (selection := input(MENU_PROMPT)) != "6":
        try:
            MENU_OPTIONS[selection](connection)
        except KeyError:
            print("Invalid input selected. Please try again.")


menu()

# Number formatting

You may have spotted in that code that we're using :.2f to limit the vote percentage to 2 decimal places. If you're not familiar with this syntax, check out this blog post that talks about it in more detail: https://blog.tecladocode.com/python-formatting-numbers-for-printing/ (opens new window).

In the menu, we have something like this:

MENU_OPTIONS = {
    "1": prompt_create_poll,
    "2": list_open_polls,
    "3": prompt_vote_poll,
    "4": show_poll_votes,
    "5": randomize_poll_winner
}

...

while (user_input := input(MENU_PROMPT)) != "6":
    try:
        MENU_OPTIONS[selection](connection)
    except KeyError:
        print("Invalid input selected. Please try again.")

What this is doing is associating each number (from "1" to "5") to a function. Note that the association is to the function name (e.g. prompt_create_poll) and not to the result of executing the function execution (prompt_create_poll()).

If we used prompt_create_poll(), then that would execute the function when the dictionary MENU_OPTIONS is created. That's not what we want.

Instead, inside the while loop we access the dictionary, retrieving the function that the user has selected, with MENU_OPTIONS[user_input]. Then we execute that function and pass the connection variable to it with (connection).

So this retrieves the function from the dictionary and executes the function:

MENU_OPTIONS[user_input](connection)

This new way of accesing menu options is shorter and, when you're accustomed to it, easier to understand too!

# `database.py`

Some of the functions here we are already familiar with.

The ones we are not, I've left empty (just with pass). We will cover how to implement them over the next couple chapters!

Note that we don't have to import psycopg2 because we're getting the connection passed in to every function, so database.py is no longer in charge of handling the connection to the database.

CREATE_POLLS = """CREATE TABLE IF NOT EXISTS polls
(id SERIAL PRIMARY KEY, title TEXT, owner_username TEXT);"""
CREATE_OPTIONS = """CREATE TABLE IF NOT EXISTS options
(id SERIAL PRIMARY KEY, option_text TEXT, poll_id INTEGER, FOREIGN KEY(poll_id) REFERENCES polls (id));"""
CREATE_VOTES = """CREATE TABLE IF NOT EXISTS votes
(username TEXT, option_id INTEGER, FOREIGN KEY(option_id) REFERENCES options (id));"""


SELECT_ALL_POLLS = "SELECT * FROM polls;"
SELECT_POLL_WITH_OPTIONS = """SELECT * FROM polls
JOIN options ON polls.id = options.poll_id
WHERE polls.id = %s;"""

INSERT_OPTION = "INSERT INTO options (option_text, poll_id) VALUES %s;"
INSERT_VOTE = "INSERT INTO votes (username, option_id) VALUES (%s, %s);"


def create_tables(connection):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(CREATE_POLLS)
            cursor.execute(CREATE_OPTIONS)
            cursor.execute(CREATE_VOTES)


def get_polls(connection):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(SELECT_ALL_POLLS)
            return cursor.fetchall()


def get_latest_poll(connection):
    with connection:
        with connection.cursor() as cursor:
            pass


def get_poll_details(connection, poll_id):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(SELECT_POLL_WITH_OPTIONS, (poll_id,))
            return cursor.fetchall()


def get_poll_and_vote_results(connection, poll_id):
    with connection:
        with connection.cursor() as cursor:
            pass


def get_random_poll_vote(connection, option_id):
    with connection:
        with connection.cursor() as cursor:
            pass


def create_poll(connection, title, owner, options):
    with connection:
        with connection.cursor() as cursor:
            pass


def add_poll_vote(connection, username, option_id):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(INSERT_VOTE, (username, option_id))

← What is ACID? RETURNING data from modified rows →

# Building the parts we already know

# app.py

# Number formatting

# The menu code

# database.py

# `app.py`

# `database.py`