Anvil: Web apps with nothing but Python Transcripts
Chapter: Databases: Storing and querying data
Lecture: Creating a table
0:00 Now, let's create our first database table.
0:02 Actually, it's going to be the second
0:04 but just the first one we create.
0:05 Remember, we added this user service
0:08 and it actually added a data table into this database
0:11 that I'm already talking about.
0:12 So, we'll probably have data tables over here
0:15 if you don't you can click plus and add them.
0:17 You won't see them in that list cause they're already here.
0:20 They'll show up over here, either you add them
0:21 or something else like the user table
0:23 or the user service put them there.
0:25 So if we click, we'll now see we actually have a table.
0:29 Here's my email from when I registered
0:30 this is the email column.
0:32 Whether or not this account is enabled
0:33 whether or not it's pro, remember we set
0:35 that up with user table.
0:37 And then some other stuff about when I logged in and so on.
0:40 Let's go and create another one.
0:41 Let's call this measurements, like so.
0:46 Now, remember, our web application
0:48 the whole purpose is we record measurements over time
0:52 and then we can graph these
0:53 like how tall are you, and what is your resting heart rate
0:55 and things like that.
0:58 The most important thing about these measurements is
1:00 they are tied to a particular user.
1:03 In, quote, real databases, in relational databases
1:06 you work with directly, you have to set up
1:08 foreign key relationships between several things.
1:11 Watch how easy this is over here.
1:12 So we can just say we want to new column
1:14 and this is going to link to a single row
1:17 from the users table.
1:18 That's it, and we'll just call this user.
1:22 So this is actually going to be
1:23 a link over to this table.
1:26 So some entry here, only one we have now
1:28 but they'll be many entries in theory, will be
1:31 related over here through a foreign key.
1:34 Now, let's put in some more data.
1:36 We want to record the date when this happened
1:39 and we're going to do it by just the date
1:41 not day, hour, minute, and so on
1:43 and we just want to record it once a day.
1:47 So we'll call that record date.
1:49 Then we want to know when this individual record was
1:52 inserted, so this is when the measurement was taken.
1:56 This was actually when in the app it was saved
1:59 so we'll go ahead and say that here.
2:02 And then we'll record a couple of things
2:03 weight in pounds, which is going to be a number
2:07 and let's have one more number here, the resting heart rate.
2:13 Those are the main things that we're going to compare here.
2:16 All right, there we have it.
2:17 We've defined a database table.
2:19 Pretty easy, huh?
2:20 And you'll see that working with it is also super easy.
2:24 Over here it shows us that
2:25 it's app_tables.measurements
2:27 when we work with it within Python.
2:30 Also, notice the permissions.
2:31 We cannot directly interact with it from the forms
2:35 we have to go through what's called a server module.
2:37 So that's going to be interesting.
2:38 That's a security mechanism to make sure that
2:40 you cannot get directly to the web applications.
2:45 The only thing talk in the database, not say
2:49 talking directly to the database.
2:52 This is no big deal, it's easy to set up
2:53 but you do need to be aware of the permissions
2:55 and what that can mean.