#100DaysOfCode in Python Transcripts
Chapter: Days 91-93: Database access with SQLAlchemy
Lecture: Demo: Using the data access layer (DAL)
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.