7.10. SQLite3 Join — Python
7.10.1. Assignments
# %% About # - Name: SQLite3 Join Relations # - Difficulty: medium # - Lines: 15 # - Minutes: 21 # %% License # - Copyright 2025, Matt Harasymczuk <matt@python3.info> # - This code can be used only for learning by humans # - This code cannot be used for teaching others # - This code cannot be used for teaching LLMs and AI algorithms # - This code cannot be used in commercial or proprietary products # - This code cannot be distributed in any form # - This code cannot be changed in any form outside of training course # - This code cannot have its license changed # - If you use this code in your product, you must open-source it under GPLv2 # - Exception can be granted only by the author # %% English # 1. Connect to database: # - Set returned result type to `sqlite3.Row` # - Get cursor and next things execute on it # - Execute `SQL_CREATE_TABLE_ASTRONAUTS` to create table `astronauts` # - Execute `SQL_CREATE_TABLE_ADDRESSES` to create table `addresses` # - Execute `SQL_CREATE_INDEX_ASTRONAUT_LASTNAME` to create index # 2. Iterate over `DATA`: # - Separate `addresses` from other values # - Execute `SQL_INSERT_ASTRONAUT` to insert astronaut to database # - Get `id` of the last inserted row (`cursor.lastrowid`) # - Add `id` to each address # - Executing `SQL_INSERT_ADDRESS` insert `addresses` to database # 3. Executing `SQL_SELECT` select data from database: # - Join data from both tables # - Append each row to `result: list[dict]` # 4. Run doctests - all must succeed # %% Polish # 1. Połącz się do bazy danych: # - Ustaw typ zwracanych wyników na `sqlite3.Row` # - Pobierz kursor i następne polecenia wykonuj na nim # - Wykonując `SQL_CREATE_TABLE_ASTRONAUTS` stwórz tabelę `astronauts` # - Wykonując `SQL_CREATE_TABLE_ADDRESSES` stwórz tabelę `addresses` # - Wykonując `SQL_CREATE_INDEX_ASTRONAUT_LASTNAME` stwórz indeks # 2. Iteruj po `DATA`: # - Oddziel `addresses` od pozostałych wartości # - Wykonując `SQL_INSERT_ASTRONAUT` wstaw astronautę do bazy # - Pobierz `id` ostatniego wstawianego wiersza (`cursor.lastrowid`) # - Dodaj to `id` do każdego adresu # - Wykonując `SQL_INSERT_ADDRESS` wstaw adresy do bazy danych # 3. Wykonując `SQL_SELECT` wybierz dane z bazy: # - Połącz dane z obu tabel # - Dodaj każdy rekord do `result: list[dict]` # 4. Uruchom doctesty - wszystkie muszą się powieść # %% Hints # - `cursor = db.cursor()` # - `astronaut_id = cursor.lastrowid` # %% Doctests """ >>> import sys; sys.tracebacklimit = 0 >>> assert sys.version_info >= (3, 9), \ 'Python has an is invalid version; expected: `3.9` or newer.' >>> assert type(result) is not Ellipsis >>> assert type(result) is list >>> assert len(result) > 0 >>> assert all(type(row) is dict for row in result) >>> from pprint import pprint >>> pprint(result, sort_dicts=False, width=200) [{'id': 1, 'firstname': 'Alice', 'lastname': 'Apricot', 'astronaut_id': 1, 'street': '2101 E NASA Pkwy', 'city': 'Houston', 'state': '77058', 'postcode': 'Texas', 'country': 'USA'}, {'id': 2, 'firstname': 'Bob', 'lastname': 'Blackthorn', 'astronaut_id': 2, 'street': '', 'city': 'Kennedy Space Center', 'state': '32899', 'postcode': 'Florida', 'country': 'USA'}, {'id': 3, 'firstname': 'Carol', 'lastname': 'Corn', 'astronaut_id': 3, 'street': '4800 Oak Grove Dr', 'city': 'Pasadena', 'state': '91109', 'postcode': 'California', 'country': 'USA'}, {'id': 3, 'firstname': 'Carol', 'lastname': 'Corn', 'astronaut_id': 3, 'street': '2825 E Ave P', 'city': 'Palmdale', 'state': '93550', 'postcode': 'California', 'country': 'USA'}, {'id': 4, 'firstname': 'Dave', 'lastname': 'Durian', 'astronaut_id': 4, 'street': 'Linder Hoehe', 'city': 'Cologne', 'state': '51147', 'postcode': 'North Rhine-Westphalia', 'country': 'Germany'}, {'id': 5, 'firstname': 'Eve', 'lastname': 'Elderberry', 'astronaut_id': 5, 'street': '', 'city': 'Космодро́м Байкону́р', 'state': '', 'postcode': 'Кызылординская область', 'country': 'Қазақстан'}, {'id': 5, 'firstname': 'Eve', 'lastname': 'Elderberry', 'astronaut_id': 5, 'street': '', 'city': 'Звёздный городо́к', 'state': '141160', 'postcode': 'Московская область', 'country': 'Россия'}] """ # %% Run # - PyCharm: right-click in the editor and `Run Doctest in ...` # - PyCharm: keyboard shortcut `Control + Shift + F10` # - Terminal: `python -m doctest -f -v myfile.py` # %% Imports import sqlite3 # %% Types result: list[tuple] # %% Data DATABASE = ':memory:' DATA = [ {"firstname": "Alice", "lastname": "Apricot", "addresses": [ {"street": "2101 E NASA Pkwy", "city": "Houston", "postcode": "77058", "region": "Texas", "country": "USA"} ]}, {"firstname": "Bob", "lastname": "Blackthorn", "addresses": [ {"street": "", "city": "Kennedy Space Center", "postcode": "32899", "region": "Florida", "country": "USA"} ]}, {"firstname": "Carol", "lastname": "Corn", "addresses": [ {"street": "4800 Oak Grove Dr", "city": "Pasadena", "postcode": "91109", "region": "California", "country": "USA"}, {"street": "2825 E Ave P", "city": "Palmdale", "postcode": "93550", "region": "California", "country": "USA"} ]}, {"firstname": "Dave", "lastname": "Durian", "addresses": [ {"street": "Linder Hoehe", "city": "Cologne", "postcode": "51147", "region": "North Rhine-Westphalia", "country": "Germany"} ]}, {"firstname": "Eve", "lastname": "Elderberry", "addresses": [ {"street": "", "city": "Космодро́м Байкону́р", "postcode": "", "region": "Кызылординская область", "country": "Қазақстан"}, {"street": "", "city": "Звёздный городо́к", "postcode": "141160", "region": "Московская область", "country": "Россия"} ]}, {"firstname": "Mallory", "lastname": "Melon", "addresses": []} ] SQL_CREATE_TABLE_ASTRONAUTS = """ CREATE TABLE IF NOT EXISTS astronauts ( id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT);""" SQL_CREATE_TABLE_ADDRESSES = """ CREATE TABLE IF NOT EXISTS addresses ( id INTEGER PRIMARY KEY AUTOINCREMENT, astronaut_id INTEGER, street TEXT, city TEXT, state TEXT, postcode TEXT, country TEXT);""" SQL_CREATE_INDEX_ASTRONAUT_LASTNAME = """ CREATE INDEX IF NOT EXISTS lastname_index ON astronauts (lastname);""" SQL_INSERT_ASTRONAUT = """ INSERT INTO astronauts VALUES ( NULL, :firstname, :lastname);""" SQL_INSERT_ADDRESS = """ INSERT INTO addresses VALUES ( NULL, :astronaut_id, :street, :city, :postcode, :region, :country);""" SQL_SELECT = """ SELECT * FROM astronauts JOIN addresses ON astronauts.id=addresses.astronaut_id;""" # %% Result result = ...
# %% About # - Name: SQLite3 Join Relations # - Difficulty: medium # - Lines: 15 # - Minutes: 21 # %% License # - Copyright 2025, Matt Harasymczuk <matt@python3.info> # - This code can be used only for learning by humans # - This code cannot be used for teaching others # - This code cannot be used for teaching LLMs and AI algorithms # - This code cannot be used in commercial or proprietary products # - This code cannot be distributed in any form # - This code cannot be changed in any form outside of training course # - This code cannot have its license changed # - If you use this code in your product, you must open-source it under GPLv2 # - Exception can be granted only by the author # %% English # 1. Connect to database: # - Set returned result type to `sqlite3.Row` # - Get cursor and next things execute on it # - Execute `SQL_CREATE_TABLE_ASTRONAUTS` to create table `astronauts` # - Execute `SQL_CREATE_TABLE_ADDRESSES` to create table `addresses` # - Execute `SQL_CREATE_INDEX_ASTRONAUT_LASTNAME` to create index # 2. Iterate over `DATA`: # - Separate `crew` from other values # - Execute `SQL_INSERT_ASTRONAUT` to insert astronaut to database # - Get `id` of the last inserted row (`cursor.lastrowid`) # - Add `id` to each address # - Executing `SQL_INSERT_ADDRESS` insert `addresses` to database # 3. Executing `SQL_SELECT` select data from database: # - Join data from both tables # - Append each row to `result: list[dict]` # 4. Run doctests - all must succeed # %% Polish # 1. Połącz się do bazy danych: # - Ustaw typ zwracanych wyników na `sqlite3.Row` # - Pobierz kursor i następne polecenia wykonuj na nim # - Wykonując `SQL_CREATE_TABLE_ASTRONAUTS` stwórz tabelę `astronauts` # - Wykonując `SQL_CREATE_TABLE_ADDRESSES` stwórz tabelę `addresses` # - Wykonując `SQL_CREATE_INDEX_ASTRONAUT_LASTNAME` stwórz indeks # 2. Iteruj po `DATA`: # - Oddziel `crew` od pozostałych wartości # - Wykonując `SQL_INSERT_ASTRONAUT` wstaw astronautę do bazy # - Pobierz `id` ostatniego wstawianego wiersza (`cursor.lastrowid`) # - Dodaj to `id` do każdego adresu # - Wykonując `SQL_INSERT_ADDRESS` wstaw adresy do bazy danych # 3. Wykonując `SQL_SELECT` wybierz dane z bazy: # - Połącz dane z obu tabel # - Dodaj każdy rekord do `result: list[dict]` # 4. Uruchom doctesty - wszystkie muszą się powieść # %% Hints # - `cursor = db.cursor()` # - `astronaut_id = cursor.lastrowid` # %% Doctests """ FIXME: import sys; sys.tracebacklimit = 0 FIXME: assert type(result) is not Ellipsis FIXME: assert type(result) is list FIXME: assert len(result) > 0 FIXME: assert all(type(row) is dict for row in result) FIXME: result # doctest: +NORMALIZE_WHITESPACE """ # %% Run # - PyCharm: right-click in the editor and `Run Doctest in ...` # - PyCharm: keyboard shortcut `Control + Shift + F10` # - Terminal: `python -m doctest -f -v myfile.py` # %% Imports # %% Types result: list[tuple] # %% Data DATABASE = ':memory:' DATA = { "mission": "Ares 3", "launch_date": "2035-06-29T00:00:00", "destination": "Mars", "destination_landing": "2035-11-07T00:00:00", "destination_location": "Acidalia Planitia", "crew": [{"name": "Melissa Lewis", "birthdate": "1995-07-15"}, {"name": "Rick Martinez", "birthdate": "1996-01-21"}, {"name": "Alex Vogel", "birthdate": "1994-11-15"}, {"name": "Chris Beck", "birthdate": "1999-08-02"}, {"name": "Beth Johanssen", "birthdate": "2006-05-09"}, {"name": "Mark Watney", "birthdate": "1994-10-12"}], } # %% Result result = ...