Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Practice creating and reading entries in a relational database.
SQLAlchemy Docs
Installing SQLite and Accessing it on Windows
Follow the directions listed out in the answer on this forum post
SQLite Browser Application
If you’re working on this project locally, you can use the SQLite Browser application to view your database table. Download the application here.
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
We're going to focus on creating
animals and logs in our new database.
0:01
If you haven't already,
let's add our dunder main,
0:05
if __name__ == "__main__".
0:11
And then inside, we need to do
Base.metadata.create_all(engine).
0:18
Don't forget to save, and
let's run the file, Python3 models.py.
0:27
Oops, I forgot to, if you're like me and
you closed your project and
0:36
opened it back up, you'll probably
need to reactivate your environment.
0:40
There we go, now I can run.
0:44
Perfect, and there's our zoo database.
0:48
So if you ever close out of your project,
you just gotta reactivate
0:52
your virtual environment and
then you're back in and ready to go.
0:55
Now let's play with it
in the Python shell.
0:59
So I'm gonna pop in to the shell,
there we go.
1:01
We need to import models so
1:06
that we can import all the code that
we've written so far so we can use it.
1:08
And we're gonna start by creating
an animal to go into the animal table.
1:13
So let me scroll up so we can see what
that table is going to look like.
1:17
I'm gonna kinda squish this up here.
1:24
Okay, so I'm going to make a lion,
1:30
feel free to make whatever
animal you wanna make.
1:33
We'll do models.Animal, yes, Animal,
1:38
class Animal, and
then we will need a name.
1:43
Remember, ID is always
automatically created for you, so
1:47
you don't need to pass an ID number in.
1:51
Name is just gonna be lion, and
1:54
then we need a habitat called savannah.
1:57
And then we don't need a log, because
it's not a column, it's a relationship.
2:02
So we don't need to pass anything in for
that.
2:08
So I'm gonna hit Enter.
2:10
We can check that it's created by
doing lion.name, lion.habitat.
2:12
And if we do lion.id,
that'll be nothing because we haven't put
2:21
it in the database yet,
or into our table yet.
2:25
So let's do that now,
models.session.add(lion),
2:27
models.session.commit(), perfect.
2:34
Now if I run lion.id, I'm just pushing
the up arrow to get back to it.
2:40
Now I have one because it's
the first item in the animal table.
2:45
So we have a lion.
2:49
We can check the other ones too.
2:52
If we wanna do habitat, that's still
there, name still there, perfect.
2:54
Now let's try creating a log for our lion.
3:01
So I'm gonna call the variable lion_log,
3:04
almost sounds like Lincoln Logs,
[LAUGH] models.Logbook, same thing.
3:08
I'm gonna scroll here so
we can see what we need.
3:13
So we're going to do an animal_id, and
you just say what animal this belongs to.
3:19
And we know now the lion's ID is 1,
so I can put in 1.
3:26
And then we need to do some notes,
I'm just gonna
3:31
put great pouncer,
it's what lions are good at.
3:36
And let's go ahead and
commit it to the database,
3:42
models.session.add(lion_log),
models.session.commit().
3:46
And then we can check by
doing lion_log.id, and
3:55
we should now have an ID number.
3:59
And it's also 1 because it's
the first entry in our logbook table.
4:01
So we have one entry in
each table right now.
4:06
Now let's try accessing these relationship
attributes that have this back_populate.
4:10
And let's see what happens,
see what they look like.
4:17
So if I do lion_ log.animal,
it's going into the animal class and
4:20
finding the animal with the ID,
that's our foreign key,
4:27
and grabbing all of its information.
4:34
So it's pulling that information for
us from the other table,
4:39
which can save a lot of time,
save you from doing some queries.
4:42
Let's try it on the opposite one.
4:45
So here our animal table has this
logs relationship attributes.
4:49
So let's do lion.logs, and
you can see it's a list.
4:55
And inside of the list we have a log.
5:02
So if I were to create a second log,
5:06
let's do lion_log 2 = models.logbook,
5:10
and then we need animal_id = 1 still and
5:16
notes = "really likes meat".
5:22
Don't forget to close my string,
models.session.add(lion_log2),
5:26
And commit.
5:40
And now if I run,
I'm just gonna do up arrow,
5:42
lion.logs, you can see I get
both logs in the logbook.
5:47
So I get logbook number two and
I get, if I scroll up,
5:53
logbook number one,
because they both share this animal ID.
5:57
So when I'm on the lion and I access this
relationship, it's going to pull all
6:02
of the logs that are created referencing
this animal, which is cool, right?
6:07
It's pretty powerful.
6:13
And this is kind of what relational
databases allow us to do.
6:14
Before we play around some more, I wanna
show y'all what the database looks like.
6:19
So I'm gonna access that
using the SQLite shell.
6:23
If you're on a Windows computer,
6:26
then you'll wanna make sure
you have SQLite installed.
6:28
Check the teacher's notes for some help.
6:31
So we'll need to exit the Python shell,
6:33
also gonna run a clear,
and then sqlite3 zoo.db.
6:38
And it looks almost exactly
like the Python shell.
6:43
Once we're inside, we'll do .tables,
and this will show us our two tables,
6:47
animals and logbook.
6:51
And this comes, of course,
from our table name that we set earlier.
6:52
And then we can run a query, so
let's do select all from animals.
6:59
And this is what that table actually
looks like in our database.
7:06
We have one entry, so
the ID of that entry is 1,
7:11
the name is lion, and
the habitat is savannah.
7:14
So just like I said earlier, this
relationship is not shown in the database
7:19
in the table, because it's not a column,
it's just a relationship.
7:23
And we can see that if we do select all
from logbook to show the other one.
7:28
So this one, it has an ID of 1,
the animal's ID's of 1,
7:35
[LAUGH] is 1, and then we have a note.
7:40
And then it's the second entry,
so it has ID of 2,
7:43
still animal ID of 1, and
it really likes meat.
7:47
So the relationships will not show
because they are not columns.
7:52
Now, a question has popped into my head,
and
7:58
I wonder if it's something
you're wondering about too.
7:59
What happens if I create a log for
an animal that doesn't exist?
8:02
Well, let's create one and see.
8:09
It's the best way to test things out.
8:10
Let's do, I believe it's .exit,
yes, that was correct.
8:13
And let's pop back into the Python shell,
and don't forget to import models again.
8:16
Okay, so I'm gonna do seal_log and
8:22
I'm gonna do model.Logbook(animal _id).
8:27
And let's set this equal to, we still
only have one animal in the database.
8:34
Can I scroll up?
8:39
Yeah, I can.
8:40
So we still only have the animal,
we still only have the lion.
8:43
That's what I was going for.
8:47
So this, let's say this is animal ID of 2.
8:50
And let's do notes = "likes to wave",
8:54
and models.session.add,
8:59
models.session.commit(), awesome.
9:07
And then we check with seal_log.id,
which is now 3.
9:13
It's our third entry into that table,
so it has an ID of 3.
9:17
And we can do seal_log dot, and
let me scroll down to our logbook,
9:23
animal is our relationship attribute here,
so let's do animal.
9:28
And you see I get nothing,
because it tries to go and
9:34
find an animal with an ID of 2 and
finds that there's nothing there,
9:37
so it returns none, it returns nothing.
9:43
Let me see if it gives us this.
9:48
Yeah, there you can see
that gives us None.
9:55
That's really important to keep in mind,
10:00
because if I was going to,
say, create, let's say,
10:04
I believe that's how you spell a wombat,
models.Animal and name="wombat".
10:09
Hopefully that's an animal and
10:16
not something I'm making
up on [LAUGH] the spot.
10:17
Habitat, I actually don't know,
we're gonna guess, forest.
10:20
And let's add it,
models.session.add(wombat),
10:25
models.session.commit(), perfect.
10:32
So now if I do our seal_log.animal,
I'm just doing up
10:38
arrow to get back to it,
it's gonna bring me the wombat.
10:42
Because that's our second animal in our
animal table, so that gives it an ID of 2.
10:47
And obviously that's not a seal.
10:54
This is important to remember,
10:57
it's possible to make a log with
an animal ID that doesn't exist.
10:59
And then if an animal is created
that doesn't match that log,
11:03
then things can get kind of messy.
11:07
So keep in mind when creating applications
that you'll probably wanna make
11:09
sure an animal ID is chosen from
animals already existing in your table.
11:14
Or prompt the user to
create an animal first and
11:20
then create a log for that animal second.
11:23
Next up, we're gonna be working
with updating and deleting entries.
11:26
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