7.8. SQLite3 Fetch

• Fetch as list[tuple] / list[list]

• Fetch as list[Row] / list[dict]

• sqlite3.row_factory

7.8.1. Fetch Sequences

>>> import sqlite3
>>>
>>>
>>> DATABASE = ':memory:'
>>>
>>> SQL_CREATE_TABLE = """
...     CREATE TABLE IF NOT EXISTS astronauts (
...         id INTEGER PRIMARY KEY AUTOINCREMENT,
...         firstname TEXT,
...         lastname TEXT);"""
>>>
>>> SQL_INSERT = """
...     INSERT INTO astronauts
...     VALUES (NULL, :firstname, :lastname);"""
>>>
>>> SQL_SELECT = """
...     SELECT *
...     FROM astronauts;"""
>>>
>>> data = [
...     {'firstname': 'Mark', 'lastname': 'Watney'},
...     {'firstname': 'Melissa', 'lastname': 'Lewis'},
...     {'firstname': 'Rick', 'lastname': 'Martinez'},
...     {'firstname': 'Alex', 'lastname': 'Vogel'},
...     {'firstname': 'Beth', 'lastname': 'Johanssen'},
...     {'firstname': 'Chris', 'lastname': 'Beck'},
... ]
>>>
>>>
>>> with sqlite3.connect(DATABASE) as db:
...     _ = db.execute(SQL_CREATE_TABLE)
...     _ = db.executemany(SQL_INSERT, data)
...     for row in db.execute(SQL_SELECT):
...         print(row)
(1, 'Mark', 'Watney')
(2, 'Melissa', 'Lewis')
(3, 'Rick', 'Martinez')
(4, 'Alex', 'Vogel')
(5, 'Beth', 'Johanssen')
(6, 'Chris', 'Beck')

7.8.2. Fetch Mappings

>>> import sqlite3
>>>
>>>
>>> DATABASE = ':memory:'
>>>
>>> SQL_CREATE_TABLE = """
...     CREATE TABLE IF NOT EXISTS astronauts (
...         id INTEGER PRIMARY KEY AUTOINCREMENT,
...         firstname TEXT,
...         lastname TEXT);"""
>>>
>>> SQL_INSERT = """
...     INSERT INTO astronauts
...     VALUES (NULL, :firstname, :lastname);"""
>>>
>>> SQL_SELECT = """
...     SELECT *
...     FROM astronauts;"""
>>>
>>> data = [
...     {'firstname': 'Mark', 'lastname': 'Watney'},
...     {'firstname': 'Melissa', 'lastname': 'Lewis'},
...     {'firstname': 'Rick', 'lastname': 'Martinez'},
...     {'firstname': 'Alex', 'lastname': 'Vogel'},
...     {'firstname': 'Beth', 'lastname': 'Johanssen'},
...     {'firstname': 'Chris', 'lastname': 'Beck'},
... ]
>>>
>>>
>>> with sqlite3.connect(DATABASE) as db:
...     db.row_factory = sqlite3.Row
...     _ = db.execute(SQL_CREATE_TABLE)
...     _ = db.executemany(SQL_INSERT, data)
...     for row in db.execute(SQL_SELECT):
...         print(dict(row))
{'id': 1, 'firstname': 'Mark', 'lastname': 'Watney'}
{'id': 2, 'firstname': 'Melissa', 'lastname': 'Lewis'}
{'id': 3, 'firstname': 'Rick', 'lastname': 'Martinez'}
{'id': 4, 'firstname': 'Alex', 'lastname': 'Vogel'}
{'id': 5, 'firstname': 'Beth', 'lastname': 'Johanssen'}
{'id': 6, 'firstname': 'Chris', 'lastname': 'Beck'}

7.8.3. Assignments

# %% 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

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: SQLite3 Fetch Logs
# - Difficulty: easy
# - Lines: 4
# - Minutes: 5

