Anvil: Web Apps with Nothing but Python Transcripts
Chapter: Databases: Storing and querying data
Lecture: Querying the database
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Well adding the measurement wasn't too hard. Let's see about querying the database. We're going to have this @anvil decorator here.
0:09
The next thing we want to do when our app is running on the authenticated homepage we want to draw a plot of all of your measurements.
0:18
Now, that's a lot of work. So what we're going to do is we're going to start by just showing a list. A flat, string list of all of our measurements.
0:26
And then we'll turn that into a plot just a little bit. Either way, though, to make that happen on the data side, we need to query the measurements
0:35
for a particular user. Turns out to be not too hard. Let's write a method here called my_measurements. And it actually takes no arguments.
0:44
It's going to have a user. A logged in user just like before. This time, if somebody tries to ask for their measurements but they're not authenticated
0:53
they don't have any measurements. We're just going to return an empty list, here. And that's fine.
0:59
Next, what we want to do, is we want to say the measurements that you have are actually, app_tables.measurements. We're going to do a search.
1:10
And we can just say either some kind of expression or things like, User=user. How about that? Now this will give us all of our measurements back
1:20
but we want them ordered. We can, of course, order them in Python. But you know what's really good at ordering things?
1:26
Databases. And the Anvil database allows us to specify a little search expression, here. So notice up at the top
1:33
we have anvil.tables as tables imported. So we can say, tables. has a couple of things. Most importantly, order_by.
1:42
And then we can say what we want to order by. First let's go and say ascending=True and the column name is going to be what did we call it down here?
1:50
Make sure we get it exactly right. RecordDate. There. So we're going to order by that. That's here. Alright, perfect.
2:01
Anvil looks like it thinks I did something wrong. Did I? We'll see, let's return measurements. Alright, that looks okay to me.
2:11
Probably there is something wrong that I'm just missing but let's go and run with it. See if this works.
2:17
So let's just go over here, in this authenticated bit. Instead of doing this, let's print anvil.server.call my_measurements, and that's it.
2:27
Let's just run that and see what we get. Oh, there you go. Positional argument followed by positional argument in the wrong wording.
2:37
Of course, that is not allowed and I was just not focused. Okay, try again. This should work. Look at that, we have an iterator.
2:47
That's cool that we get an iterator we can loop over it but let's actually loop over the items here. I'll say, for m in this thing
2:57
let's print out something like RecordDate. And we can also print, let's do a little format here. Let's just put out the heart rate.
3:13
So I'll copy those over. And we're going to be printing the record date weight in pounds, and heart rate for each entry.
3:19
Remember, there should be two for me so far. Run it again, and we get some output. Two output. This is encouraging.
3:28
Hey, look at that, there are the dates. Exactly what we were looking for. So we were able to get this cursor back to the database from the server.
3:35
Actually passed through Javascript over to the client where we could process the result of this query.
3:41
So here we are querying this database pretty easily. Now let's just go back here and review real quick. We come over here, we got to our table
3:50
and we just called search. And then we specified some filter criteria like user equals such and such.
3:57
On top of that we threw in a order by record date ascending. Did that work correctly? Let's see. We should have the oldest first and the newest last.
4:06
Well, there's two of them, they're ordered right. It must be perfect. No, actually, this is the right way in this limited data set, at least
4:13
to show that off.