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