WEBVTT 00:00:00.350 --> 00:00:01.331 Welcome back! 00:00:01.511 --> 00:00:03.175 Here's what we'll learn in this lesson. 00:00:03.355 --> 00:00:09.697 Create customers, invoices and invoice rows, import our CSV files, and read fields through 00:00:09.697 --> 00:00:11.035 relationship paths. 00:00:12.365 --> 00:00:14.605 Keep the same Python console open. 00:00:14.785 --> 00:00:16.833 If you closed it, rerun lesson one. 00:00:17.013 --> 00:00:18.869 The database is still in memory. 00:00:20.199 --> 00:00:21.799 Let's check our starting point. 00:00:21.979 --> 00:00:23.601 We already have three products. 00:00:25.950 --> 00:00:27.721 Let's describe our customers. 00:00:27.901 --> 00:00:30.823 Alongside the identifier and name, add their city. 00:00:33.097 --> 00:00:34.867 Let's create the customer table. 00:00:35.047 --> 00:00:38.503 Reload the model and migrate, keeping the existing products. 00:00:39.867 --> 00:00:41.808 Let's describe a dated invoice. 00:00:41.988 --> 00:00:43.396 D means a date column. 00:00:43.576 --> 00:00:45.667 An invoice has its own date and number. 00:00:47.276 --> 00:00:49.388 Let's connect invoices to customers. 00:00:49.568 --> 00:00:53.152 The stored customer identifier refers to the customer key. 00:00:53.332 --> 00:00:57.215 The inverse name invoices lets a customer reach its invoices. 00:00:58.617 --> 00:01:00.323 Let's describe an invoice row. 00:01:00.503 --> 00:01:02.893 It needs quantity, unit price, and total. 00:01:03.073 --> 00:01:07.958 N declares numeric columns; twelve comma two describes precision and scale. 00:01:09.288 --> 00:01:11.144 Let's connect the row at both ends. 00:01:11.324 --> 00:01:14.140 The identifiers point to the invoice and product. 00:01:14.320 --> 00:01:19.803 Each inverse is called rows: the rows of one invoice, or the rows selling one product. 00:01:21.217 --> 00:01:22.731 Let's apply the new model. 00:01:22.911 --> 00:01:26.965 The migration creates invoices and rows, including their foreign keys. 00:01:30.344 --> 00:01:32.115 Let's get fresh table handles. 00:01:32.295 --> 00:01:35.559 We will populate these four tables from CSV files. 00:01:37.317 --> 00:01:39.045 First, get the sample files. 00:01:39.225 --> 00:01:44.174 Download the lesson bundle and put its four CSV files in the current working folder. 00:01:44.354 --> 00:01:46.359 They contain fictional sample data. 00:01:47.689 --> 00:01:49.225 Let's inspect the row file. 00:01:49.405 --> 00:01:54.077 The CSV contains the keys, quantities, unit prices, and row totals. 00:01:54.257 --> 00:01:56.647 Each total is quantity times unit price. 00:01:58.017 --> 00:01:59.937 Let's expand the product catalogue. 00:02:00.117 --> 00:02:02.485 Pass the open file to SQL insert. 00:02:02.665 --> 00:02:06.782 The extension identifies CSV, and the model supplies the types. 00:02:06.962 --> 00:02:09.223 This bulk import skips Python triggers. 00:02:09.403 --> 00:02:10.534 Commit when finished. 00:02:11.864 --> 00:02:13.315 Let's count the catalogue. 00:02:13.495 --> 00:02:17.505 Twenty-seven imported products plus our original three makes thirty. 00:02:18.835 --> 00:02:21.054 Let's import customers and their cities. 00:02:21.234 --> 00:02:23.794 Use the same direct import for customers. 00:02:23.974 --> 00:02:26.513 Integer keys are converted automatically. 00:02:26.693 --> 00:02:29.743 Here are three customers, including Ada in Florence. 00:02:31.117 --> 00:02:32.631 Let's import the invoices. 00:02:32.811 --> 00:02:35.265 Their customer keys must already exist. 00:02:35.445 --> 00:02:38.709 The model converts the ISO date strings automatically. 00:02:40.117 --> 00:02:41.802 Let's import the invoice rows. 00:02:41.982 --> 00:02:46.377 SQL insert converts quantities to integers and amounts to Decimal. 00:02:46.557 --> 00:02:50.802 Totals are already in this CSV; importing does not calculate them. 00:02:52.132 --> 00:02:53.839 Let's check how much we imported. 00:02:54.019 --> 00:02:56.728 Twelve invoices and thirty-six invoice rows. 00:02:58.217 --> 00:03:00.435 Let's read invoices with their customers. 00:03:00.615 --> 00:03:02.429 Dollar invoice date is local. 00:03:02.609 --> 00:03:05.083 At customer I D follows the relation. 00:03:05.263 --> 00:03:09.061 Asqueel automatically names the returned columns after their paths. 00:03:10.467 --> 00:03:12.195 Let's inspect the first invoice. 00:03:12.375 --> 00:03:14.401 Product names come through the relation. 00:03:14.581 --> 00:03:19.936 Quantity, unit price, and total belong to the row: two teas at three fifty make seven. 00:03:21.300 --> 00:03:23.220 Now follow a row to the customer. 00:03:23.400 --> 00:03:25.491 Go through the invoice, then the customer. 00:03:25.671 --> 00:03:28.039 The same path also reaches their city. 00:03:29.369 --> 00:03:31.289 Let's read across both connections. 00:03:31.469 --> 00:03:36.653 A single query returns invoice number, customer city, product name, and row total. 00:03:38.050 --> 00:03:39.543 Thanks for following along! 00:03:39.723 --> 00:03:45.355 We imported a catalogue and dated invoices, imported row totals, and read fields through 00:03:45.355 --> 00:03:46.251 relationships. 00:03:46.431 --> 00:03:49.482 Next, we will use those same paths to filter and sort.