#100DaysOfCode in Python Transcripts
Chapter: Days 91-93: Database access with SQLAlchemy
Lecture: Demo: Using the data access layer (DAL)
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Alright, we have everything set up
0:01
and ready to go.
0:02
We can access the database all we want.
0:04
Now we just need to decide what do we want to do
0:07
with the database?
0:08
So if you recall over here in program,
0:10
there's this game service
0:12
that's being used in a couple places.
0:14
Find or create a player.
0:16
Alright so one is going to be us, the other is the computer.
0:19
Down here, get the game count, get the all players.
0:21
Now this is really a nice design pattern
0:24
because that means the only data access
0:26
that's really happening in the entire program
0:28
is in this one file.
0:30
If you have a really complicated app,
0:31
maybe you create different types of these little
0:34
data access layers service type things.
0:35
But, you isolate it into one place.
0:37
That means if you decide like,
0:39
hey, I'd like to switch to some entirely different type
0:41
of database or completely change this around to call web
0:44
service is instead of to call
0:46
data access layer uh, direct database access.
0:49
It's one place that you change.
0:51
So, I strongly encourage you to create this kind of pattern
0:54
that isolates all the database stuff into one place.
0:56
In order to make this work, we really just have to
0:58
write the code to make these things go.
1:01
Let's start over here.
1:02
If we want to say get the history
1:04
of a game, how do we do that?
1:05
Well, remember that session
1:06
thing we talked about.
1:07
This is how it's going to start over and over and over again.
1:10
So we'll say session factory
1:13
and we'll import this up at the top.
1:18
And down here we can say sessionfactory.create_session.
1:21
Notice that we do not have a factory.
1:24
Alright, there's no factory.
1:25
Uh, so we're just going to say create session.
1:27
And that's going to create this and at the end,
1:31
we're going to do session.close.
1:35
If we had made changes, we would say commit.
1:38
But, we're not going to do that.
1:39
Okay so we've created our session
1:40
and now we can create a query.
1:42
So I'll say the query is going to be
1:44
session.query, and you give it some kind of type.
1:47
We're going to look for moves.
1:49
So what we'd want to get back is a list of moves.
1:51
So we want to come in here and say query of move
1:53
and then we can do a bunch of things.
1:54
Orderby, filter, all, first and so on.
1:58
So we're going to say filter and the way you do this is
2:00
you go to the class and you say what are we looking for?
2:02
Game id equals, do a double equals, not a single,
2:05
equals this.
2:07
Now we can wrap that around and we want to do
2:09
an order, orderby, and then want to say we want to
2:12
orderby move, roll number.
2:15
Here we're going to say roll one,
2:17
then roll two, then roll three within this particular game.
2:20
And then we want to return all of those as a list.
2:23
So we'll say .all, we'll say moves equals list of query.
2:27
And then we'll return the moves.
2:28
Now, you might say it's slightly less efficient
2:31
to turn this into a list,
2:32
instead of like return this back and iterate over it,
2:35
and that would be cool.
2:36
However, this means that all the
2:38
data access is done by line 21.
2:41
Then we close the session
2:42
and we can just say forget about database access.
2:45
We are now back to just working with Python objects.
2:48
So, this is great.
2:49
Let's go ahead and write the rest of them.
2:53
To figure out how many wins a player has,
2:55
we'll create a session again or create a query on the move.
2:58
I want to say, I want to find the move that is for this
3:02
particular player and is a winning move.
3:04
This is the move that wins the game
3:06
for that particular player.
3:08
If they lose the game, this never gets set.
3:10
So, that doesn't count.
3:11
So, this thing here will get us all
3:12
the moves, then we can call .count instead of get the
3:15
objects back, this'll just give us a number called wins,
3:18
close the session and carry on.
3:23
Now for find and create a player, this one has sort of two
3:25
modes, which is, makes it somewhat, uh, more cumbersome.
3:28
But, what we're going to do is we're going to come in and
3:29
create a session.
3:30
This is how it always begins.
3:32
Create a query for the player.
3:33
When I say I would've liked to find the player by name
3:36
and this time just give me the first one.
3:37
Remember, the name is unique so this is one or zero
3:40
we're getting back.
3:41
If we got one back we'd just close the
3:42
session and say, "Here, this one already existed.".
3:45
But if we don't get one back, that means it's time
3:47
to create a new player.
3:48
Right, we've never seen this player.
3:50
So, what we're going to do is create a player object,
3:52
set the various values, the only one that doesn't have
3:54
a default of any form is the name so it's kind of boring.
3:58
But, we just set the name.
3:59
We'd set all the things, if we're setting more.
4:01
And then the way it gets in the database,
4:03
so we say session.add and then session.commit.
4:06
Now, if we were not using player again, the object,
4:10
and we just wanted to create it and forget it,
4:13
this should be fine.
4:14
We'd just be done.
4:15
However, once you call commit,
4:16
all the properties of this object get stale.
4:19
And you have to like reset them and try to get them back.
4:22
Which is kind of, uh, annoying.
4:23
So, what we're going to do is just get a new object
4:25
back from the database and this one,
4:27
uh, will not, this one will not be stale.
4:30
Right, the commit flag won't tell that we got to re-read
4:33
that data. So, sort of refresh this object after it's in
4:35
the database and send it back.
4:39
Getting all the players, actually this is the easiest query
4:42
we're going to write.
4:43
All we have to do is go to the session,
4:44
create a query player and say all, hit that with a list to
4:48
read through it, close the session and now
4:50
we have all players.
4:51
We might want to do an orderby,
4:53
alright maybe order by name.
4:54
But, if you don't care about ordering, this is all it takes.
4:58
While we're at it, let's do all rolls.
5:02
This one's basically the same.
5:03
We're going to get all the rolls, and this time
5:04
we actually do want to order them by name.
5:06
So they're alphabetical, that'll just make it easier to find
5:09
in our UI. Convert that to a list and return them.
5:11
It's all good.
5:12
Oh, except for I put that into the wrong spot, didn't I?
5:16
There, there's all our rolls.
5:19
Want to find a roll?
5:22
Here, we're just going to go through and say create a query
5:24
based on roll, where the name is that and go first.
5:27
It's either going to give us one back or not, which we've
5:29
indicated with a optional roll.
5:31
Rather than it's just a roll, it's a return value.
5:35
Now, for creating a roll, it's going to be very similar
5:38
to what we did before.
5:40
I'm going to come down here, create a session,
5:42
we're going to create a roll and this time
5:45
I think we just got to say roll.name == name.
5:47
There's no more constructor initializer there.
5:50
Save it and re-refresh
5:51
it so that it's not stale, and give it back, all good.
5:55
Only have one more left here and that's record roll.
5:57
This has probably got the most going on in terms of data.
6:00
So we'll come down here and we'll set all these properties.
6:02
And we're going to create a session, create the moves,
6:04
here we set all the things we care about that don't have
6:07
default values.
6:08
Again out of the session, commit, close.
6:10
We don't even return it back.
6:11
We don't care about getting the record of the move,
6:14
we're just going to say put it in the database,
6:15
I'll ask for it back some other time.
6:17
Now that defines all of our functions.
6:19
Let's see if we run it, if it'll even work.
6:22
First of all, before I run this, look over here.
6:25
If I do a quick refresh, sync.
6:28
We now, that I already ran it, just this second actually
6:31
created this rockpaperscissors.sqlite.
6:34
And notice that it's a database icon.
6:35
That's not because of the extension,
6:37
that's because PyCharm looked at it and said,
6:39
"I understand what that is.".
6:42
So, we're going to be able to work with that in a second.
6:44
Let's see if our game just runs.
6:45
We may have to go fix it up.
6:48
Michael, here's all our rolls, I'm going to throw some water,
6:51
and I'm going to throw some fire,
6:52
just keep him off guard there, maybe some more fire.
6:55
Uh, how 'about we throw down a tree and let's see if we
6:58
can hit him with some scissors.
6:59
4 to 1, dominated.
7:02
That's pretty cool, let's run it again.
7:04
Look at this.
7:05
Now here's our player history.
7:08
In a historical perspective,
7:09
Michael's won one time and the computer's won no times.
7:12
Let's throw Jennifer in here, see how she does, uh, 2.
7:17
She's going to throw some air, lots of air, who wouldn't want to
7:21
throw a sponge in there?
7:22
Maybe a little wolf action.
7:24
Boom, Jennifer also wins.
7:25
Now if you run it again you'll see
7:27
Michael and Jennifer won, the computer zero.
7:29
How cool is that?
7:30
And this is in our little database.
7:32
Let's look at that a little bit deeper.
