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