Scene 01
What we'll learn in this lesson
Welcome! Today I'm introducing Asqueel, a Python adapter for SQL. Here's what we'll learn in this lesson. Install Asqueel, create a product table, write and query data, add columns, and import CSV. We'll work in one Python terminal with SQLite in memory. Closing it discards the data.
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
>>>
>>> ######## Import our two tools ########
>>>
>>> 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
>>>
>>> ######## Choose SQLite in memory ########
>>>
>>> model = SqlDatabaseConfig()
>>> root = model.source.db(implementation="sqlite")
>>> _ = root.connection(name=":memory:", implementation="sqlite")
>>>
>>> ######## Describe a product table ########
>>>
>>> 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
>>>
>>> ######## Create that table ########
>>>
>>> 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
>>>
>>> ######## Add our first product ########
>>>
>>> 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
>>>
>>> ######## Read our product ########
>>>
>>> products.query(columns="$code, $name").fetch()
[[code=TEA,name=Tea]]
>>>
>>> ######## Change its name ########
>>>
>>> products.update({"name": "Green tea"}, pkey="TEA")
{'name': 'Green tea'}
>>> db.commit()
>>>
>>> ######## Check the update ########
>>>
>>> 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
>>>
>>> ######## Track category and stock ########
>>>
>>> _ = product.column("category", size=":30")
>>> _ = product.column("stock", dtype="I")
>>>
>>> ######## Load the updated model ########
>>>
>>> db.reloadModel(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
>>>
>>> ######## Add those database columns ########
>>>
>>> print(db.migrate().commands)
ALTER TABLE "shop"."shop_product"
ADD COLUMN "category" character varying(30);
ALTER TABLE "shop"."shop_product"
ADD COLUMN "stock" integer;
>>>
>>> ######## Check our original row ########
>>>
>>> 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
>>>
>>> ######## Give tea its stock ########
>>>
>>> products.update({"category": "Drinks", "stock": 8}, pkey="TEA")
{'category': 'Drinks', 'stock': 8}
>>> db.commit()
>>>
>>> ######## Read the new values ########
>>>
>>> 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. Pass the stream to SQL insert and specify CSV. Asqueel converts stock using the model and returns the imported row count. Then commit.
Example & screen notes
>>>
>>> ######## Add products from CSV ########
>>>
>>> from io import StringIO
>>> data = StringIO(
... "code,name,category,stock\n"
... "COF,Coffee,Drinks,24\n"
... "MUG,Mug,Kitchen,12\n"
... )
>>>
>>> ######## Import those rows ########
>>>
>>> products.sql_insert(data, format="csv")
2
>>> 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
>>>
>>> ######## List just the drinks ########
>>>
>>> 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.