#100DaysOfWeb in Python Transcripts
Chapter: Days 33-36: Database access with SQLAlchemy
Lecture: Modeling with classes
0:00 We have our app in place
0:01 now what we want to do is create some classes
0:04 that map into our database.
0:07 In Python we work in classes and objects
0:10 and in the database we work
0:11 in relational normalized data in the tables.
0:14 So, in order to do that I'm going to create
0:17 a couple files in a folder here
0:19 let's create a folder called data.
0:23 And, in there I'm going to put another folder called models.
0:26 So these are going to be all the SQLAchemy models
0:28 that map to the database.
0:30 Create sub-folder because there'll be other stuff
0:32 we'll end up with in this data section.
0:34 What's the primary thing we're going to work with?
0:37 How about scooters?
0:38 So let's start by defining this class
0:40 and you're going to see there's one other thing we have to do
0:42 to sort of set the foundation in terms of classes.
0:45 So let's say import sqlalchemy as sa
0:48 this is a pretty common practice.
0:50 You don't have to do it, but I'll do it
0:51 just to keep things a little bit shorter.
0:53 We'll create a class called Scooter
0:55 singular 'cause this represents a single scooter
0:58 it's going to be an object we create.
1:00 One scooter that can go in the database.
1:03 Now there's going to be a base class
1:04 we'll talk about that in a moment.
1:07 Now, in order to define this class, we just put
1:10 what would be columns in the database as
1:13 fields here, at the class level.
1:15 So, we're going to have an id, and this is going to be a
1:17 SQLAlchemy column, and then you need to say the type.
1:21 It's *args, **kwargs.
1:24 To me that's just like "well too bad
1:26 we're not going to help you learn how to use this class."
1:30 I really don't like this design pattern
1:32 I think it's really a cop-out.
1:34 There's certainly other techniques
1:35 that are more helpful you could put in here.
1:37 Anyway, let's not get me on a rant
1:39 you have to kind of know what goes here
1:40 but honestly it's pretty simple.
1:41 So we're going to have the type
1:43 which is going to be an integer.
1:46 Every table needs a primary key, and when it's an integer
1:49 if you just want the database to figure out
1:51 what the next number should be?
1:52 That's pretty easy, so here we can say primary_key=True
1:57 and we can say autoincrement=True.
2:00 Great, so that's our id.
2:02 What else do we want to know about the scooter?
2:04 Almost every database record that I ever work with
2:06 I often want to know "well, when was that actually inserted
2:09 when was it saved?"
2:10 So let's just go ahead and add that really quick.
2:13 Create a date, and it's going to be SQLAlchemy column
2:16 of SQLAlchemy DateTime, like that.
2:19 There's going to be more stuff we add to these columns
2:21 but let's do the basic pass first.
2:24 Our scooter's are going to have VIN Numbers
2:26 we want to be able to look them up and a VIN number
2:29 a Vehicle Identification Number, seems pretty decent.
2:31 This is going to be a string, so it's going to be
2:33 SA column, SA string, like this.
2:37 We have a model, this is like a
2:38 descriptor, so it's the same thing.
2:42 We want to know what the battery level of our scooter is.
2:45 Our scooter is going to be an electric scooter
2:48 it's going to cruise around, we need to know
2:49 "is there enough charge so that we can rent it
2:51 to this person?
2:52 Or is it going to run out in five minutes
2:54 and then they're going to be dissapointed?"
3:01 So let's say "integer as a percent"
3:03 %20, %23, things like that.
3:08 so we're going to add some relationships here in a little bit
3:11 as well, but we don't have the other pieces built.
3:14 So, we'll sort of put a placeholder for that.
3:18 Now finally, if I were to go and store the scooter
3:21 in the database, SQLAlchemy will create the table
3:24 with all of these columns here.
3:27 With the right types and everything.
3:29 What is it going to call that table?
3:31 Well, exactly that, Capital S "Scooter".
3:34 Now, it's great that the class represents one scooter
3:37 but the table holds many scooters.
3:39 So let's go into this, and
3:40 the capitalization isn't great either.
3:42 So in SQLAlchemy say "table name"
3:44 __tablename__ = 'scooters'
3:46 what you really want it to be called
3:47 let's say "scooters", lower-case.
3:50 Pycharm thinks that's misspelled, tell it it's not.
3:54 Now, this is almost ready to go
3:55 into the database, it's almost ready.
3:58 There's one problem though, we have to put
4:02 a certain base class here.
4:05 So, all the things we store in the database
4:07 derive from this common base class
4:10 and that's actually how SQLAlchemy figures out
4:13 "well what tables do I need to create?"
4:14 Well, all the classes that derive from my base class
4:18 that's the tables I'm going to create.
4:21 This is not a static type because
4:23 you might want to put some into one database
4:25 some other classes in another database, things like this.
4:27 So, we have to create this class to use it.
4:31 So here's another file, call it sqlalchemybase.
4:36 Now, it's going to be pretty simple, we're just going to put
4:38 one thing in here, but I put it in a separate file
4:40 so we don't end up with
4:41 circular dependencies or other weirdness.
4:43 So, we're going to import sqlalchemy.ext.declarative, okay?
4:50 And then we're going to define a type at runtime
4:52 SqlAlchemyBase, the name doesn't matter
4:55 just pick something you like for a class name.
4:58 We're going to say sqlalchemy.ext.declarative.declarative_base
5:02 just like this, if we get out of the way.
5:05 So we create this type here, and this is just as if
5:08 we had typed class SqlAlchemyBase, right?
5:15 But it's declared at runtime here.
5:18 So now we can use this as our base class here
5:21 of course we have to import it above.
5:24 Now this scooter is ready, this scooter can now be
5:27 modeled in the database, as long as
5:29 the database matches that schema.
5:31 We could do queries against it, we could do inserts
5:34 updates, deletes, all those kinds of things.
5:37 So this is how we model data in SQLAlchemy.