# %% English
# 1. Extract date, time, log level and message from each line
# 2. Parse date and time as date and time objects
# 3. Combine date and time into datetime object
# 4. Define datetime, level and message as tuple
# 5. Connect to database:
#    - Execute `SQL_CREATE_TABLE` to create database table
#    - Execute `SQL_INSERT` to insert logs to in `list[tuple]` format
#    - Execute `SQL_SELECT` to select data
#    - Iterate over rows and append each to `result: list[tuple]`
# 6. Run doctests - all must succeed

# %% Polish
# 1. Wyciągnij datę, czas, poziom logowania i teść z każdej linii
# 2. Rozczytaj datę i czas jako obiekty date and time
# 3. Połącz datę i czas w obiekt datetime
# 4. Zdefiniuj datetime, level i message jako tuplę
# 5. Połącz się do bazy danych:
#    - Wykonaj `SQL_CREATE_TABLE` aby stworzyć tabelę w bazie danych
#    - Wykonaj `SQL_INSERT` aby wstawić logi w formacie `list[tuple]`
#    - Wykonaj `SQL_SELECT` aby wybrać dane
#    - Iterując po wierszach dopisuj je do `result: list[tuple]`
# 6. Uruchom doctesty - wszystkie muszą się powieść

# %% References
# [1] National Aeronautics and Space Administration.
#     Apollo 11 timeline.
#     Year: 1969. Retrieved: 2021-03-25.
#     URL: https://history.nasa.gov/SP-4029/Apollo_11i_Timeline.htm

# %% Hints
# - `datetime.fromisoformat(str)`
# - `datetime.combine(date, time)`
# - `datetime.strptime(str, format)`

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> assert sys.version_info >= (3, 9), \
'Python 3.9+ required'

>>> assert type(result) is not Ellipsis
>>> assert type(result) is list
>>> assert len(result) > 0
>>> assert all(type(row) is tuple for row in result)

>>> result  # doctest: +NORMALIZE_WHITESPACE
[(28, '1969-07-24 17:29:00', 'INFO', 'Crew egress'),
 (27, '1969-07-24 16:50:35', 'WARNING', 'Splashdown (went to apex-down)'),
 (26, '1969-07-24 16:35:05', 'WARNING', 'Entry'),
 (25, '1969-07-24 16:21:12', 'INFO', 'CM/SM separation'),
 (24, '1969-07-22 04:55:42', 'WARNING', 'Transearth injection ignition (SPS)'),
 (23, '1969-07-21 21:35:00', 'INFO', 'CSM/LM docked'),
 (22, '1969-07-21 17:54:00', 'WARNING', 'LM lunar liftoff ignition (LM APS)'),
 (21, '1969-07-21 05:11:13', 'DEBUG', 'EVA ended (hatch closed)'),
 (20, '1969-07-21 03:15:16', 'INFO', 'LMP on lunar surface'),
 (19, '1969-07-21 03:05:58', 'DEBUG', 'Contingency sample collection started (CDR)'),
 (18, '1969-07-21 02:56:16', 'WARNING', 'Neil Armstrong first words on the Moon'),
 (17, '1969-07-21 02:56:15', 'WARNING', '1st step taken lunar surface (CDR)'),
 (16, '1969-07-21 02:39:33', 'DEBUG', 'EVA started (hatch open)'),
 (15, '1969-07-20 20:17:39', 'WARNING', 'LM lunar landing'),
 (14, '1969-07-20 20:14:18', 'ERROR', 'LM 1201 alarm'),
 (13, '1969-07-20 20:10:22', 'ERROR', 'LM 1202 alarm'),
 (12, '1969-07-20 20:05:05', 'WARNING', 'LM powered descent engine ignition'),
 (11, '1969-07-20 17:44:00', 'INFO', 'CSM/LM undocked'),
 (10, '1969-07-16 21:43:36', 'INFO', 'Lunar orbit circularization ignition'),
 (9, '1969-07-16 17:21:50', 'INFO', 'Lunar orbit insertion ignition'),
 (8, '1969-07-16 16:56:03', 'INFO', 'CSM docked with LM/S-IVB'),
 (7, '1969-07-16 16:22:13', 'INFO', 'Translunar injection'),
 (6, '1969-07-16 13:39:40', 'DEBUG', 'S-II center engine cutoff'),
 (5, '1969-07-16 13:35:17', 'DEBUG', 'Launch escape tower jettisoned'),
 (4, '1969-07-16 13:34:44', 'WARNING', 'S-II ignition'),
 (3, '1969-07-16 13:33:23', 'DEBUG', 'Maximum dynamic pressure (735.17 lb/ft^2)'),
 (2, '1969-07-16 13:31:53', 'WARNING', 'S-IC engine ignition (#5)'),
 (1, '1969-07-14 21:00:00', 'INFO', 'Terminal countdown started')]
