Adding the vote date to our polling app

To work with dates and times a little bit in our polling app, let's add the date when a vote was cast to our votes.

We'll first of all add a column to our table:

-CREATE_VOTES = "CREATE TABLE IF NOT EXISTS votes (username TEXT, option_id INTEGER);"
+CREATE_VOTES = "CREATE TABLE IF NOT EXISTS votes (username TEXT, option_id INTEGER, vote_timestamp INTEGER);"

Then we'll accept the timestamp in our database method:

-def add_poll_vote(connection, username: str,  option_id: int):
+def add_poll_vote(connection, username: str, vote_timestamp: float, option_id: int):
     with get_cursor(connection) as cursor:
-        cursor.execute(INSERT_VOTE, (username, option_id))
+        cursor.execute(INSERT_VOTE, (username, option_id, vote_timestamp))

Now, we must pass in here a timestamp representing the current time in UTC. Let's go to our Option model class, and in the vote() method let's calculate the current UTC timestamp as we have learned.

Then, pass it in to the add_poll_vote() function:

@with_connection
def vote(self, username: str, connection):
    current_datetime_utc = datetime.datetime.now(tz=pytz.utc)
    current_timestamp = current_datetime_utc.timestamp()
    database.add_poll_vote(connection, username, current_timestamp, self.id)

Finally, we can display the vote date in app.py when we're printing the votes for an option. What I'm going to do is add another user prompt at the end of show_poll_votes() to ask them whether they want to see detailed vote information:

def show_poll_votes():
    ...
    
    vote_log = input("Would you like to see the vote log? (y/N) ")

    if vote_log == "y":
        _print_votes_for_options(options)

Then I'll write the _print_votes_for_options() function, which does that.

To display date information we have to use the utcfromtimestamp() method in the datetime model, and then use pytz to localize the date into the user's timezone.

For now I'm going to hardcode the user's timezone as Europe/London, but you might want to store this in the user's table or a config file.

def _print_votes_for_options(options: List[Option]):
    for option in options:
        print(f"-- {option.text} --")
        for vote in option.votes:
            naive_datetime = datetime.datetime.utcfromtimestamp(vote[2])
            utc_date = pytz.utc.localize(naive_datetime)
            local_date = utc_date.astimezone(pytz.timezone("Europe/London")).strftime("%Y-%m-%d %H:%M")
            print(f"\t- {vote[0]} on {local_date}")

That's about it!

The flow for this change is something you'll do quite often:

1. Figure out the data you want to store
2. Work backwards from the database to add the necessary flow:
   1. Change the database-interaction function.
   2. Change the model calling the database function.
   3. Change the user-facing code that uses the model.

Wrapping Up

Thank you for joining me for this section!

We have learned:

• How to better structure our Python applications by using models.
• How to reduce duplication throughout our code by using decorators.
• How to work with dates and times in Python and PostgreSQL.

I'll see you in the next one!