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