#100DaysOfCode in Python Transcripts
Chapter: Days 91-93: Database access with SQLAlchemy
Lecture: Demo: Defining columns (via classes)
0:01 Now, let's go ahead and define the columns
0:03 that are going to be in our tables,
0:06 and also, how they appear in the classes.
0:09 So, here we're defining the name property
0:11 and in memory usually the thing that defines
0:14 what specific item you have is just the address in memory.
0:19 Like, when you created the pointer to the thing,
0:21 that is kind of it's id,
0:23 but in the database world we typically need
0:25 to set and id to a particular thing.
0:27 Let's say none for second and we're going to set the name
0:30 to something else.
0:32 We can also control
0:34 what table this gets mapped to.
0:37 So, if we do nothing it will just be added to role,
0:40 like capitol 'R' role and I'm not a super fan of that.
0:42 So, let's say table name is going to be roles.
0:45 Plural, lower case, I kind of like that better.
0:47 Alright, so how do we tell SQLAlchemy,
0:50 if this is an and id, and even a primary key,
0:53 and auto incrementing, unique, and all that stuff?
0:56 Simple enough we say SQLAlchemy and we add that
0:59 to the top and we say column capitol 'C',
1:01 not lower case 'C'. There's two for some reason
1:04 and here we'll say
1:05 what type of things SQLAlchemy.Integer.
1:09 We'll say primary key is True.
1:11 Auto increment, True.
1:13 Okay, so that's great.
1:14 That's going to get us started there
1:15 and this is going to be a string,
1:19 that's what we say, string.
1:21 Now, we might want to add some other features
1:24 like this role is supposed to be the one and only
1:26 dragon, or rock, or paper, or something.
1:28 So, we could come over here
1:30 and say unique equals True, as well.
1:32 No creative uniqueness constraint for the database.
1:36 This go moved out of the way by it's own self,
1:38 but that's okay.
1:40 If a role, it's going to have an id and a name
1:42 and whenever I'm working with databases
1:44 there's one other thing I really like to know.
1:46 Like, when was this record created?
1:48 Is this old, is this new?
1:50 It doesn't matter so much for the role,
1:51 but for players and the history that's going to matter a lot.
1:55 Let's go ahead and add one here as well.
1:56 It's going to be a SQLAlchemy.DateTime.
2:00 This is pretty good, it doesn't have to be unique,
2:02 but what we would like is to not have to bother
2:04 to manually set this,
2:05 but just have this happen automatically.
2:07 Saved role, the created time was when it was first created
2:10 in the database.
2:12 So, we can come over here and set the default,
2:13 simply some kind function, how about date time,
2:17 and I'll have to import that .date.time.now.
2:21 Now, it's super critical you don't put
2:23 these parentheses here. You just put the functions here.
2:26 You put the parentheses, everything is going to be created
2:29 when the program starts.
2:30 If you put a function it will be called
2:32 every time something is inserted.
2:34 So, we want this kind of now and I'm going to copy this
2:37 because we're going to use it actually,
2:39 probably want both of these top ones here.
2:43 Perfect. So, this role class, this role model
2:46 is going to be mapped to the database is 100% done.
2:48 Let's quickly knock out the other two.
2:54 Now, the only other thing we're going to have here
2:56 is the players name again.
2:57 So, this will be super easy.
3:01 It'll say novel is false.
3:03 This is a required value you have to give it to us.
3:06 Okay, we can also put that in our role while we're at it.
3:10 Here, you have to say the name.
3:12 So, again the player classes are pretty much ready to go.
3:19 Now, it turns out that this move is the most complicated
3:21 and we're going to sort of stop short of some,
3:23 maybe some full modeling here.
3:26 Just for the sake of keeping us time bounded here,
3:29 but we're going to say the table is moves.
3:31 Then I'm going to put in a bunch of columns
3:32 we're going to talk about.
3:37 So, like before we have the id
3:39 of when it was created and now we have some
3:41 sort of relationship thing.
3:43 So, what role is it associated with
3:46 and what player is it associated with?
3:48 So, these are integers
3:49 and it's going to be foreign keys back to the other thing.
3:52 Now, we could model these relationships,
3:54 but like I said, this is a super quick intro
3:56 to SQLAlchemy and not to deep dive into it.
3:59 There's a lot of complexity to those relationships.
4:01 So, we're just going to kind of keep them loose for
4:03 the time being.
4:04 We'll have a string, which is like some sort of UUID
4:06 type thing for the game.
4:07 So, we know when the game is played, which it is,
4:10 this is the role, like this is the first round,
4:12 second round, third round.
4:14 Who played that particular role,
4:16 and is this the play that wins the game?
4:19 Alright, is this the final play that beaks this up.
4:23 You typically might say well,
4:24 that's always going to be the fifth one.
4:25 Unless, there's some kind of tie
4:27 and we tie, and tie, tie,
4:28 and this keeps going.
4:30 Alright. So, it can get slightly more complicated because of ties.
4:32 So, we need to know when the last,
4:34 and when the particular play is the final one
4:37 that is the winning play.
4:39 So, with this we have our classes all defined.
4:41 We've got our role, our player,
4:44 and then for historical reasons,
4:46 we have our moves.
4:47 Of course they all derive from this model base,
4:49 which is super simple to create.
4:51 Not obvious, but very, very simple to do
4:53 and we did that in our model_base.py file.