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.