Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Relational Databases With SQLAlchemy!
You have completed Relational Databases With SQLAlchemy!
Preview
Start creating a relational database with SQLAlchemy by creating models for two tables.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
Welcome back.
0:00
I'm going to be working locally
here in Visual Studio Code, but
0:01
there are also workspaces
attached to the videos.
0:04
I would highly recommend getting used
to working locally since this will
0:08
be more realistic as a developer.
0:11
Okay, let's kick this into gear.
0:14
First, create a folder for
your project and open it in your IDE.
0:16
My folder is called sqlalchemy-relational.
0:20
Then we'll need to create
a virtual environment.
0:23
There's my environment folder.
0:30
And then we'll need to activate it.
0:33
This is how you activate it on a Mac.
0:35
Make sure you check the Teacher's Notes
if you forgot how to do it on Windows.
0:37
Perfect, and we can install sqlalchemy,
pip install sqlalchemy.
0:41
If I scroll up a bit here, you can
see I have sqlalchemy version 1.4.22.
0:49
Don't worry if your version is different,
just check the Teacher's Notes in case
0:54
there's any changes that
you need to be aware of.
0:58
The project we're going
to work on is zoo themed.
1:01
Before I start creating my database model,
1:04
I'm going to outline it
using code comments.
1:06
You could also do this on paper or
use a spreadsheet or
1:09
even diagram it if you want.
1:12
I find it helpful to outline how I want
the database to look before I start
1:15
creating the models.
1:19
One table is going to hold
all of the animals, and
1:22
the other is going to
hold a Zookeepers Log.
1:24
So let's comment these in here.
1:27
So we're gonna have an Animals table, that
table is going to need a unique ID for
1:30
each row.
1:35
Then we're going to need a Name for
the animal, and
1:36
I'm just gonna do a Habitat and just keep
it simple with just these three things.
1:39
If you wanna add something like color,
size, etc.,
1:44
feel free to do so, I'm just gonna
keep it to these to keep it short.
1:47
The other table is going
to be a Zookeeper Log.
1:53
Still gonna need a unique ID, then
here we're going to have an Animal ID,
1:57
and this is going to be our foreign key,
which is going to grab, or
2:03
hold, an ID from the Animals table so that
we can find the relating animal easily.
2:09
And then we're just gonna have Notes,
same thing, just keeping it super simple.
2:16
Now that we've got the layout figured out,
let's create the models.
2:20
Let's start with all the typical imports.
2:25
From sqlalchemy import create_engine,
2:31
Column, Integer, String.
2:38
And we're going to add one more here.
2:43
This is where we're going
to add our foreign key.
2:45
And because now we have to do this kind
of scrolling, I don't wanna do that,
2:48
so I'm gonna wrap these like this,
it's two, three, and two. Perfect, okay.
2:54
Next, we're going to need from
3:03
sqlalchemy.ext.declarative import
3:08
declarative_base from sqlalchemy.orm
3:15
import sessionmaker.
3:22
And then we're also going
to need relationship.
3:28
And we're gonna get into foreign key and
relationship later,
3:31
I just wanna make sure we have
all our imports ready to go.
3:34
Next, let's do our variables.
3:38
Two lines and
then let's do engine = create_engine.
3:42
Then we're going to need sqlite:///zoo.db,
3:48
kind of rhymes a little bit.
3:53
I'm gonna set echo=False.
3:56
If you wanna set it to true so
that you see what it looks like
3:59
in SQL statements in the console,
go for it, totally up to you.
4:02
We'll need Session =
sessionmaker(bind=engine).
4:07
Gonna need a lowercase session
= uppercase Session(),
4:16
calling it and
then Base = declarative_base.
4:22
Awesome, and so just in case yours
is the same you may be noticing that
4:27
is kind of giving you an error.
4:32
You can always check either by clicking
on your environment folder then library,
4:35
seeing that sqlalchemy is installed or you
can run your pip freeze requirements.txt.
4:41
Let that go and you can see we have
greenlet and sqllchemy installed.
4:48
So it is installed,
4:52
this is just a bug from Pylance that
is they're currently working on it.
4:53
So it's gonna tell you that it can't find
sqlalchemy, but don't worry, it is there.
4:58
Okay, so now we're going to work
on creating our two classes.
5:04
It's one class per table, and since we're
creating two tables, if I scroll up,
5:09
an Animal table in a Zookeeper table,
we need to create two classes.
5:14
So first, class Animal(Base),
5:18
and let's set the tablename = animals.
5:24
And we'll leave it as that for right now.
5:33
Class Logbook(Base).
5:36
And tablename = logbook.
5:42
And don't forget to save.
5:48
In the Teacher's Notes,
I've linked to this building
5:53
a relationship part of
the SQLAlchemy documentation.
5:56
This new class, an address class,
is using our foreign key field here,
6:01
and it's relating to the class that was
originally shown in the documentation.
6:06
So if you come back up here to declare
mapping, you can see the original
6:12
class that they created, a user class with
an ID, name, full name, and nickname.
6:17
And now they're adding on, scroll
back down to building a relationship.
6:24
Now they're creating a second class,
which is a second table called addresses.
6:29
And it has an ID, email address, and
6:35
then a user ID which relates
to the users table ID column.
6:38
And then this is defining
the relationship between the two,
6:44
which is that it relates to user and
it's going to back populate addresses.
6:48
So let's start working on this on our own,
6:55
and we'll see how it works
with our two classes.
6:57
So in our Logbook class,
put a space in there,
7:04
and then we're gonna have our ID column.
7:10
So Column, that is an Integer,
7:13
and that is our primary_key=True.
7:17
Add some space here, there we go.
7:22
Then we're gonna have our animal_id,
7:25
which is also a column in our table,
it's still going to be an integer.
7:29
And this is going to be our foreign key.
7:34
So our foreign key we need to tell
it what table it relates to, so
7:39
we need to use the table names.
7:42
You can see our table name is Animals.
7:44
Animals, and it's going to be
the id field, which will look
7:48
essentially the exact same as this
when we create it on the animal class.
7:53
Perfect, then the last column we have
is the notes column, we get Column.
8:00
And I'm just gonna leave this as a string,
8:07
since we're just using
this to play around with.
8:10
And then we're gonna create
our relationships, so
8:14
we have animal = relationship.
8:19
This is a call.
8:22
And then here we put in our class name,
so it relates to Animal.
8:24
And then it's going to back
populate something called logs.
8:31
And we're gonna create
this on the animal class.
8:38
And when we get there
I'll let what that means.
8:43
Let's also create
a dunder-repper real quick,
8:47
so def __repr__, it's going to
take self return, let's do an f.
8:53
I'm just gonna do a triple strings so
that we can do this on multiple lines.
9:01
I'm gonna do a new line just
to make sure it starts off.
9:07
Logbook, and I'm gonna use self.id, so
9:13
it's kind of like logbook one,
that might work.
9:17
And just gonna do a carriage return,
9:22
and then we'll have Animal ID
9:28
= {self.animal_id}.
9:33
And then, let me make sure
that also has carriage return.
9:37
And let's do Notes = {self.notes}.
9:43
Awesome, and I'm just gonna copy this
cuz we're probably we're gonna make
9:50
another one for the animal class, so
just to make a little bit easier.
9:54
Okay, so let's review.
9:58
On our logbook, in this table,
we have to have a primary key, so
10:00
that's our ID field.
10:05
Then we have animal_id, which is gonna be
the column of IDs from the animal table so
10:07
that when we look up notes we can then go,
what animal is this for?
10:12
Looks up in our Animal database and
it goes, that was for the koala.
10:18
And then we have a column of all of our
notes, and then we have our relationship.
10:23
And we'll talk more about that back
populates here as we create animal.
10:30
So we have our animal,
Let's have a blank line,
10:34
and now let's pop in same thing,
we're gonna need
10:40
an id = Column (Integer,
primary_key=True).
10:45
Then we're gonna have a name column,
10:50
which is a column, that is a string.
10:54
Then we're gonna have habitat,
10:57
which is also a column
that is also a string.
11:01
And then here's where our
relationship comes back in.
11:07
I'm gonna create a logs that
is going to be a relationship.
11:10
Back to logbook, remember, same thing,
11:17
it has to be the name of the class
when we're doing the relationship.
11:19
And it's going to back populate, Animal.
11:25
So what this does,
is it creates this fun little
11:32
connection between this attribute and
this attribute.
11:36
So when you're on the logbook table and
you select this attribute,
11:41
it's going to give you the Animal that
relates to the foreign id from this field.
11:47
And if you're on the Animal table
when you do the log attribute,
11:53
and when you access that,
it's going to go to the logbook and
11:59
find all the notes that relate to this
id and put them in a list for you.
12:03
We'll see it more when we get going,
but it's really cool.
12:10
Okay, same thing, let's add
a dunder-repper, mine's still copy-pasted,
12:14
so I'm gonna switch this to,
Animal, it's self.id,
12:18
then we need, Name.name.
12:24
And then whoops, habitat,
and this will be habitat.
12:30
Perfect, and I'll hit Save.
12:39
Whoops, okay, now that we've
got our classes put together,
12:42
in the next video we're going to start
creating entries for both tables.
12:46
And we're gonna get more explanation and
12:51
practice into how these
relationships work.
12:53
See you there.
12:56
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up