4.2. Database SQLite

4.2.1. Installation

  1. To check if Sqlite3 is already installed run in Console/Terminal/CMD:

    $ sqlite3 --version
    
  2. If it's not installed, then:

    • Download from https://sqlite.org/download.html

    • Extract archive

    • Add sqlite executable to $PATH:

      • *nix systems (Linux, macOS, etc...): move sqlite executable to /usr/local/bin/

      • Windows (better): Add folder with extracted sqlite executable to $PATH directory by following instruction (https://python.astrotech.io/install.html)

      • Windows (simple): If you have GIT installed, move sqlite executable to C:\Program Files\Git\cmd

    • Check if Sqlite3 is installed (Point 1.)

4.2.2. SQL Syntax

  • More information in Database SQL

4.2.3. Data Types

Table 4.1. SQLite basic data types

SQLite Type

Python Type

Description

NULL

None

The value is a undefined value

INTEGER

int

The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value

REAL

float

The value is a floating point value, stored as an 8-byte IEEE floating point number

TEXT

str

The value is a text string, stored using the database encoding (ie. UTF-8)

BLOB

bytes

The value is a blob of data, stored exactly as it was input

Table 4.2. SQLite extra data types

SQLite Type

SQLite Alias

INTEGER

INT

INTEGER

INTEGER

INTEGER

TINYINT

INTEGER

SMALLINT

INTEGER

MEDIUMINT

INTEGER

BIGINT

INTEGER

UNSIGNED BIG INT

INTEGER

INT2

INTEGER

INT8

TEXT

CHARACTER(20)

TEXT

VARCHAR(255)

TEXT

VARYING CHARACTER(255)

TEXT

NCHAR(55)

TEXT

NATIVE CHARACTER(70)

TEXT

NVARCHAR(100)

TEXT

TEXT

TEXT

CLOB

REAL

REAL

REAL

DOUBLE

REAL

DOUBLE PRECISION

REAL

FLOAT

NUMERIC

NUMERIC

NUMERIC

DECIMAL(10,5)

NUMERIC

BOOLEAN

NUMERIC

DATE

NUMERIC

DATETIME

4.2.4. Numeric

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively. If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL.

4.2.5. Datetime

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

  • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the Gregorian calendar.

  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

4.2.6. Constrains

Table 4.3. SQL Constraints

Constraint

Description

NOT NULL

Ensures that a column cannot have a NULL value

UNIQUE

Ensures that all values in a column are different

PRIMARY KEY

A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

FOREIGN KEY

Uniquely identifies a row/record in another table

CHECK

Ensures that all values in a column satisfies a specific condition

DEFAULT

Sets a default value for a column when no value is specified

INDEX

Used to create and retrieve data from the database very quickly

4.2.7. DB API v2

sqlite3.connect(...) -> connection

connection.execute(...) -> result
connection.executemany(...) -> list[result]
connection.fetchmany(...) -> list[result]
connection.fetchone(...) -> result
connection.cursor(...) -> cursor
connection.commit(...)
connection.close()

4.2.8. Connection

Connection to in-memory database:

import sqlite3

DATABASE = ':memory:'

with sqlite3.connect(DATABASE) as db:
    ...

Connection to database file:

import sqlite3

DATABASE = r'/tmp/database.sqlite3'

with sqlite3.connect(DATABASE) as db:
    ...

4.2.9. Execute

import sqlite3


DATABASE = ':memory:'

SQL_CREATE_TABLE = """
    CREATE TABLE IF NOT EXISTS astronauts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        pesel INTEGER UNIQUE,
        firstname TEXT,
        lastname TEXT)"""

SQL_INSERT = 'INSERT INTO astronauts VALUES (NULL, :pesel, :firstname, :lastname)'

DATA = {'pesel': '61041212345',
        'firstname': 'Mark',
        'lastname': 'Watney'}


with sqlite3.connect(DATABASE) as db:
    db.execute(SQL_CREATE_TABLE)

4.2.10. Executemany list[tuple]

Code 4.1. Execute many
import sqlite3


DATABASE = ':memory:'
DATA = [(61041212345, 'José', 'Jiménez'),
        (61041212346, 'Jan', 'Twardowski'),
        (61041212347, 'Melissa', 'Lewis'),
        (61041212348, 'Alex', 'Vogel'),
        (61041212349, 'Ryan', 'Stone')]

SQL_CREATE_TABLE = """
    CREATE TABLE IF NOT EXISTS astronauts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        pesel INTEGER UNIQUE,
        firstname TEXT,
        lastname TEXT)"""

SQL_INSERT = """
    INSERT INTO astronauts
    VALUES (NULL, ?, ?, ?)"""


with sqlite3.connect(DATABASE) as db:
    db.execute(SQL_CREATE_TABLE)

    try:
        db.executemany(SQL_INSERT, DATA)
    except sqlite3.IntegrityError:
        print('Pesel need to be UNIQUE')

4.2.11. Execute many list[dict]

Code 4.2. Execute many
import sqlite3


DATABASE = ':memory:'
DATA = [{'pesel': '61041212345', 'firstname': 'José', 'lastname': 'Jiménez'},
        {'pesel': '61041212346', 'firstname': 'Jan', 'lastname': 'Twardowski'},
        {'pesel': '61041212347', 'firstname': 'Melissa', 'lastname': 'Lewis'},
        {'pesel': '61041212348', 'firstname': 'Alex', 'lastname': 'Vogel'},
        {'pesel': '61041212349', 'firstname': 'Ryan', 'lastname': 'Stone'}]

SQL_CREATE_TABLE = """
    CREATE TABLE IF NOT EXISTS astronauts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        pesel INTEGER UNIQUE,
        firstname TEXT,
        lastname TEXT)"""

SQL_INSERT = """
    INSERT INTO astronauts
    VALUES (NULL, :pesel, :firstname, :lastname)"""


with sqlite3.connect(DATABASE) as db:
    db.execute(SQL_CREATE_TABLE)

    try:
        db.executemany(SQL_INSERT, DATA)
    except sqlite3.IntegrityError:
        print('Pesel need to be UNIQUE')

4.2.12. Fetch as list[tuple]

Code 4.3. Results
import sqlite3

DATABASE = ':memory:'
DATA = [{'pesel': '61041212345', 'firstname': 'José', 'lastname': 'Jiménez'},
        {'pesel': '61041212346', 'firstname': 'Jan', 'lastname': 'Twardowski'},
        {'pesel': '61041212347', 'firstname': 'Melissa', 'lastname': 'Lewis'},
        {'pesel': '61041212348', 'firstname': 'Alex', 'lastname': 'Vogel'},
        {'pesel': '61041212349', 'firstname': 'Ryan', 'lastname': 'Stone'}]

SQL_CREATE_TABLE = """
    CREATE TABLE IF NOT EXISTS astronauts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        pesel INTEGER UNIQUE,
        firstname TEXT,
        lastname TEXT)"""

SQL_INSERT = """
    INSERT INTO astronauts
    VALUES (NULL, :pesel, :firstname, :lastname)"""

SQL_SELECT = 'SELECT * FROM astronauts'


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, 61041212345, 'José', 'Jiménez')
# (2, 61041212346, 'Jan', 'Twardowski')
# (3, 61041212347, 'Melissa', 'Lewis')
# (4, 61041212348, 'Alex', 'Vogel')
# (5, 61041212349, 'Ryan', 'Stone')