"""

import sqlite3
from datetime import date, datetime, time

DATABASE = ':memory:'

DATA = [
    ('1969-07-14 21:00:00', 'INFO', 'Terminal countdown started'),
    ('1969-07-16 13:31:53', 'WARNING', 'S-IC engine ignition (#5)'),
    ('1969-07-16 13:33:23', 'DEBUG', 'Maximum dynamic pressure (735.17 lb/ft^2)'),
    ('1969-07-16 13:34:44', 'WARNING', 'S-II ignition'),
    ('1969-07-16 13:35:17', 'DEBUG', 'Launch escape tower jettisoned'),
    ('1969-07-16 13:39:40', 'DEBUG', 'S-II center engine cutoff'),
    ('1969-07-16 16:22:13', 'INFO', 'Translunar injection'),
    ('1969-07-16 16:56:03', 'INFO', 'CSM docked with LM/S-IVB'),
    ('1969-07-16 17:21:50', 'INFO', 'Lunar orbit insertion ignition'),
    ('1969-07-16 21:43:36', 'INFO', 'Lunar orbit circularization ignition'),
    ('1969-07-20 17:44:00', 'INFO', 'CSM/LM undocked'),
    ('1969-07-20 20:05:05', 'WARNING', 'LM powered descent engine ignition'),
    ('1969-07-20 20:10:22', 'ERROR', 'LM 1202 alarm'),
    ('1969-07-20 20:14:18', 'ERROR', 'LM 1201 alarm'),
    ('1969-07-20 20:17:39', 'WARNING', 'LM lunar landing'),
    ('1969-07-21 02:39:33', 'DEBUG', 'EVA started (hatch open)'),
    ('1969-07-21 02:56:15', 'WARNING', '1st step taken lunar surface (CDR)'),
    ('1969-07-21 02:56:16', 'WARNING', 'Neil Armstrong first words on the Moon'),
    ('1969-07-21 03:05:58', 'DEBUG', 'Contingency sample collection started (CDR)'),
    ('1969-07-21 03:15:16', 'INFO', 'LMP on lunar surface'),
    ('1969-07-21 05:11:13', 'DEBUG', 'EVA ended (hatch closed)'),
    ('1969-07-21 17:54:00', 'WARNING', 'LM lunar liftoff ignition (LM APS)'),
    ('1969-07-21 21:35:00', 'INFO', 'CSM/LM docked'),
    ('1969-07-22 04:55:42', 'WARNING', 'Transearth injection ignition (SPS)'),
    ('1969-07-24 16:21:12', 'INFO', 'CM/SM separation'),
    ('1969-07-24 16:35:05', 'WARNING', 'Entry'),
    ('1969-07-24 16:50:35', 'WARNING', 'Splashdown (went to apex-down)'),
    ('1969-07-24 17:29:00', 'INFO', 'Crew egress'),
]


SQL_CREATE_TABLE = """
    CREATE TABLE IF NOT EXISTS logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        datetime DATETIME,
        level TEXT,
        message TEXT);"""

SQL_INSERT = 'INSERT INTO logs VALUES (NULL, ?, ?, ?);'
SQL_SELECT = 'SELECT * FROM logs ORDER BY datetime DESC;'

# Select all results from database in list[tuple] format
# Example [(28, '1969-07-24 17:29:00', 'INFO', 'Crew egress'), ...]
# type: list[tuple]
result = ...

# %% 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

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: SQLite3 Fetch Logs
# - Difficulty: easy
# - Lines: 11
# - Minutes: 13

# %% English
# 1. Split `DATA` by lines:
#    - Strip line from whitespace at the begining and at the end
#    - Extract date, time, log level and message from each line
#    - Parse date and time as date and time objects
#    - Combine date and time into datetime object
#    - Define datetime, level and message as tuple
#    - Add that tuple to `data: list[tuple]`
# 2. Connect to database:
#    - Execute `SQL_CREATE_TABLE` to create database table
#    - Execute `SQL_INSERT` to insert logs to in `list[tuple]` format
#    - Execute `SQL_SELECT` to select data
#    - Iterate over rows and append each to `result: list[tuple]`
# 3. Run doctests - all must succeed

# %% Polish
# 1. Podziel `DATA` po liniach:
#    - Oczyść linię z białych znaków na początku i na końcu
#    - Wyciągnij datę, czas, poziom logowania i teść z każdej linii
#    - Rozczytaj datę i czas jako obiekty date and time
#    - Połącz datę i czas w obiekt datetime
#    - Zdefiniuj datetime, level i message jako tuplę
#    - Dodaj tą tuplę do `data: list[tuple]`
# 2. Połącz się do bazy danych:
#    - Wykonaj `SQL_CREATE_TABLE` aby stworzyć tabelę w bazie danych
#    - Wykonaj `SQL_INSERT` aby wstawić logi w formacie `list[tuple]`
#    - Wykonaj `SQL_SELECT` aby wybrać dane
#    - Iterując po wierszach dopisuj je do `result: list[tuple]`
# 3. Uruchom doctesty - wszystkie muszą się powieść

# %% References
# [1] National Aeronautics and Space Administration.
#     Apollo 11 timeline.
#     Year: 1969. Retrieved: 2021-03-25.
#     URL: https://history.nasa.gov/SP-4029/Apollo_11i_Timeline.htm

# %% Hints
# - `datetime.fromisoformat(str)`
# - `datetime.combine(date, time)`
# - `datetime.strptime(str, format)`

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> assert sys.version_info >= (3, 9), \
'Python 3.9+ required'

>>> assert type(result) is not Ellipsis
>>> assert type(result) is list
>>> assert len(result) > 0
>>> assert all(type(row) is tuple for row in result)

>>> result  # doctest: +NORMALIZE_WHITESPACE
[(28, '1969-07-24 17:29:00', 'INFO', 'Crew egress'),
 (27, '1969-07-24 16:50:35', 'WARNING', 'Splashdown (went to apex-down)'),
 (26, '1969-07-24 16:35:05', 'WARNING', 'Entry'),
 (25, '1969-07-24 16:21:12', 'INFO', 'CM/SM separation'),
 (24, '1969-07-22 04:55:42', 'WARNING', 'Transearth injection ignition (SPS)'),
 (23, '1969-07-21 21:35:00', 'INFO', 'CSM/LM docked'),
 (22, '1969-07-21 17:54:00', 'WARNING', 'LM lunar liftoff ignition (LM APS)'),
 (21, '1969-07-21 05:11:13', 'DEBUG', 'EVA ended (hatch closed)'),
 (20, '1969-07-21 03:15:16', 'INFO', 'LMP on lunar surface'),
 (19, '1969-07-21 03:05:58', 'DEBUG', 'Contingency sample collection started (CDR)'),
 (18, '1969-07-21 02:56:16', 'WARNING', 'Neil Armstrong first words on the Moon'),
 (17, '1969-07-21 02:56:15', 'WARNING', '1st step taken lunar surface (CDR)'),
 (16, '1969-07-21 02:39:33', 'DEBUG', 'EVA started (hatch open)'),
 (15, '1969-07-20 20:17:39', 'WARNING', 'LM lunar landing'),
 (14, '1969-07-20 20:14:18', 'ERROR', 'LM 1201 alarm'),
 (13, '1969-07-20 20:10:22', 'ERROR', 'LM 1202 alarm'),
 (12, '1969-07-20 20:05:05', 'WARNING', 'LM powered descent engine ignition'),
 (11, '1969-07-20 17:44:00', 'INFO', 'CSM/LM undocked'),
 (10, '1969-07-16 21:43:36', 'INFO', 'Lunar orbit circularization ignition'),
 (9, '1969-07-16 17:21:50', 'INFO', 'Lunar orbit insertion ignition'),
 (8, '1969-07-16 16:56:03', 'INFO', 'CSM docked with LM/S-IVB'),
 (7, '1969-07-16 16:22:13', 'INFO', 'Translunar injection'),
 (6, '1969-07-16 13:39:40', 'DEBUG', 'S-II center engine cutoff'),
 (5, '1969-07-16 13:35:17', 'DEBUG', 'Launch escape tower jettisoned'),
 (4, '1969-07-16 13:34:44', 'WARNING', 'S-II ignition'),
 (3, '1969-07-16 13:33:23', 'DEBUG', 'Maximum dynamic pressure (735.17 lb/ft^2)'),
 (2, '1969-07-16 13:31:53', 'WARNING', 'S-IC engine ignition (#5)'),
 (1, '1969-07-14 21:00:00', 'INFO', 'Terminal countdown started')]
"""

