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