WEBVTT 00:00:00.350 --> 00:00:01.491 Welcome back! 00:00:01.671 --> 00:00:06.784 Let us turn our product catalogue into an invoicing example with customers, dates, 00:00:06.784 --> 00:00:08.218 quantities, and prices. 00:00:09.548 --> 00:00:11.982 Keep the same Python console open. 00:00:12.162 --> 00:00:14.404 If you closed it, rerun lesson one. 00:00:14.584 --> 00:00:16.583 The database is still in memory. 00:00:17.913 --> 00:00:19.687 Let's check our starting point. 00:00:19.867 --> 00:00:21.681 We already have three products. 00:00:23.050 --> 00:00:24.993 Let's describe our customers. 00:00:25.173 --> 00:00:28.311 Alongside the identifier and name, add their city. 00:00:29.641 --> 00:00:31.596 Let's create the customer table. 00:00:31.776 --> 00:00:35.455 Reload the model and migrate, keeping the existing products. 00:00:36.850 --> 00:00:39.005 Let's describe a dated invoice. 00:00:39.185 --> 00:00:40.746 D means a date column. 00:00:40.926 --> 00:00:43.223 An invoice has its own date and number. 00:00:44.553 --> 00:00:46.869 Let's connect invoices to customers. 00:00:47.049 --> 00:00:51.497 The stored customer identifier refers to the key in the customer table. 00:00:52.850 --> 00:00:54.719 Let's describe an invoice row. 00:00:54.899 --> 00:00:57.503 It needs quantity, unit price, and total. 00:00:57.683 --> 00:01:02.791 N declares numeric columns; twelve comma two describes precision and scale. 00:01:04.121 --> 00:01:06.182 Let's connect the row at both ends. 00:01:06.362 --> 00:01:09.220 The invoice identifier points to its invoice. 00:01:09.400 --> 00:01:12.407 The product code points to the original catalogue. 00:01:13.767 --> 00:01:15.465 Let's apply the new model. 00:01:15.645 --> 00:01:19.943 The migration creates invoices and rows, including their foreign keys. 00:01:22.025 --> 00:01:23.999 Let's get fresh table handles. 00:01:24.179 --> 00:01:27.666 We will populate these four tables from CSV files. 00:01:29.067 --> 00:01:31.006 First, get the sample files. 00:01:31.186 --> 00:01:36.339 Download the lesson bundle and put its four CSV files in the current working folder. 00:01:36.519 --> 00:01:38.699 They contain fictional sample data. 00:01:40.029 --> 00:01:41.739 Let's inspect the row file. 00:01:41.919 --> 00:01:45.935 The CSV contains the keys, quantities, and unit prices. 00:01:46.115 --> 00:01:48.335 We will calculate the total ourselves. 00:01:49.733 --> 00:01:51.816 Let's expand the product catalogue. 00:01:51.996 --> 00:01:57.601 Read the new products, convert stock to an integer, insert each dictionary, and commit. 00:01:58.931 --> 00:02:00.554 Let's count the catalogue. 00:02:00.734 --> 00:02:04.917 Twenty-seven imported products plus our original three makes thirty. 00:02:06.247 --> 00:02:08.660 Let's import customers and their cities. 00:02:08.840 --> 00:02:10.867 Convert their keys to integers. 00:02:11.047 --> 00:02:14.299 Here are three customers, including Ada in Florence. 00:02:15.650 --> 00:02:17.380 Let's import the invoices. 00:02:17.560 --> 00:02:20.226 Their customer keys must already exist. 00:02:20.406 --> 00:02:24.396 Convert each year-month-day string to a Python date before inserting. 00:02:25.750 --> 00:02:27.659 Let's import the invoice rows. 00:02:27.839 --> 00:02:31.242 Convert each price directly from its text to Decimal. 00:02:31.422 --> 00:02:36.147 Multiply by quantity, round to two decimal places, and store the total. 00:02:36.327 --> 00:02:38.997 This calculation happens during import. 00:02:40.327 --> 00:02:42.168 Let's check how much we imported. 00:02:42.348 --> 00:02:45.250 Twelve invoices and thirty-six invoice rows. 00:02:46.650 --> 00:02:49.084 Let's read invoices with their customers. 00:02:49.264 --> 00:02:51.251 Dollar invoice date is local. 00:02:51.431 --> 00:02:54.120 At customer I D follows the relation. 00:02:54.300 --> 00:02:58.310 Asqueel automatically names the returned columns after their paths. 00:02:59.650 --> 00:03:01.582 Let's inspect the first invoice. 00:03:01.762 --> 00:03:03.973 Product names come through the relation. 00:03:04.153 --> 00:03:09.767 Quantity, unit price, and total belong to the row: two teas at three fifty make seven. 00:03:11.150 --> 00:03:13.224 Now follow a row to the customer. 00:03:13.404 --> 00:03:15.657 Go through the invoice, then the customer. 00:03:15.837 --> 00:03:18.348 The same path also reaches their city. 00:03:19.678 --> 00:03:21.823 Let's read across both connections. 00:03:22.003 --> 00:03:27.429 A single query returns invoice number, customer city, product name, and row total. 00:03:28.850 --> 00:03:30.496 Thanks for following along! 00:03:30.676 --> 00:03:36.666 We imported a catalogue and dated invoices, calculated row totals, and read fields through 00:03:36.666 --> 00:03:37.597 relationships. 00:03:37.777 --> 00:03:41.022 Next, we will use those same paths to filter and sort.