Asqueel

Course / Orientation / Lesson 07

Lesson 07 · Orientation

Fetch and export

Reuse the city report to choose Python result formats, retrieve records or keys, read batches and export CSV or JSON.

2:49 · video10 scenesPrerequisites: the in-memory Python session from lesson 6

Asqueel 0.6.0 · Python console demonstration · API compatibility

Course contents
Orientation
  1. 01 Welcome: your first table
  2. 02 Customers, invoices and relationship paths
  3. 03 Filter and sort through relationships
  4. 04 Aliases and formula columns
  5. 05 Aggregate related records
  6. 06 Group by and HAVING
  7. 07 Fetch and export
  8. 08 Optional: find the best buyer
  9. 09 Writes and transactions
  10. 10 Python triggers
The model
  1. 11 virtualRelation
  2. 12 Derived columns: aliasColumn, formulaColumn
  3. 13 Advanced formulas
  4. 14 subQueryColumn and bagItemColumn
  5. 15 Layered recipes
  6. 16 Extending the grammar
  7. 17 The model hosts external metadata
Reading data
  1. 18 query(...): syntax and clauses
  2. 19 Parameters and macros
  3. 20 Fetch terminals
  4. 21 The environment
  5. 22 Row policies
  6. 23 Locking on read: for_update
Writing data
  1. 24 insert, update, delete
  2. 25 The order of the write cycle
  3. 26 Field triggers
  4. 27 Relation actions: Python and SQL
  5. 28 Writes without triggers: sql_*, table.lock
  6. 29 sys_fields, hierarchical, auto_counter
  7. 30 recordToUpdate and batchUpdate
Transactions & operations
  1. 31 Transactions and connections
  2. 32 Deferred callbacks
  3. 33 Direct SQL
  4. 34 Import and migrations
  5. 35 The command line
  6. 36 Integration in a host application
  7. 37 The complete example
Other Python ORMs
  1. 38 asqueel, SQLAlchemy, Django and Peewee
WATCH THE LESSON2:49 · English

Optional extension: Find the best buyer →

Download MP4 ↓English captions ↓

Follow the chapters, then try the examples yourself. The complete transcript and code are below.

Try it yourself

Run this lesson in Google Colab, with the starting data included. A Google account is required to execute code. You can also download the notebook for local Jupyter.

Try it yourself ↗Download notebook ↓

Download this example ↓ · Includes four CSV files and console commands. Put the CSV files in your working folder. If Python was closed, use start-here.py to recreate lessons one through six, including the city report query. This lesson writes CSV and JSON files.

Download individual CSV files: customers.csv · invoice_rows.csv · invoices.csv · products.csv

Scene 01

What we'll learn in this lesson

Welcome back! Here's what we'll learn. Use the city report we built in lesson six. We will read named rows, index or group records in Python, fetch one record or keys, read batches, and export CSV and JSON. The query is already ready; today we focus on its results.

Scene 02

Fetch rows and read their fields

Let's inspect a fetched row. Fetch returns a list of named rows. Read a field by its name, or turn a row into an ordinary Python dictionary with dict. Each fetch executes the query again.

Example & screen notes
>>> 
>>> ######## Inspect a fetched row ########
>>> 
>>> records = report.fetch()
>>> records[0]
[city=Amsterdam,sales=26.2,row_count=3]
>>> records[0]["sales"]
26.2
>>> dict(records[0])
{'city': 'Amsterdam', 'sales': 26.2, 'row_count': 3}

Scene 03

Index the summaries by city

Let's look up a city directly. Fetch as dict indexes the result rows by the selected key. Our grouped cities are unique, so none are lost. The values are still named rows. With a repeated key, a later row would replace the earlier one.

Example & screen notes
>>> 
>>> ######## Look up a city directly ########
>>> 
>>> by_city = report.fetchAsDict(key="city")
>>> by_city["Florence"]
[city=Florence,sales=300.65,row_count=12]
>>> by_city["Florence"]["sales"]
300.65

Scene 04

Group detail rows in Python

