Anvil: Web Apps with Nothing but Python Transcripts
Chapter: Databases: Storing and querying data
Lecture: Creating a table

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


Talk Python's Mastodon Michael Kennedy's Mastodon