import sqlite3
from datetime import date, datetime, time

DATABASE = ':memory:'

DATA = """1969-07-14, 21:00:00, INFO, Terminal countdown started
1969-07-16, 13:31:53, WARNING, S-IC engine ignition (#5)
1969-07-16, 13:33:23, DEBUG, Maximum dynamic pressure (735.17 lb/ft^2)
1969-07-16, 13:34:44, WARNING, S-II ignition
1969-07-16, 13:35:17, DEBUG, Launch escape tower jettisoned
1969-07-16, 13:39:40, DEBUG, S-II center engine cutoff
1969-07-16, 16:22:13, INFO, Translunar injection
1969-07-16, 16:56:03, INFO, CSM docked with LM/S-IVB
1969-07-16, 17:21:50, INFO, Lunar orbit insertion ignition
1969-07-16, 21:43:36, INFO, Lunar orbit circularization ignition
1969-07-20, 17:44:00, INFO, CSM/LM undocked
1969-07-20, 20:05:05, WARNING, LM powered descent engine ignition
1969-07-20, 20:10:22, ERROR, LM 1202 alarm
1969-07-20, 20:14:18, ERROR, LM 1201 alarm
1969-07-20, 20:17:39, WARNING, LM lunar landing
1969-07-21, 02:39:33, DEBUG, EVA started (hatch open)
1969-07-21, 02:56:15, WARNING, 1st step taken lunar surface (CDR)
1969-07-21, 02:56:16, WARNING, Neil Armstrong first words on the Moon
1969-07-21, 03:05:58, DEBUG, Contingency sample collection started (CDR)
1969-07-21, 03:15:16, INFO, LMP on lunar surface
1969-07-21, 05:11:13, DEBUG, EVA ended (hatch closed)
1969-07-21, 17:54:00, WARNING, LM lunar liftoff ignition (LM APS)
1969-07-21, 21:35:00, INFO, CSM/LM docked
1969-07-22, 04:55:42, WARNING, Transearth injection ignition (SPS)
1969-07-24, 16:21:12, INFO, CM/SM separation
1969-07-24, 16:35:05, WARNING, Entry
1969-07-24, 16:50:35, WARNING, Splashdown (went to apex-down)
1969-07-24, 17:29, INFO, Crew egress"""


