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