4.2.13. Fetch as list[dict]

Code 4.4. Results with dict
import sqlite3

DATABASE = ':memory:'
DATA = [{'pesel': '61041212345', 'firstname': 'José', 'lastname': 'Jiménez'},
        {'pesel': '61041212346', 'firstname': 'Jan', 'lastname': 'Twardowski'},
        {'pesel': '61041212347', 'firstname': 'Melissa', 'lastname': 'Lewis'},
        {'pesel': '61041212348', 'firstname': 'Alex', 'lastname': 'Vogel'},
        {'pesel': '61041212349', 'firstname': 'Ryan', 'lastname': 'Stone'}]

SQL_CREATE_TABLE = """
    CREATE TABLE IF NOT EXISTS astronauts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        pesel INTEGER UNIQUE,
        firstname TEXT,
        lastname TEXT)"""

SQL_INSERT = """
    INSERT INTO astronauts
    VALUES (NULL, :pesel, :firstname, :lastname)"""

SQL_SELECT = 'SELECT * FROM astronauts'


with sqlite3.connect(DATABASE) as db:
    db.execute(SQL_CREATE_TABLE)
    db.executemany(SQL_INSERT, DATA)

    db.row_factory = sqlite3.Row

    for row in db.execute(SQL_SELECT):
        print(dict(row))