Let's keep the detail rows too. Fetch grouped creates Python lists, one for each city. It does not calculate SQL aggregates. Florence keeps all twelve detail rows. Use this when you want groups of records rather than summary totals.

Example & screen notes
>>> 
>>> ######## Keep the detail rows too ########
>>> 
>>> details = rows.query(
...     columns="$id, $city, $quantity, $calculated_total",
...     order_by="$id",
... )
>>> groups = details.fetchGrouped(key="city")
>>> [(city, len(items)) for city, items in groups.items()]
[('Florence', 12), ('London', 6), ('Helsinki', 6), ('Amsterdam', 3), ('Manchester', 3), ('New York', 3), ('Boston', 3)]
>>> groups["Florence"][0]
[id=1,city=Florence,quantity=2,calculated_total=7.0]

Scene 05

Fetch one record or just the keys

Let's fetch one known product. Fetch record returns a single row. This filter uses a unique key. A missing row raises an error unless ignoreMissing allows None. Multiple rows raise an error unless explicit order by selects the first ordered row. Let's fetch just the product keys. Fetch pkeys returns a list of primary-key values in query order. Our limit selects the first three drink codes.

Example & screen notes
>>> 
>>> ######## Fetch one known product ########
>>> 
>>> products.query(
...     columns="$code, $name, $stock",
...     where="$code = :code", code="TEA",
... ).fetchRecord()
[code=TEA,name=Green tea,stock=8]

>>> 
>>> ######## Fetch just the product keys ########
>>> 
>>> products.query(
...     where="$category = :category", category="Drinks",
...     order_by="$code", limit=3,
... ).fetchPkeys()
['BTEA', 'COF', 'HTEA']

Scene 06

Get JSON text

Let's get the report as JSON. Fetch as JSON returns text. Decode it to inspect the first object. This creates no file. We will export JSON to a file shortly.

Example & screen notes
>>> 
>>> ######## Get the report as JSON ########
>>> 
>>> import json
>>> payload = report.fetchAsJson()
>>> type(payload).__name__
'str'
>>> json.loads(payload)[0]
{'city': 'Amsterdam', 'sales': 26.2, 'row_count': 3}

Scene 07

Read larger results in batches

Let's fetch the details in batches. Iterfetch yields batches of rows. Here they contain ten, ten, ten and six records. We skip the final empty batch. This example records only the sizes; real code can process each batch as it arrives.

Example & screen notes
>>> 
>>> ######## Fetch the details in batches ########
>>> 
>>> [len(batch) for batch in details.iterfetch(arraysize=10) if batch]
[10, 10, 10, 6]

Scene 08

Export the report directly to CSV

Let's export the CSV report. Pass a destination to fetch. The file extension chooses the format, and the return value is seven: the number of rows written. The headings use our optional aliases. Only the report file is saved; SQLite is still in memory.

Example & screen notes
>>> 
>>> ######## Export the CSV report ########
>>> 
>>> report.fetch("sales-by-city.csv")
7
>>> from pathlib import Path
>>> print(Path("sales-by-city.csv").read_text())
city,sales,row_count
Amsterdam,26.2,3
Boston,57.8,3
Florence,300.65,12
Helsinki,57.2,6
London,189.7,6
Manchester,87.4,3
New York,96.3,3

Scene 09

Export the same report to JSON

Let's export JSON as well. The same query writes a JSON array when the destination ends in dot json. Again, fetch returns a row count. Existing destination files are replaced. The notebook includes a way to download both reports.

Example & screen notes
>>> 
>>> ######## Export JSON as well ########
>>> 
>>> report.fetch("sales-by-city.json")
7
>>> json.loads(Path("sales-by-city.json").read_text())[0]
{'city': 'Amsterdam', 'sales': 26.2, 'row_count': 3}
>>> db.commit()

Scene 10

Thank you — use and export the results

Thanks for following along! We reused one report query, chose how to receive its results in Python, and exported two files. You can now try the optional best-buyer exercise, or go straight to lesson nine for writes and transactions.

Optional extension: Find the best buyer →