6.1. SQL About

SQL

Structured Query Language

Domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables. [1] [2]

SELECT

SQL language operation to retrieve data from the database

INSERT

SQL language operation to put data to the database

UPDATE

SQL language operation to modify data in the database

JOIN

SQL language operation to retrieve data from the database from multiple tables and merge them

6.1.1. References

[1]

SQL. Wikipedia. Year: 2021. Retrieved: 2021-12-16. URL: https://en.wikipedia.org/wiki/SQL

[2]

Shafranovich, Y. The application/sql Media Type. Internet Engineering Task Force (IETF). Retrieved: 2021-12-16. Year: 2013. URL: https://datatracker.ietf.org/doc/html/rfc6922

6.1.2. 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: Database Connection Test
# - Difficulty: easy
# - Lines: 0
# - Minutes: 2

# %% English
# 1. Run file to download and check database file
# 2. Run doctests - all must succeed

# %% Polish
# 1. Uruchom plik aby ściągnąć i sprawdzić plik bazy danych
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> import sqlite3

>>> assert database.exists(), \
'Error downloading database file'

>>> assert database.stat().st_size > 0, \
'Database did not download properly'

>>> db = sqlite3.connect(database)

>>> TABLES = 'SELECT `name` FROM `sqlite_master` WHERE `type`="table"'
>>> tables = {row[0] for row in db.execute(TABLES)}
>>> assert 'users' in tables
>>> assert 'products' in tables
>>> assert 'orders' in tables
>>> assert 'addresses' in tables

>>> INDEXES = 'SELECT `name` FROM `sqlite_master` WHERE `type`="index"'
>>> indexes = {row[0] for row in db.execute(INDEXES)}
>>> assert 'users_lastname_firstname' in indexes
>>> assert 'products_ean13' in indexes
>>> assert 'products_name' in indexes
>>> assert 'orders_user' in indexes
>>> assert 'addresses_country' in indexes

>>> USERS = 'SELECT COUNT(*) FROM `users`'
>>> users_count = db.execute(USERS).fetchone()[0]
>>> assert users_count == 6

>>> PRODUCTS = 'SELECT COUNT(*) FROM `products`'
>>> products_count = db.execute(PRODUCTS).fetchone()[0]
>>> assert products_count == 25

>>> ORDERS = 'SELECT COUNT(*) FROM `orders`'
>>> orders_count = db.execute(ORDERS).fetchone()[0]
>>> assert orders_count == 33

>>> ADDRESSES = 'SELECT COUNT(*) FROM `addresses`'
>>> addresses_count = db.execute(ADDRESSES).fetchone()[0]
>>> assert addresses_count == 8

>>> db.close()
"""

from urllib.request import urlopen
from pathlib import Path


DATA = 'https://python3.info/_static/shop.db'
database = Path(__file__).parent.parent / 'shop.db'

with urlopen(DATA) as url:
    content = url.read()
    database.write_bytes(content)