# {'id': 1, 'pesel': 61041212345, 'firstname': 'José', 'lastname': 'Jiménez'}
# {'id': 2, 'pesel': 61041212346, 'firstname': 'Jan', 'lastname': 'Twardowski'}
# {'id': 3, 'pesel': 61041212347, 'firstname': 'Melissa', 'lastname': 'Lewis'}
# {'id': 4, 'pesel': 61041212348, 'firstname': 'Alex', 'lastname': 'Vogel'}
# {'id': 5, 'pesel': 61041212349, 'firstname': 'Ryan', 'lastname': 'Stone'}

4.2.14. Cursor

Code 4.5. Results with cursor
import sqlite3


DATABASE = ':memory:'
DATA = [{'pesel': '61041212345', 'firstname': 'José', 'lastname': 'Jiménez'},
        {'pesel': '61041212346', 'firstname': 'Jan', 'lastname': 'Twardowski'},
        {'pesel': '61041212347', 'firstname': 'Melissa', 'lastname': 'Lewis'},
        {'pesel': '61041212348', 'firstname': 'Alex', 'lastname': 'Vogel'},
        {'pesel': '61041212349', 'firstname': 'Ryan', 'lastname': 'Stone'}]

SQL_CREATE_TABLE = """
    CREATE TABLE IF NOT EXISTS astronauts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        pesel INTEGER UNIQUE,
        firstname TEXT,
        lastname TEXT)"""

SQL_INSERT = """
    INSERT INTO astronauts
    VALUES (NULL, :pesel, :firstname, :lastname)"""

SQL_SELECT = 'SELECT * FROM astronauts'


with sqlite3.connect(DATABASE) as db:
    db.execute(SQL_CREATE_TABLE)
    db.executemany(SQL_INSERT, DATA)
    cursor = db.cursor()

    for row in cursor.execute(SQL_SELECT):
        print(row)

# (1, 61041212345, 'José', 'Jiménez')
# (2, 61041212346, 'Jan', 'Twardowski')
# (3, 61041212347, 'Melissa', 'Lewis')
# (4, 61041212348, 'Alex', 'Vogel')
# (5, 61041212349, 'Ryan', 'Stone')

4.2.15. Use cases

import json
from datetime import datetime, timezone
import logging
import sqlite3
import serial


logging.basicConfig(
    format='[%(asctime).19s] %(levelname)s %(message)s',
    level=logging.INFO)

DATABASE = '/home/pi/database/sensor-data.sqlite3'
DEVICE = '/dev/ttyACM0'
UNITS = {
    'air_temperature': 'C',
    'air_humidity': '%',
    'water_temperature': 'C',
    'luminosity': 'lux',
    'power_k1': 'on/off',
    'power_k2': 'on/off',
    'power_k3': 'on/off',
    'power_k4': 'on/off'}

SQL_CREATE_TABLE = """
    CREATE TABLE IF NOT EXISTS sensor_data (
        datetime DATETIME PRIMARY KEY,
        sync_datetime DATETIME DEFAULT NULL,
        device VARCHAR(255),
        parameter VARCHAR(255),
        value REAL,
        unit VARCHAR(255));"""

SQL_CREATE_INDEX_SYNCDATETIME = """
    CREATE INDEX IF NOT EXISTS sensor_data_sync_datetime_index
    ON sensor_data (sync_datetime);"""

SQL_CREATE_INDEX_DATETIME = """
    CREATE UNIQUE INDEX IF NOT EXISTS sensor_data_datetime_index
    ON sensor_data (datetime);"""

SQL_INSERT = """
    INSERT INTO sensor_data
    VALUES (:datetime, NULL, :device, :parameter, :value, :unit);"""


with sqlite3.connect(DATABASE) as db:
    db.execute(SQL_CREATE_TABLE)
    db.execute(SQL_CREATE_INDEX_DATETIME)
    db.execute(SQL_CREATE_INDEX_SYNCDATETIME)


def save_to_sqlite3(data):
    data = [{'datetime': datetime.now(timezone.utc),
             'parameter': parameter,
             'value': float(value),
             'unit': UNITS.get(parameter, None),
             'device': 'hydroponics'}
            for parameter, value in data.items()]

    with sqlite3.connect(DATABASE) as db:
        db.executemany(SQL_INSERT, data)


