Welcome back! In the last lesson, you learned how to make your word prediction game smarter by scoring guesses based on how close their meaning is to the correct answer. Now, let’s take your game to the next level by adding a leaderboard.

A leaderboard is a simple but powerful feature. It lets players see how their scores compare to others, which makes the game more exciting and competitive. In this lesson, you’ll learn how to store player scores and display the top results for each day. By the end, you’ll have the foundation for a daily leaderboard that updates as people play.

Before we dive in, let’s quickly remind ourselves what a model is. In web development, a model is a way to represent and store data in your application. With SQLAlchemy, we define models as Python classes that describe the structure of our data.

A model acts as a blueprint for a table in a database. For example, if you want to keep track of high scores, you need a model that describes what information you want to save for each score — such as the player’s name, their score, and the date.

Here’s how you might define a simple model using SQLAlchemy’s declarative system:

In this example, ExampleModel is a model with two fields: id and name. Each time you add a new entry, it creates a new row in the database.

Now, let’s build a model for our leaderboard. We want to store each player’s name, their score, and the date they played. Let’s start step by step.

First, we need to import the right tools and set up our base for models:

Next, let’s define our HighScore model:

Let’s break down what each part does:

• id: This is a unique number for each score entry. It helps us keep track of each row.
• name: This stores the player’s name. It’s a string and cannot be empty.
• score: This is the player’s score. It must be a number and cannot be empty.
• date: This records the date the score was achieved. By default, it uses today’s date.

The to_dict method helps us convert a score entry into a format that’s easy to use in our app or send to the frontend.

Now that we have a model, let’s see how to save a new score and fetch the top scores for a given day. This is how your game will keep track of who’s doing well and display the results to everyone.

Before you can save or fetch scores, you need to connect to your database and create a session. This session is what you’ll use to talk to the database.

• SQLALCHEMY_DATABASE_URL is the connection string that tells SQLAlchemy where your database is. Here, "sqlite:///highscores.db" means you’re using a SQLite database stored in a file called highscores.db in your project directory.
• create_engine sets up the connection to your database. The connect_args={"check_same_thread": False} part is specific to SQLite and allows the database connection to be shared across different threads, which is useful when using FastAPI.
• sessionmaker is a factory for creating new database sessions. The parameters:
  • autocommit=False means changes are not automatically saved to the database—you have to call commit() manually. This gives you more control over when data is saved.
  • autoflush=False means changes are not automatically flushed (written) to the database before each query. This can help avoid unexpected database writes.
  • bind=engine tells the sessionmaker which database engine to use for connections.

To save a player’s score, you’ll need a function that takes the player’s name, their score, and the date. This function will create a new HighScore entry and add it to the database.

How it works:

• The function takes a database session, the player’s name, their score, and an optional date string.
• If a date is provided, it’s converted to a date object. If not, today’s date is used.
• A new HighScore object is created with the provided data.
• db_session.add(entry) adds the new score to the session.
• db_session.commit() saves the new score to the database.

Example usage:

This will save Alice’s score of 90 for today’s date.

To display the leaderboard, you need to fetch all scores for a specific day and sort them from highest to lowest. Here’s a function to do that:

How it works:

• The function takes a database session and an optional date string.
• It determines which date to use (either the provided date or today).
• It builds a query to select all HighScore entries for that date, sorted by score in descending order.
• It executes the query and gets all matching scores.
• It converts each score to a dictionary using the to_dict method.

Example usage:

Suppose we have three scores for today:

If we call get_leaderboard(db_session), we’ll get:

This list is ready to be shown on your game’s leaderboard!

In this lesson, you learned how to create a leaderboard for your word prediction game. You built a HighScore model to store player names, scores, and dates. You also wrote functions to save new scores and fetch the top scores for any day.

These are the building blocks for a daily leaderboard that updates as people play. Up next, you’ll get to practice these skills by working with the leaderboard code yourself. This hands-on practice will help you get comfortable with saving and displaying scores in your own game. Good luck!