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