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

6.1.2. Assignments

# %% About
# - Name: Database Connection Test
# - Difficulty: easy
# - Lines: 0
# - Minutes: 2

# %% 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. 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ść

# %% Doctests
"""
>>> 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()
"""

# %% 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
from urllib.request import urlopen
from pathlib import Path

# %% Types
result: str

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

# %% Result