SQL_CREATE_TABLE = """
    CREATE TABLE IF NOT EXISTS logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        datetime DATETIME,
        level TEXT,
        message TEXT);"""

SQL_INSERT = 'INSERT INTO logs VALUES (NULL, ?, ?, ?);'
SQL_SELECT = 'SELECT * FROM logs ORDER BY datetime DESC;'

# Select all results from database in list[tuple] format,
# Example [(28, '1969-07-24 17:29:00', 'INFO', 'Crew egress'), ...]
# type: list[tuple]
result = ...

# %% 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

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: SQLite3 Fetch CSV
# - Difficulty: easy
# - Lines: 15
# - Minutes: 13

# %% English
# 1. Read data from `FILE` (don't use `csv` or `pandas` library)
# 2. Replace species from `int` to `str` according to `SPECIES`
#    conversion table
# 3. Connect to the `sqlite3` using context manager (`with`)
# 4. Create table `iris` and write data to it
# 5. Select data and add them to `result: list[dict]`
# 6. Run doctests - all must succeed

# %% Polish
# 1. Wczytaj dane z `FILE` (nie używaj biblioteki `csv` lub `pandas`)
# 2. Podmień gatunki z `int` na `str` zgodnie z tabelą podstawień `SPECIES`
# 3. Połącz się do bazy danych `sqlite3` używając context managera (`with`)
# 4. Stwórz tabelę `iris` i zapisz do niej dane
# 5. Wybierz dane i dodaj je do `result: list[dict]`
# 6. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> assert sys.version_info >= (3, 9), \
'Python 3.9+ required'

