WEBVTT 00:00:00.350 --> 00:00:01.331 Welcome back! 00:00:01.511 --> 00:00:02.685 Here's what we'll learn. 00:00:02.865 --> 00:00:05.489 Use the city report we built in lesson six. 00:00:05.669 --> 00:00:12.283 We will read named rows, index or group records in Python, fetch one record or keys, read 00:00:12.283 --> 00:00:14.735 batches, and export CSV and JSON. 00:00:14.915 --> 00:00:18.371 The query is already ready; today we focus on its results. 00:00:19.767 --> 00:00:21.431 Let's inspect a fetched row. 00:00:21.611 --> 00:00:23.680 Fetch returns a list of named rows. 00:00:23.860 --> 00:00:29.065 Read a field by its name, or turn a row into an ordinary Python dictionary with dict. 00:00:29.245 --> 00:00:31.315 Each fetch executes the query again. 00:00:32.683 --> 00:00:34.347 Let's look up a city directly. 00:00:34.527 --> 00:00:38.325 Fetch as dict indexes the result rows by the selected key. 00:00:38.505 --> 00:00:41.022 Our grouped cities are unique, so none are lost. 00:00:41.202 --> 00:00:42.930 The values are still named rows. 00:00:43.110 --> 00:00:46.609 With a repeated key, a later row would replace the earlier one. 00:00:48.017 --> 00:00:49.766 Let's keep the detail rows too. 00:00:49.946 --> 00:00:53.061 Fetch grouped creates Python lists, one for each city. 00:00:53.241 --> 00:00:55.715 It does not calculate SQL aggregates. 00:00:55.895 --> 00:00:58.199 Florence keeps all twelve detail rows. 00:00:58.379 --> 00:01:02.134 Use this when you want groups of records rather than summary totals. 00:01:03.517 --> 00:01:05.202 Let's fetch one known product. 00:01:05.382 --> 00:01:07.451 Fetch record returns a single row. 00:01:07.631 --> 00:01:09.530 This filter uses a unique key. 00:01:09.710 --> 00:01:13.571 A missing row raises an error unless ignoreMissing allows None. 00:01:13.751 --> 00:01:19.042 Multiple rows raise an error unless explicit order by selects the first ordered row. 00:01:20.372 --> 00:01:22.249 Let's fetch just the product keys. 00:01:22.429 --> 00:01:26.696 Fetch pkeys returns a list of primary-key values in query order. 00:01:26.876 --> 00:01:29.223 Our limit selects the first three drink codes. 00:01:30.617 --> 00:01:32.579 Let's get the report as JSON. 00:01:32.759 --> 00:01:34.722 Fetch as JSON returns text. 00:01:34.902 --> 00:01:37.121 Decode it to inspect the first object. 00:01:37.301 --> 00:01:38.666 This creates no file. 00:01:38.846 --> 00:01:41.235 We will export JSON to a file shortly. 00:01:42.617 --> 00:01:44.515 Let's fetch the details in batches. 00:01:44.695 --> 00:01:46.637 Iterfetch yields batches of rows. 00:01:46.817 --> 00:01:49.995 Here they contain ten, ten, ten and six records. 00:01:50.175 --> 00:01:51.967 We skip the final empty batch. 00:01:52.147 --> 00:01:57.566 This example records only the sizes; real code can process each batch as it arrives. 00:01:58.950 --> 00:02:00.977 Let's export the CSV report. 00:02:01.157 --> 00:02:02.842 Pass a destination to fetch. 00:02:03.022 --> 00:02:08.263 The file extension chooses the format, and the return value is seven: the number of rows 00:02:08.263 --> 00:02:08.739 written. 00:02:08.919 --> 00:02:11.287 The headings use our optional aliases. 00:02:11.467 --> 00:02:15.179 Only the report file is saved; SQLite is still in memory. 00:02:16.550 --> 00:02:18.427 Let's export JSON as well. 00:02:18.607 --> 00:02:23.621 The same query writes a JSON array when the destination ends in dot json. 00:02:23.801 --> 00:02:25.849 Again, fetch returns a row count. 00:02:26.029 --> 00:02:28.567 Existing destination files are replaced. 00:02:28.747 --> 00:02:31.755 The notebook includes a way to download both reports. 00:02:33.150 --> 00:02:34.643 Thanks for following along! 00:02:34.823 --> 00:02:40.535 We reused one report query, chose how to receive its results in Python, and exported two 00:02:40.535 --> 00:02:40.925 files. 00:02:41.105 --> 00:02:46.811 You can now try the optional best-buyer exercise, or go straight to lesson nine for writes 00:02:46.811 --> 00:02:47.889 and transactions.