#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.