>>> 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)

>>> result  # doctest: +NORMALIZE_WHITESPACE
[{'id': 4, 'species': 'virginica', 'sepal_length': 5.4, 'sepal_width': 3.9, 'petal_length': 1.3, 'petal_width': 0.4},
 {'id': 3, 'species': 'versicolor', 'sepal_length': 5.7, 'sepal_width': 4.4, 'petal_length': 1.5, 'petal_width': 0.4},
 {'id': 5, 'species': 'versicolor', 'sepal_length': 5.1, 'sepal_width': 3.5, 'petal_length': 1.4, 'petal_width': 0.3},
 {'id': 8, 'species': 'versicolor', 'sepal_length': 5.4, 'sepal_width': 3.4, 'petal_length': 1.7, 'petal_width': 0.2},
 {'id': 1, 'species': 'setosa', 'sepal_length': 4.3, 'sepal_width': 3.0, 'petal_length': 1.1, 'petal_width': 0.1},
 {'id': 2, 'species': 'setosa', 'sepal_length': 5.8, 'sepal_width': 4.0, 'petal_length': 1.2, 'petal_width': 0.2},
 {'id': 6, 'species': 'setosa', 'sepal_length': 5.7, 'sepal_width': 3.8, 'petal_length': 1.7, 'petal_width': 0.3},
 {'id': 7, 'species': 'setosa', 'sepal_length': 5.1, 'sepal_width': 3.8, 'petal_length': 1.5, 'petal_width': 0.3},
 {'id': 9, 'species': 'setosa', 'sepal_length': 5.1, 'sepal_width': 3.7, 'petal_length': 1.5, 'petal_width': 0.4},
 {'id': 10, 'species': 'setosa', 'sepal_length': 4.6, 'sepal_width': 3.6, 'petal_length': 1.0, 'petal_width': 0.2}]

>>> from pathlib import Path
>>> Path(FILE).unlink(missing_ok=True)
"""

import sqlite3

DATABASE = ':memory:'
FILE = '_temporary.csv'

SPECIES = {
    0: 'setosa',
    1: 'versicolor',
    2: 'virginica'}

DATA = """4.3,3.0,1.1,0.1,0
5.8,4.0,1.2,0.2,0
5.7,4.4,1.5,0.4,1
5.4,3.9,1.3,0.4,2
5.1,3.5,1.4,0.3,1
5.7,3.8,1.7,0.3,0
5.1,3.8,1.5,0.3,0
5.4,3.4,1.7,0.2,1
5.1,3.7,1.5,0.4,0
4.6,3.6,1.0,0.2,0"""

SQL_CREATE_TABLE = """
    CREATE TABLE IF NOT EXISTS iris (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        species TEXT,
        sepal_length REAL,
        sepal_width REAL,
        petal_length REAL,
        petal_width REAL);"""

SQL_INSERT = """
    INSERT INTO iris VALUES (
        NULL,
        :species,
        :sepal_length,
        :sepal_width,
        :petal_length,
        :petal_width);"""

SQL_SELECT = """
    SELECT *
    FROM iris
    ORDER BY species DESC, id ASC;"""

with open(FILE, mode='w') as file:
    file.write(DATA)


# Select all results from database in list[dict] format
# Example [{'id': 4,
#           'species': 'virginica',
#           'sepal_length': 5.4,
#           'sepal_width': 3.9,
#           'petal_length': 1.3,
#           'petal_width': 0.4}, ...]
# type: list[tuple]
result = ...