with serial.Serial(port=DEVICE, baudrate=115200) as usb:
    while True:
        line = usb.readline()
        try:
            data = json.loads(line)
            save_to_sqlite3(data)
            logging.info(data)
        except json.decoder.JSONDecodeError:
            logging.error(line)

4.2.16. Assignments

Code 4.6. Solution
"""
* Assignment: Database SQLite Logs
* Complexity: easy
* Lines of code: 17 lines
* Time: 21 min

English:
    1. Use data from "Given" section (see below)
    2. Extract date, time, log level and message from each line in `FILE`
    3. Collect data to `data: list[dict]` (see below)
    4. Create database schema for logs
    5. Add all logs to database
    6. Use `SQL_SELECT` query to extract data
    7. Iterate over rows and append each to `result: list[dict]`
    8. Compare result with "Tests" section (see below)

Polish:
    1. Użyj danych z sekcji "Given" (patrz poniżej)
    2. Wyciągnij datę, czas, poziom logowania i teść z każdej linii w `FILE`
    3. Zbierz dane do `data: list[dict]` (patrz sekcja input)
    4. Stwórz schemat bazy danych dla logów
    5. Dodaj wszystkie linie do bazy danych
    6. Użyj zapytania `SQL_SELECT` do wyciągnięcia danych
    7. Iterując po wierszach dopisuj je do `result: list[dict]`
    8. Porównaj wyniki z sekcją "Tests" (patrz poniżej)

References:
    * Apollo 11 timeline https://history.nasa.gov/SP-4029/Apollo_11i_Timeline.htm

Hints:
    * `datetime.fromisoformat()`

Tests:
    >>> type(result)
    <class 'list'>
    >>> len(result) > 0
    True
    >>> all(type(row) is dict
    ...     for row in result)
    True
    >>> result  # doctest: +NORMALIZE_WHITESPACE
    [{'id': 28, 'datetime': '1969-07-24 17:29:00', 'level': 'INFO', 'message': 'Crew egress'},
     {'id': 27, 'datetime': '1969-07-24 16:50:35', 'level': 'WARNING', 'message': 'Splashdown (went to apex-down)'},
     {'id': 26, 'datetime': '1969-07-24 16:35:05', 'level': 'WARNING', 'message': 'Entry'},
     {'id': 25, 'datetime': '1969-07-24 16:21:12', 'level': 'INFO', 'message': 'CM/SM separation'},
     {'id': 24, 'datetime': '1969-07-22 04:55:42', 'level': 'WARNING', 'message': 'Transearth injection ignition (SPS)'},
     {'id': 23, 'datetime': '1969-07-21 21:35:00', 'level': 'INFO', 'message': 'CSM/LM docked'},
     {'id': 22, 'datetime': '1969-07-21 17:54:00', 'level': 'WARNING', 'message': 'LM lunar liftoff ignition (LM APS)'},
     {'id': 21, 'datetime': '1969-07-21 05:11:13', 'level': 'DEBUG', 'message': 'EVA ended (hatch closed)'},
     {'id': 20, 'datetime': '1969-07-21 03:15:16', 'level': 'INFO', 'message': 'LMP on lunar surface'},
     {'id': 19, 'datetime': '1969-07-21 03:05:58', 'level': 'DEBUG', 'message': 'Contingency sample collection started (CDR)'},
     {'id': 18, 'datetime': '1969-07-21 02:56:15', 'level': 'WARNING', 'message': "That's one small step for [a] man... one giant leap for mankind"},
     {'id': 17, 'datetime': '1969-07-21 02:56:15', 'level': 'WARNING', 'message': '1st step taken lunar surface (CDR)'},
     {'id': 16, 'datetime': '1969-07-21 02:39:33', 'level': 'DEBUG', 'message': 'EVA started (hatch open)'},
     {'id': 15, 'datetime': '1969-07-20 20:17:39', 'level': 'WARNING', 'message': 'LM lunar landing'},
     {'id': 14, 'datetime': '1969-07-20 20:14:18', 'level': 'ERROR', 'message': 'LM 1201 alarm'},
     {'id': 13, 'datetime': '1969-07-20 20:10:22', 'level': 'ERROR', 'message': 'LM 1202 alarm'},
     {'id': 12, 'datetime': '1969-07-20 20:05:05', 'level': 'WARNING', 'message': 'LM powered descent engine ignition'},
     {'id': 11, 'datetime': '1969-07-20 17:44:00', 'level': 'INFO', 'message': 'CSM/LM undocked'},
     {'id': 10, 'datetime': '1969-07-16 21:43:36', 'level': 'INFO', 'message': 'Lunar orbit circularization ignition'},
     {'id': 9, 'datetime': '1969-07-16 17:21:50', 'level': 'INFO', 'message': 'Lunar orbit insertion ignition'},
     {'id': 8, 'datetime': '1969-07-16 16:56:03', 'level': 'INFO', 'message': 'CSM docked with LM/S-IVB'},
     {'id': 7, 'datetime': '1969-07-16 16:22:13', 'level': 'INFO', 'message': 'Translunar injection'},
     {'id': 6, 'datetime': '1969-07-16 13:39:40', 'level': 'DEBUG', 'message': 'S-II center engine cutoff'},
     {'id': 5, 'datetime': '1969-07-16 13:35:17', 'level': 'DEBUG', 'message': 'Launch escape tower jettisoned'},
     {'id': 4, 'datetime': '1969-07-16 13:34:44', 'level': 'WARNING', 'message': 'S-II ignition'},
     {'id': 3, 'datetime': '1969-07-16 13:33:23', 'level': 'DEBUG', 'message': 'Maximum dynamic pressure (735.17 lb/ft^2)'},
     {'id': 2, 'datetime': '1969-07-16 13:31:53', 'level': 'WARNING', 'message': 'S-IC engine ignition (#5)'},
     {'id': 1, 'datetime': '1969-07-14 21:00:00', 'level': 'INFO', 'message': 'Terminal countdown started'}]
    >>> from os import remove
    >>> remove(FILE)
    >>> remove(DATABASE)
"""


