Scene 01
Welcome to Asqueel
Welcome! Today I'm introducing Asqueel, a Python adapter for SQL. We'll start in the terminal, using temporary data. More complex examples will follow.
Example & screen notes
Welcome to Asqueel
A Python adapter for SQL
First lessons: Python terminal
SQLite in memory
Close the session → data disappears
Later: more complex examples
Scene 02
Create a virtual environment
Let's create an isolated environment. Python puts its packages in this dot venv folder. Let's activate it. These are the macOS and Linux commands. Let's install Asqueel. The migration extra includes the engine that creates and changes our tables.
Example & screen notes
$ python3 -m venv .venv
$ source .venv/bin/activate
(.venv) $ python -m pip install "asqueel[migration]"
Scene 03
Start Python
Let's open Python. We'll keep this console open for the whole example. Let's import our two tools. One describes the model; the other connects it to a database.
Example & screen notes
(.venv) $ python
>>> from asqueel import AsqueelDb, SqlDatabaseConfig
Scene 04
Describe a product table
Let's choose SQLite in memory. This model lives right here in Python. No model file or class is needed. Let's describe a product table. It needs a code and a name. Code is the unique key; both columns contain text.
Example & screen notes
>>> model = SqlDatabaseConfig()
>>> root = model.source.db(implementation="sqlite")
>>> _ = root.connection(name=":memory:", implementation="sqlite")
>>> shop = root.packages().package("shop", sqlschema="shop")
>>> product = shop.table("product", pkey="code")
>>> _ = product.column("code", size=":10")
>>> _ = product.column("name", size=":60")
Scene 05
Create the table
Let's create that table. Migrate compares the model with the database and runs the missing SQL. Here is the create table statement.
Example & screen notes
>>> db = AsqueelDb(model)
>>> print(db.migrate().commands)
CREATE TABLE "shop"."shop_product"(
"code" character varying(10) NOT NULL,
"name" character varying(60),
PRIMARY KEY ("code")
);
Scene 06
Insert a product
Let's add our first product. Insert tea, then commit the transaction. The data stays in memory, not on disk.
Example & screen notes
>>> products = db.table("shop.product")
>>> products.insert({"code": "TEA", "name": "Tea"})
{'code': 'TEA', 'name': 'Tea'}
>>> db.commit()
Scene 07
Read and update
Let's read our product. Select its code and name, then fetch the result. Let's change its name. Find the row by its key, set Green tea, and commit. Let's check the update. The same query now returns Green tea.
Example & screen notes
>>> products.query(columns="$code, $name").fetch()
[[code=TEA,name=Tea]]
>>> products.update({"name": "Green tea"}, pkey="TEA")
{'name': 'Green tea'}
>>> db.commit()
>>> products.query(columns="$code, $name").fetch()
[[code=TEA,name=Green tea]]
>>> db.commit()
Scene 08
Add two columns
Let's track category and stock. Add a text column for category and an integer column for stock. Let's load the updated model. Reload changes the running model while keeping our database connection.
Example & screen notes
>>> _ = product.column("category", size=":30")
>>> _ = product.column("stock", dtype="I")
>>> db.reload_model(model)
Scene 09
Migrate again
Let's add those database columns. Migrate now generates two add column statements, without recreating the table. Let's check our original row. Get a fresh table handle. Green tea is still there; stock has no value yet.
Example & screen notes
>>> print(db.migrate().commands)
ALTER TABLE "shop"."shop_product"
ADD COLUMN "category" character varying(30);
ALTER TABLE "shop"."shop_product"
ADD COLUMN "stock" integer;
>>> products = db.table("shop.product")
>>> products.query(columns="$name, $stock").fetch()
[[name=Green tea,stock=None]]
>>> db.commit()
Scene 10
Write the new fields
Let's give tea its stock. Set its category to Drinks and its stock to eight, then commit. Let's read the new values. The query includes our two new columns.
Example & screen notes
>>> products.update({"category": "Drinks", "stock": 8}, pkey="TEA")
{'category': 'Drinks', 'stock': 8}
>>> db.commit()
>>> products.query(columns="$name, $category, $stock").fetch()
[[name=Green tea,category=Drinks,stock=8]]
>>> db.commit()
Scene 11
Import CSV data
Let's add products from CSV. For this example, keep the CSV in a Python string too. Its headers match the table columns. Let's import those rows. Read each row as a dictionary, convert stock to an integer, insert it, and commit.
Example & screen notes
>>> import csv
>>> from io import StringIO
>>> data = StringIO(
... "code,name,category,stock\n"
... "COF,Coffee,Drinks,24\n"
... "MUG,Mug,Kitchen,12\n"
... )
>>> for row in csv.DictReader(data):
... row["stock"] = int(row["stock"])
... _ = products.insert(row)
...
>>> db.commit()
Scene 12
Query the imported rows
Let's list just the drinks. Use a parameter for the category and sort by stock, largest first. Coffee from the CSV, then our Green tea.
Example & screen notes
>>> products.query(
... columns="$name, $stock",
... where="$category = :category", category="Drinks",
... order_by="$stock DESC",
... ).fetch()
[[name=Coffee,stock=24], [name=Green tea,stock=8]]
>>> db.commit()
Scene 13
Thank you — next: customers
Thanks for watching! Next, we'll add a customer table and follow the relationships in a small invoicing example.
Example & screen notes
Thank you for following along!
Next lesson: add a customer table
Build invoices and follow relationship paths