In this lesson, you will learn how to create a one to many relationship between two database tables using SQLAlchemy.
Tasks covered include:
- creating a child table
- defining a foreign key in the child table
- creating the relationship between the parent and child table
- demonstrate usage in Python
- view the data created in a local sqlite database

https://app.egghead.io/api/v1/lessons/python-create-a-one-to-many-relationship-in-python-using-sqlalchemy/subtitles

Create a one to many relationship in Python using SQLAlchemy

Instructor: [00:02] We have this class inventory that represents the inventory table in our database. In it, we can store information like the make, model, year, availability status, and color of the cars available from this rental agency.

[00:16] Let's add another table called history that allows us to store some of the history about the available rental cars. To do that, we'll start a new class and we're going to call it history. It's going to inherit entity and base. Entity comes from this file right here where we just define some defaults for all of our database tables. Base is the SQLAlchemy declarative base, which is the base declaration for all of the SQLAlchemy methods.

[00:45] First, inside of here, we'll define our table name using the double under or dunder table method. We'll call it history. Then, we'll add a column called history type. That's going to be a column of type string.

[01:01] Now to define the relationship between the history table and the inventory table, we need to import a couple of things here. We need to import foreign key from SQLAlchemy. Then from the SQLAlchemy ORM, we need to import relationship. Then in our table definition, we can create a variable called inventory ID. That's going to be a column type of integer that has a foreign key relationship to inventory ID.

[01:34] We'll define our dunder init for this table, it's going to receive itself as a parameter and then history type, which refers to this history type here. We'll be able to add that. Then from entity, we'll call the dunder init method there. That's going to just give us our defaults of the ID created at and updated at. Then say self dot history type is equal to the history type that we pass in whenever this is called.

[02:03] There's nothing new there. That's the exact same thing that we did in this inventory table up here. What is new is we're going to define another variable called inventory. It's going to be equal to a relationship with the table inventory. This inventory keyword here or inventory string here references this class of inventory. That's what establishes the relationship in our history table to the inventory ID.

[02:30] We want that to be a two way relationship though, now, we're going to define inventory dot history. We'll define that relationship as the history, again, that history object refers to this history class. Then we'll say order by history dot ID, whenever we see this relationship, there's a sort order to it from the history table and back populates inventory. That's going to establish from the inventory table or the inventory object the relationship with the history.

[03:04] Let's jump over to a terminal and I'll show you how that works. Just going to open up a Python console here. Then from inventory-when I say from inventory, I'm referring to this inventory dot py file here-I want to import inventory and history. From the inventory file here, I want to import the class inventory and I want to import the class history.

[03:29] Let's define a car and if it's going to be an instance of the inventory class, which takes parameters for the make, model, year, available, and color. The make we'll say is a GMC, the model we'll call a suburban, we'll say it's a 2018 model. For availability status we'll just call it true, saying that it's available in inventory. Then color is going to be black.

[03:54] If we take a look at the car we have our inventory object there. Now, we have this car dot history, which is an empty array because it has no history. Let's add some history to it. We can say car dot history is equal to-and because this is a one to many relationship, it's going to take a list.

[04:13] We'll give it an instance of the history class where we take history type as a parameter. We'll give it history type of new car added. We'll give it one more history item. We'll say the car was crashed. Now when we look at car dot history again we have those two inventory objects. We can iterate through them. From a Python object oriented level, it looks like everything is working.

[04:42] We've got this main dot py file that exposes a couple of methods on the inventory object. It has a get inventory, add inventory, rent inventory, and return inventory. Let's change this so that our add inventory adds a new history item to our database. After create the instance of the inventory class, we can say new INV dot history is equal to history, which we'll need to import. We can do just the exact same thing that we did in the console.

[05:15] We'll jump over to the console and take a look at how this works. First one to show you over here in this entity file, whenever I built this, I created this little variable right here. It looks for an environmental variable called DBHost. If it doesn't find that, it just writes to SQL lite in memory object.

[05:34] I'm going to say DBHost is equal to SQL lite INV dot SQL lite. That way instead of writing to an end memory database object, it's going to write to a SQL lite database file in this same folder, and we'll be able to take a look at what this does for us in the database.

[05:54] I'm going to fire up my Python console and import main. Then, we're going to call that add inventory function. Remember, the add inventory function accepts make, model, year, and color as parameters. We'll give it a GMC Tahoe this time. It's 2018 and this one is going to be red. We'll do one more. We'll do a Kia Sorento. It's a 2017 and it'll be green.

[06:29] Those have been added. What that means is whenever we call this function, we added them to the session. Then we called the session dot commit, which writes it to the database. As a matter of fact, you can see it over here. This INV dot SQL lite file has been created for us. If we exit the console we can open the SQL lite database using the SQL lite three command. Take a look at our tables and there's the history table and the inventory table.

[06:58] If we select star from inventory, there's our two cars, the GMC Tahoe and the Kia Sorento. Now if we select star from history, we see that there's two history items added for each car, the car being added and the car being crashed. The inventory ID field that we see here lines up with the inventory ID for that car in the inventory table.

Create a one to many relationship in Python using SQLAlchemy

Share this video with your friends