# Given
import sqlite3
from datetime import datetime, timezone

DATABASE = r'_temporary.sqlite3'
FILE = r'_temporary.csv'

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:15, WARNING, That's one small step for [a] man... one giant leap for mankind
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_CREATE_INDEX = """
    CREATE INDEX IF NOT EXISTS
        logs_datetime_index ON logs (datetime);"""

SQL_INSERT = """
    INSERT INTO logs VALUES (
        NULL,
        :datetime,
        :level,
        :message);"""

SQL_SELECT = 'SELECT * FROM logs ORDER BY datetime DESC'


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


result: list

Code 4.7. Solution
"""
* Assignment: Database SQLite CSV
* Complexity: easy
* Lines of code: 30 lines
* Time: 21 min

English:
    1. Use data from "Given" section (see below)
    2. Save `DATA` to `FILE`
    3. Read data from `FILE` (don't use `csv` or `pandas` library)
    4. Replace species from `int` to `str` according to `SPECIES` conversion table
    5. Connect to the `sqlite3` using context manager (`with`)
    6. Create table `iris` and write data to it
    7. Print all rows as dicts

Polish:
    1. Użyj danych z sekcji "Given" (patrz poniżej)
    2. Zapisz `DATA` do `FILE`
    3. Wczytaj dane z `FILE` (nie używaj biblioteki `csv` lub `pandas`)
    4. Podmień gatunki z `int` na `str` zgodnie z tabelą podstawień `SPECIES`
    5. Połącz się do bazy danych `sqlite3` używając context managera (`with`)
    6. Stwórz tabelę `iris` i zapisz do niej dane
    7. Wypisz wszystkie wiersze jako słowniki

Tests:
    >>> type(result)
    <class 'list'>
    >>> len(result) > 0
    True
    >>> all(type(row) is dict
    ...     for row in result)
    True
    >>> 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 os import remove
    >>> remove(FILE)
    >>> remove(DATABASE)
"""


# Given
import sqlite3

DATABASE = r'_temporary.sqlite3'
FILE = r'_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_CREATE_INDEX = """
    CREATE INDEX IF NOT EXISTS
        iris_species_index ON iris (species);"""

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

result: list


Code 4.8. Solution
"""
* Assignment: Database SQLite JSON
* Complexity: medium
* Lines of code: 15 lines
* Time: 21 min

English:
    1. Use data from "Given" section (see below)
    2. Create database and two tables `astronaut` and `address`
    3. Insert data to separate tables
    4. Print data joining information from both tables

