WEBVTT 00:00:00.350 --> 00:00:01.289 Welcome! 00:00:01.469 --> 00:00:05.480 Today I'm introducing Asqueel, a Python adapter for SQL. 00:00:06.810 --> 00:00:09.777 We'll start in the terminal, using temporary data. 00:00:09.957 --> 00:00:12.104 More complex examples will follow. 00:00:13.517 --> 00:00:15.909 Let's create an isolated environment. 00:00:16.089 --> 00:00:19.257 Python puts its packages in this dot venv folder. 00:00:20.587 --> 00:00:21.967 Let's activate it. 00:00:22.147 --> 00:00:24.668 These are the macOS and Linux commands. 00:00:25.998 --> 00:00:27.589 Let's install Asqueel. 00:00:27.769 --> 00:00:32.493 The migration extra includes the engine that creates and changes our tables. 00:00:33.917 --> 00:00:35.392 Let's open Python. 00:00:35.572 --> 00:00:38.581 We'll keep this console open for the whole example. 00:00:39.911 --> 00:00:41.662 Let's import our two tools. 00:00:41.842 --> 00:00:45.670 One describes the model; the other connects it to a database. 00:00:47.017 --> 00:00:49.069 Let's choose SQLite in memory. 00:00:49.249 --> 00:00:51.618 This model lives right here in Python. 00:00:51.798 --> 00:00:54.169 No model file or class is needed. 00:00:55.499 --> 00:00:57.444 Let's describe a product table. 00:00:57.624 --> 00:00:59.333 It needs a code and a name. 00:00:59.513 --> 00:01:03.085 Code is the unique key; both columns contain text. 00:01:04.433 --> 00:01:06.057 Let's create that table. 00:01:06.237 --> 00:01:10.652 Migrate compares the model with the database and runs the missing SQL. 00:01:10.832 --> 00:01:12.990 Here is the create table statement. 00:01:14.350 --> 00:01:16.144 Let's add our first product. 00:01:16.324 --> 00:01:18.885 Insert tea, then commit the transaction. 00:01:19.065 --> 00:01:21.561 The data stays in memory, not on disk. 00:01:22.950 --> 00:01:24.509 Let's read our product. 00:01:24.689 --> 00:01:27.655 Select its code and name, then fetch the result. 00:01:28.985 --> 00:01:30.471 Let's change its name. 00:01:30.651 --> 00:01:33.991 Find the row by its key, set Green tea, and commit. 00:01:35.321 --> 00:01:36.826 Let's check the update. 00:01:37.006 --> 00:01:39.548 The same query now returns Green tea. 00:01:40.950 --> 00:01:42.882 Let's track category and stock. 00:01:43.062 --> 00:01:47.028 Add a text column for category and an integer column for stock. 00:01:48.358 --> 00:01:50.185 Let's load the updated model. 00:01:50.365 --> 00:01:54.503 Reload changes the running model while keeping our database connection. 00:01:55.867 --> 00:01:57.905 Let's add those database columns. 00:01:58.085 --> 00:02:03.034 Migrate now generates two add column statements, without recreating the table. 00:02:04.364 --> 00:02:06.223 Let's check our original row. 00:02:06.403 --> 00:02:08.048 Get a fresh table handle. 00:02:08.228 --> 00:02:11.460 Green tea is still there; stock has no value yet. 00:02:12.867 --> 00:02:14.511 Let's give tea its stock. 00:02:14.691 --> 00:02:18.339 Set its category to Drinks and its stock to eight, then commit. 00:02:19.669 --> 00:02:21.325 Let's read the new values. 00:02:21.505 --> 00:02:23.842 The query includes our two new columns. 00:02:25.267 --> 00:02:27.308 Let's add products from CSV. 00:02:27.488 --> 00:02:31.051 For this example, keep the CSV in a Python string too. 00:02:31.231 --> 00:02:33.569 Its headers match the table columns. 00:02:34.899 --> 00:02:36.586 Let's import those rows. 00:02:36.766 --> 00:02:42.043 Read each row as a dictionary, convert stock to an integer, insert it, and commit. 00:02:43.467 --> 00:02:45.080 Let's list just the drinks. 00:02:45.260 --> 00:02:49.536 Use a parameter for the category and sort by stock, largest first. 00:02:49.716 --> 00:02:52.533 Coffee from the CSV, then our Green tea. 00:02:53.883 --> 00:02:55.273 Thanks for watching! 00:02:55.453 --> 00:03:01.329 Next, we'll add a customer table and follow the relationships in a small invoicing example.