All courses
Hamburger Icon
  • Labs icon Lab
  • Core Tech
Labs

Guided: Working with a Relational Database in Python

In this lab, you'll build a Python application that connects to a relational database using the SQLAlchemy ORM to fetch and manage data. You'll be responsible for setting up a connection with the ORM, retrieving data, and establishing relationships across multiple tables. By the end of this lab, you should have gained practical experience with using relational databases in Python.

Get started Contact sales
Labs

Path Info

Level
Clock icon Beginner
Duration
Clock icon 30m
Published
Clock icon Apr 19, 2025

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.

Table of Contents

  1. Challenge

    Introduction

    Overview

    Relational databases are used to store and organize structured data using tables with defined relationships. In this lab, you will be working with relational databases is through SQLAlchemy, an ORM (Object-Relational Mapper) that allows developers to interact with databases using Python classes and objects instead of raw SQL. Although this is far from being the only way to interact with relational databases, SQLAlchemy is currently the most commonly used ORM for relational databases(DBs) through python.

    Application Scenario

    The premise of this lab is that you are trying to estimate ad revenue generated by your site whenever a user visits the site with a specific ad being displayed. If the user clicks the ad, the ad revenue generated gets multiplied by a specific amount. However, the percentage of users who actually click an ad typically varies depending on the advertiser. The formula that is used to estimate your ad revenue is a weighted sum that factors in the base rate paid per visitor, estimated click rate, and the multiplier per click.

    Your site has multiple advertisers that display content on it and each of them has their own values for base rates, click rates, and click multipliers that are stored in a SQLite database. You will need to work with this database using the SQLAlchemy ORM to query data and use it to calculate your ad revenue.

    Goals of the Application

    The first thing to note is that SQLAlchemy is installed as a dependency in the Python virtual environment. To activate the virtual environment during this lab, use the command source .venv/bin/activate. You can check that the virtual environment is activated with which python, which should print a filepath pointing to a python exe within the .venv directory. You will also see workspace prefixed with .venv. To deactive this virtual environment, simply enter the command deactivate.

    The main goals you will need to accomplish with this lab are:

    1. Establishing a connection with the database(db)
    2. Define models for interacting with the database using the SQLAlchemy ORM
    3. Create a relationship between tables with SQLAlchemy and using that data to estimate ad revenue.

    To accomplish these goals, you will be working in the db/advertiser_model.py, db/database.py, revenue.py and seed_data.py files.

    There is also a solution directory for you to check your implementations at any time. In addition, if you ever need to re-seed your database for whatever reason, use the command python seed_data.py.

  2. Challenge

    Defining Models and Connecting to the DB

    Implementing the Base Class Factory and DB Models

    Open the db/advertiser_model.py file. Inside this file, you will find all the relevant imports from the sqlalchemy library needed for this file as well as the AdCampaign class, which will represent the AdCampaign model to be stored in the database. For the purpose of this lab, an AdCampaign is simply a tag that will be attached to an advertiser. As an example, an AdCampaign could be something like a "summer sale" or a "holiday sale" that an advertiser might promote to entice more visitors.

    Now take a look at the structure of AdCampaign, as custom base classes you define for SQLAlchemy will typically follow this format. Each class starts with a __tablename__ representing the name of the table, while the following properties are values that each entry of an AdCampaign can have.

    Each property is a Mapped type that is then mapped to a column using the ORMs mapped_column. In other words, mapped_column allows you to map a property with a certain value to a column within the database.

    Your objective here is to define a new model class called Advertiser similar to how AdCampaign is defined, but before you do so you will need to create the Base class factory.

    A Base class factory takes the metadata from each of your custom base classes which the ORM can then use to generate the tables and entries for your models. To do so, you will first need to define a Base class and then pass it into your custom model classes.

    Instructions 1. Create a `class` called `Base()`. Pass in the `DeclarativeBase` that is imported from SQLAlchemy. 2. Inside this base class, just leave a `pass` statement. 3. Modify `AdCampaign`s class signature to be `class AdCampaign(Base)`. 4. Modify `Advertiser`s class signature to be `class Advertiser(Base)`.

    Now that the Base class factory is setup, you will need to implement the Advertiser model.

    Instructions 1. Create a `__tablename__` and pass it the name `advertisers` 2. Define an `id` and `name` property just like they are defined in `AdCampaign` 3. Define `base_revenue`, `click_rate`, and `click_multiplier` properties. These properties should be mapped to a `float` type and you need to pass in `Float, Nullable=False` as parameters to `mapped_column()`.

    Setting up the DB Connection

    Now that the models have been defined, you will need to setup the connection to the DB within db/database.py.

    The very first thing you should do here is import the Base class factory you made with from .advertiser_model import Base. Now to setup the DB connection, you will need to create an engine with a path to your database file. Then you will need to create a Session factory using sessionmaker. In SQLAlchemy, these sessions are required whenever you want to make transactions to the database. Actions made in a session are not pushed through to the database until you call session.commit(), similar to how Git operates.

    Everything you need to setup this connection has already been imported (aside from Base which you should have imported by now).

    Instructions 1. Create a `DATABASE_URL` variable and set it to `"sqlite:///./db/ad_revenue.db"`. 2. Create an `engine` variable and set it to `create_engine()`, passing in the `DATABASE_URL`. 3. Create a `SessionLocal` variable and set it to `sessionmaker()`, passing in the parameters `bind=engine, autoflush=False` to `sessionmaker()`. 4. Replace the `pass` placeholder in `init_db()` with `Base.metadata.create_all(bind=engine)` 5. Replace the `pass` placeholder in `get_session()` with `return SessionLocal()`.

    After completing these steps, you have setup an engine that binds all sessions to the database filepath that you specified. Whenever you wish to perform transactions with your database, the SessionLocal instance will be invoked to create a session(using get_session()). The init_db() function will be utilized to initially populate all the tables mapped to your models using the Base class factory. This function will be used in seed_data.py.

  3. Challenge

    Seeding the DB and Displaying Ad Revenue

    Seeding Data

    Now take a look at seed_data.py. This file will be used to seed your database with some initial data that will then be used to calculate estimated ad revenue per visitor. If a database file has not been created for whatever reason, such as a clean slate or running it for the first time, running this file will automatically create the db file as ad_revenue.db within the db directory. Go ahead and complete seed_data.py.

    Instructions 1. At the start of `seed_advertisers()`, call `init_db()` which you implemented in `database.py`. This will initialize the tables in the db file. 2. Go to the nested `for` loop in the `with` statement. Right after the `campaign_names`, which you won't need to worry about for now, right a query `stmt` with `select(Advertiser).where(Advertiser.name == data["name"])`. This is another usage of an ORM like SQLAlchemy, as you don't have to worry about formatting raw SQL statements in code. 3. After the `stmt`, define an `advertiser` variable and set it to `session.scalars(stmt).first()`. The `scalars()` method executes the `stmt` being passed in and `first()` retrieves the first matching item. 4. After the `advertiser`, write a conditional `if` statement that checks if `advertiser` is null. If it is, set it `Advertiser(**data)` and create a transaction to add it to the db with `session.add(advertiser)`. 5. Right before the print statement, add `session.commit()`.

    By completing these instructions, you have written a query that searches the database for any advertisers whose name matches the ones being seeded in the advertisers collection. If it does not already exist, you create a mapped Advertiser instance using the object data and stage it to be added to the database. After the transactions are staged, you commit them to the database just like you would commit changes with Git operations.

    Calculating Ad Revenue

    Now it's time to head to revenue.py to calculate ad revenue per visitor for each advertiser. The revenue calculation and library imports are all done for you, so all you need to do is write a query to retrieve advertiser data from the database to fill into the formula.

    Instructions 1. Right before the `for` loop in the `with` statement, define a `stmt` query with `select(Advertiser)`. 2. Define an `advertisers` variable set to `advertisers = session.scalars(stmt).all()`. This time you are keeping all the retrieved `Advertiser` data

    Once this is done, you can execute python seed_data.py if you haven't already to generate the db file and populate it with some seeded advertisers. Then do python revenue.py to display each advertiser and their expected revenue per visitor. You should see something like the following

    Spoiler Advertiser: PS-Alpha Expected Revenue per Visitor = $0.11

    Advertiser: PS-Tech Expected Revenue per Visitor = $0.09

    Advertiser: PS-Market Expected Revenue per Visitor = $0.12

    Advertiser: PS-Fit Expected Revenue per Visitor = $0.05

    Advertiser: PS-Industrial Expected Revenue per Visitor = $0.18

    Advertiser: PS-Stellar Expected Revenue per Visitor = $0.26

    Next Up

    There is one last thing you need to do which is to setup a relationship between the Advertiser you implemented and some AdCampaigns, which you will tackle in the next step.

  4. Challenge

    Relations

    Creating Relationships between Tables

    Being able to make connections between tables of data is the hallmark of a relational database. For this lab, you will need to create a one-to-many relationship between Advertisers and AdCampaigns. The SQLAlchemy ORM typically accomplishes this through the relationship() function, which you will need use back in db/advertiser_model.py. You will also be modifying seed_data.py and revenue.py to link and display AdCampaigns to their respective Advertisers.

    Instructions 1. Head to `advertiser_model.py` and head to the `AdCampaign` class. After `name` define a `advertiser_id` property that is mapped to an `int`. Pass in `ForeignKey('advertisers.id')` to its `mapped_column()` which will refer it to the id of the advertiser it is related to. 2. Create another property called `advertiser` that is mapped to `"Advertiser"` set to `relationship("Advertiser", back_populates="campaigns")`. 3. In the `Advertiser` class, add a new property called `campaigns` that is mapped to `List["AdCampaign"]`. Set it to `relationship(back_populates="advertiser")

    Doing this sets up a bidirectional relationship between Advertiser and AdCampaign, which you will take advantage of to reseed your database in seed_data.py

    Instructions 1. In `seed_data.py`, replace the `if` statement with and `else`. Inside this `else` block, add the line `advertiser.campaigns = [AdCampaign(name=name) for name in campaign_names]` between the `session.add()` statement and `advertiser` assignment. 2. Uncomment the code snippet containing the `if` statement regarding campaigns.

    In the previous step you defined a relationship between Advertiser and AdCampaign. Now you've modified seed_data.py so that some of the seeded advertisers are now linked with certain ad campaigns. You can run python seed_data.py again to update your db with the new seeding or delete the file and run the command if you want a clean slate. Both should be fine.

    Lastly, you will need to update revenue.py to display campaigns linked to an advertiser.

    Instructions 1. Head to `revenue.py`. Change the `stmt` to be `select(Advertiser).options(selectinload(Advertiser.campaigns))` 2. Uncomment the code snippet containing the `if` statement regarding campaigns.

    Using selectinload this time performs eager loading of records, which is both performant and intentional in this context since you want to grab all the campaigns related to an advertiser. Once you've done this, performing python revenue.py should now display the appropriate AdCampaigns that are related to their advertiser. Keep in mind that not every Advertiser has an AdCampaign.

    Conclusion

    Congratulations on completing this lab! To reiterate what's been covered, you have down the following:

    1. Setup a connection with your DB through the SQLAlchemy ORM
    2. Defined custom model classes, created tables, and made sessions for transactions through the Base and sessionmaker factories
    3. Written queries to interact/retrieve data from the DB
    4. Established relationships across different tables
    5. Used this relational data to calculate and display ad revenue
George Chiang - Labs - George C.
Author
George Chiang

George is a Pluralsight Author working on content for Hands-On Experiences. He is experienced in the Python, JavaScript, Java, and most recently Rust domains.

What's a lab?

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Provided environment for hands-on practice

We will provide the credentials and environment necessary for you to practice right within your browser.

Guided walkthrough

Follow along with the author’s guided walkthrough and build something new in your provided environment!

Did you know?

On average, you retain 75% more of your learning if you get time for practice.
Ready to get started?
View individual plans View team plans