Polish:
    1. Użyj danych z sekcji "Given" (patrz poniżej)
    2. Stwórz bazę danych i dwie tabele `astronaut` i `address`
    3. Zapisz dane do osobnych tabel
    4. Wypisz dane łącząc informacje z obu tabel

Tests:
    >>> type(result)
    <class 'list'>
    >>> len(result) > 0
    True
    >>> all(type(row) is dict
    ...     for row in result)
    True
    >>> result  # doctest: +NORMALIZE_WHITESPACE
    [{'id': 1, 'firstname': 'José', 'lastname': 'Jiménez', 'astronaut_id': 1, 'street': '2101 E NASA Pkwy', 'city': 'Houston', 'state': 'Texas', 'code': 77058, 'country': 'USA'},
     {'id': 1, 'firstname': 'José', 'lastname': 'Jiménez', 'astronaut_id': 1, 'street': None, 'city': 'Kennedy Space Center', 'state': 'Florida', 'code': 32899, 'country': 'USA'},
     {'id': 2, 'firstname': 'Mark', 'lastname': 'Watney', 'astronaut_id': 2, 'street': '4800 Oak Grove Dr', 'city': 'Pasadena', 'state': 'California', 'code': 91109, 'country': 'USA'},
     {'id': 2, 'firstname': 'Mark', 'lastname': 'Watney', 'astronaut_id': 2, 'street': '2825 E Ave P', 'city': 'Palmdale', 'state': 'California', 'code': 93550, 'country': 'USA'},
     {'id': 3, 'firstname': 'Иван', 'lastname': 'Иванович', 'astronaut_id': 3, 'street': '', 'city': 'Космодро́м Байкону́р', 'state': 'Кызылординская область', 'code': None, 'country': 'Қазақстан'},
     {'id': 5, 'firstname': 'Alex', 'lastname': 'Vogel', 'astronaut_id': 5, 'street': 'Linder Hoehe', 'city': 'Köln', 'state': None, 'code': 51147, 'country': 'Germany'}]
    >>> from os import remove
    >>> remove(DATABASE)
"""


# Given
import sqlite3

DATABASE = r'_temporary.sqlite3'

SQL_CREATE_TABLE_ASTRONAUT = """
    CREATE TABLE IF NOT EXISTS astronaut (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        firstname TEXT,
        lastname TEXT);"""

SQL_CREATE_TABLE_ADDRESS = """
    CREATE TABLE IF NOT EXISTS address (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        astronaut_id INTEGER,
        street TEXT,
        city TEXT,
        state TEXT,
        code INT,
        country TEXT);"""

SQL_CREATE_INDEX_ASTRONAUT_LASTNAME = """
    CREATE INDEX IF NOT EXISTS lastname_index ON astronaut (lastname);"""

SQL_INSERT_ASTRONAUT = """
    INSERT INTO astronaut VALUES (
        NULL,
        :firstname,
        :lastname);"""

SQL_INSERT_ADDRESS = """
    INSERT INTO address VALUES (
        NULL,
        :astronaut_id,
        :street,
        :city,
        :state,
        :code,
        :country);"""

SQL_SELECT = """
    SELECT *
    FROM astronaut
    JOIN address
    ON astronaut.id=address.astronaut_id;
"""

DATA = [
    {"firstname": "José", "lastname": "Jiménez", "addresses": [
        {"street": "2101 E NASA Pkwy", "code": 77058, "city": "Houston", "state": "Texas", "country": "USA"},
        {"street": None, "code": 32899, "city": "Kennedy Space Center", "state": "Florida", "country": "USA"}]},

    {"firstname": "Mark", "lastname": "Watney", "addresses": [
        {"street": "4800 Oak Grove Dr", "code": 91109, "city": "Pasadena", "state": "California", "country": "USA"},
        {"street": "2825 E Ave P", "code": 93550, "city": "Palmdale", "state": "California", "country": "USA"}]},

    {"firstname": "Иван", "lastname": "Иванович", "addresses": [
        {"street": "", "code": None, "city": "Космодро́м Байкону́р", "state": "Кызылординская область", "country": "Қазақстан"}]},

    {"firstname": "Melissa", "lastname": "Lewis", "addresses": []},

    {"firstname": "Alex", "lastname": "Vogel", "addresses": [
        {"street": "Linder Hoehe", "city": "Köln", "code": 51147, "state": None, "country": "Germany"}]}
]

result: list