WEBVTT 00:00:00.350 --> 00:00:01.139 Welcome! 00:00:01.319 --> 00:00:05.138 Today I'm introducing Asqueel, a Python adapter for SQL. 00:00:06.468 --> 00:00:08.132 Here's what we'll learn in this lesson. 00:00:08.312 --> 00:00:14.456 Install Asqueel, create a product table, write and query data, add columns, and import CSV. 00:00:14.636 --> 00:00:18.007 We'll work in one Python terminal with SQLite in memory. 00:00:18.187 --> 00:00:19.893 Closing it discards the data. 00:00:21.267 --> 00:00:23.464 Let's create an isolated environment. 00:00:23.644 --> 00:00:26.631 Python puts its packages in this dot venv folder. 00:00:27.961 --> 00:00:29.177 Let's activate it. 00:00:29.357 --> 00:00:31.682 These are the macOS and Linux commands. 00:00:33.012 --> 00:00:34.441 Let's install Asqueel. 00:00:34.621 --> 00:00:39.123 The migration extra includes the engine that creates and changes our tables. 00:00:40.517 --> 00:00:41.818 Let's open Python. 00:00:41.998 --> 00:00:44.814 We'll keep this console open for the whole example. 00:00:46.144 --> 00:00:47.701 Let's import our two tools. 00:00:47.881 --> 00:00:51.487 One describes the model; the other connects it to a database. 00:00:52.850 --> 00:00:54.749 Let's choose SQLite in memory. 00:00:54.929 --> 00:00:57.062 This model lives right here in Python. 00:00:57.242 --> 00:00:59.439 No model file or class is needed. 00:01:00.769 --> 00:01:02.540 Let's describe a product table. 00:01:02.720 --> 00:01:04.235 It needs a code and a name. 00:01:04.415 --> 00:01:07.785 Code is the unique key; both columns contain text. 00:01:09.183 --> 00:01:10.634 Let's create that table. 00:01:10.814 --> 00:01:15.017 Migrate compares the model with the database and runs the missing SQL. 00:01:15.197 --> 00:01:17.181 Here is the create table statement. 00:01:18.583 --> 00:01:20.183 Let's add our first product. 00:01:20.363 --> 00:01:22.731 Insert tea, then commit the transaction. 00:01:22.911 --> 00:01:25.194 The data stays in memory, not on disk. 00:01:26.583 --> 00:01:27.949 Let's read our product. 00:01:28.129 --> 00:01:30.902 Select its code and name, then fetch the result. 00:01:32.232 --> 00:01:33.533 Let's change its name. 00:01:33.713 --> 00:01:36.871 Find the row by its key, set Green tea, and commit. 00:01:38.201 --> 00:01:39.545 Let's check the update. 00:01:39.725 --> 00:01:42.071 The same query now returns Green tea. 00:01:43.583 --> 00:01:45.311 Let's track category and stock. 00:01:45.491 --> 00:01:49.246 Add a text column for category and an integer column for stock. 00:01:50.576 --> 00:01:52.219 Let's load the updated model. 00:01:52.399 --> 00:01:56.324 Reload changes the running model while keeping our database connection. 00:01:57.683 --> 00:01:59.518 Let's add those database columns. 00:01:59.698 --> 00:02:04.434 Migrate now generates two add column statements, without recreating the table. 00:02:05.764 --> 00:02:07.449 Let's check our original row. 00:02:07.629 --> 00:02:09.080 Get a fresh table handle. 00:02:09.260 --> 00:02:12.289 Green tea is still there; stock has no value yet. 00:02:13.683 --> 00:02:15.134 Let's give tea its stock. 00:02:15.314 --> 00:02:18.770 Set its category to Drinks and its stock to eight, then commit. 00:02:20.100 --> 00:02:21.551 Let's read the new values. 00:02:21.731 --> 00:02:23.864 The query includes our two new columns. 00:02:25.850 --> 00:02:27.727 Let's add products from CSV. 00:02:27.907 --> 00:02:31.278 For this example, keep the CSV in a Python string too. 00:02:31.458 --> 00:02:33.549 Its headers match the table columns. 00:02:34.879 --> 00:02:36.372 Let's import those rows. 00:02:36.552 --> 00:02:40.200 Pass the stream to SQL insert and specify CSV. 00:02:40.380 --> 00:02:44.839 Asqueel converts stock using the model and returns the imported row count. 00:02:45.019 --> 00:02:45.872 Then commit. 00:02:47.267 --> 00:02:48.717 Let's list just the drinks. 00:02:48.897 --> 00:02:52.972 Use a parameter for the category and sort by stock, largest first. 00:02:53.152 --> 00:02:55.776 Coffee from the CSV, then our Green tea. 00:02:57.183 --> 00:02:58.421 Thanks for watching! 00:02:58.601 --> 00:03:04.275 Next, we'll add a customer table and follow the relationships in a small invoicing example.