Python for Entrepreneurs Transcripts
Chapter: Accessing databases from Python: SQLAlchemy ORM
Lecture: Concept: Modeling classes with SQLAlchemy
Login or
purchase this course
to watch this video and the rest of the course contents.
0:02
Now that you've seen SQLAlchemy in action let's go back and review the building blocks. We saw for the ORM we start with the SqlAlchemyBase
0:12
or whatever you want to call it but the declarative base created out of the declarative base factory method here.
0:19
remember, this is a singleton base class that is used for the base type of all of your objects that come or map to a single database.
0:30
Multiple databases maybe multiple bases here, if you are going to have one database, there is just one of these.
0:36
And then, any time we want to create a type that maps to the database, we just derive from the SqlAlchemyBase.
0:42
Here we have album, track, purchase, everything we want to put into this database is going to derive form this type.
0:48
When we actually create those classes, we probably want to give them a __tablename__ to be really explicit how they map to the database,
0:57
we are going to give them a primary key, probably called id, call it whatever you want, and here you can see
1:03
this one we are giving an integer, primary key that is autoincremented by setting the column type to integer,
1:09
primary key is True, autoincrement also True. Then we have some other columns, name, year, price, those are different types,
1:16
string, integer, float, and we also have relationship. So we have tracks and we set this up to be a sqlalchemy.orm.relationship
1:23
over to the track of class. One thing we haven't seen yet is what do we do with default values.
1:31
Everything that we've had just a nullable default value is acceptable, because there were like names of albums,
1:37
what's the default besides null there anyway, right. Well, here is the case where default values of just zero or null don't make sense.
1:45
So here we have a PasswordReset. And, we could have created an auto incrementing id for password resets but how secure do you think that would be?
1:53
How secure would it be to have like well, I tried to reset my password and it was 6, it was /reset/6 what happens if I put 7? Or 5, like,
2:05
these things that you can predict sometimes predicting them is super, super bad,
2:11
and so here what we want to do for the id is make it a really long good, we could have gone around this by having an autoincrementig id
2:18
and then another field that was like the public id or something, but we don't have to do that we could just do it this way,
2:24
What we could do is we can set a default function so we have two versions, at the bottom we are having a create a date
2:30
and we want that to just be when the object was created, unless you set it otherwise. So we can actually pass the "now" function,
2:36
notice that we are not using parenthesis when we are assigning the default, we are passing the function, not now. Otherwise,
2:43
it would just be the value of when the program started, that would be bad. but we're going to say: "SQLAlchemy, any time you create a password reset,
2:50
typical in the database, initialize the createed data by calling now at that time
2:54
and the id is a little more interesting, we are actually giving it a lambda here,
2:58
so this is lambda that takes no parameters or arguments and returns a string and what it does is it uses the UUID4, which generates
3:07
like a 32 character hexadecimal number with dashes and we want the dashes to go away, just the 32 characters
3:14
so this lets us not worry about creating long, interesting look up ids for these password resets, we just set these two defaults,
3:24
create a new password reset associated with the user and call safe and it's good to go. We also saw that primary keys are required
3:33
so here we are setting the id in this account to have a primary key, it's equal to True, and any time we want to do some kind of query
3:39
like maybe we want to run a report that will tell us about the accounts created today, we want to make sure that we have indexes.
3:46
So primary keys automatically have indexes, other things that we want to improve performance, sometimes dramatically, dramatically improve performance,
3:56
like a 1000 times better, if there is a lot of data in index versus no index, right, so you want to really think about what are you going to query by,
4:03
what are you going to filter by, what are you going to order by, put indexes where that makes sense, OK?
4:09
So here we might want to do a report, so show us our new daily users, so we want to index that.
4:14
Also we can have uniqueness constraints, so in this case, we have the email address associated with the user.
4:20
We don't want it to let multiple users have the same email address, right, this could happen really easily if somebody forgets their account
4:26
and just says "well I must have not registered" and registers again. And then if they say well I forgot my password later,
4:32
which account do you reset when they enter their email? Right, it's really not a good deal to have that duplicated
4:38
so make sure you put the uniqueness constraint in the database. Finally once you get your classes all mapped out, everything is ready to go,
4:46
it's time to create the model in the database, to actually create the schema, create the database, set up the tables and so on,
4:54
remember, you start by importing all of the types. If Python has not seen those types, has not imported those files,
5:01
as far as SQLAlchemy is concerned, they don't exist, so make sure you import them up there.
5:06
And then we're going to create and engine from a connection string and then we're going to use SqlAlchemyBase.metadata.create_all,
5:13
and that will create the data. Remember, if the table already exists when you call this, it will make zero changes to it, it only creates new tables,
5:22
it will not alter or migrate anything, One of the things worth looking at here is this echo=False on the engine,
5:29
SqlAlchemy.create_engine... echo=False. We haven't talked about this yet, we'll get to it later,
5:35
but if you say echo=True, all sorts of diagnostics will start to spit out of your application and you'll basically see all the SQL commands
5:44
that SQLAlchemy is going to send to the database echo to your terminal, which is really handy, so we'll use that as we get into this a little bit more.