CREATE DATABASE spele;

CREATE TABLE IF NOT EXISTS speletaji (
    id        SERIAL PRIMARY KEY,     -- numurē pats, unikāls
    vards     TEXT NOT NULL UNIQUE,   -- obligāts un neatkārtojas
    punkti    INTEGER DEFAULT 0,      -- ja nenorāda, būs 0
    izveidots TIMESTAMP DEFAULT NOW()
);

-- Ierobežojumi datubāzi pasargā arī tad, ja Python kods kļūdās:
--   NOT NULL    lauks nedrīkst būt tukšs
--   UNIQUE      vērtība nedrīkst atkārtoties
--   DEFAULT     noklusējuma vērtība
--   CHECK       savs noteikums
ALTER TABLE speletaji
    ADD CONSTRAINT punkti_pozitivi CHECK (punkti >= 0);

\d speletaji        -- parāda tabulas uzbūvi (psql)

Pārbaudi liec datubāzē, ne tikai Python - tad tā nostrādā arī tad, ja datus ievada kāds cits.

10.2 CRUD - četras pamata darbības

-- CREATE
INSERT INTO speletaji (vards, punkti) VALUES ('Anna', 120), ('Jānis', 95);

-- READ
SELECT vards, punkti FROM speletaji
WHERE punkti > 100
ORDER BY punkti DESC
LIMIT 5;

SELECT COUNT(*) FROM speletaji;        -- cik rindu
SELECT AVG(punkti) FROM speletaji;     -- vidējais

-- UPDATE  <- VIENMĒR ar WHERE!
UPDATE speletaji SET punkti = punkti + 10 WHERE vards = 'Anna';

-- DELETE  <- VIENMĒR ar WHERE!
DELETE FROM speletaji WHERE punkti < 50;

-- Drošības tīkls, ja neesi drošs:
BEGIN;
DELETE FROM speletaji;      -- ups, bez WHERE
ROLLBACK;                   -- atsauc visu

Pirms UPDATE vai DELETE izpildi to pašu ar SELECT - redzēsi, ko tieši skarsi.

10.3 Primārās un ārējās atslēgas

CREATE TABLE speletaji (
    id    SERIAL PRIMARY KEY,          -- PK: unikāli identificē rindu
    vards TEXT NOT NULL UNIQUE
);

CREATE TABLE speles (
    id           SERIAL PRIMARY KEY,
    speletajs_id INTEGER NOT NULL
                 REFERENCES speletaji(id)      -- FK: norāda uz citu tabulu
                 ON DELETE CASCADE,            -- dzēšot spēlētāju, dzēš spēles
    punkti       INTEGER NOT NULL CHECK (punkti >= 0),
    spelets      TIMESTAMP DEFAULT NOW()
);

-- Ārējā atslēga neļauj:
--   ievadīt spēli neesošam spēlētājam
--   izdzēst spēlētāju, kuram ir spēles (bez ON DELETE CASCADE)

-- Normalizācija: spēlētāja vārdu glabā TIKAI vienā tabulā.
-- Nomainot vārdu vienuviet, tas mainās visos rezultātos.

Ja vienus un tos pašus datus glabā divās tabulās, agri vai vēlu tie sāk atšķirties.

10.4 JOIN un GROUP BY

-- INNER JOIN: tikai tie, kam ir atbilstība ABĀS tabulās
SELECT s.vards, sp.punkti
FROM speletaji s
INNER JOIN speles sp ON s.id = sp.speletajs_id
ORDER BY sp.punkti DESC;

-- LEFT JOIN: VISI spēlētāji, arī tie BEZ spēlēm (tiem būs NULL)
SELECT s.vards, COUNT(sp.id) AS speles_skaits
FROM speletaji s
LEFT JOIN speles sp ON s.id = sp.speletajs_id
GROUP BY s.vards;

-- Kopsavilkums
SELECT s.vards,
       COUNT(sp.id)        AS speles_skaits,
       SUM(sp.punkti)      AS kopa,
       AVG(sp.punkti)::INT AS videjie,
       MAX(sp.punkti)      AS rekords
FROM speletaji s
INNER JOIN speles sp ON s.id = sp.speletajs_id
GROUP BY s.vards
HAVING COUNT(sp.id) >= 2      -- HAVING filtrē GRUPAS, WHERE filtrē RINDAS
ORDER BY kopa DESC LIMIT 5;

Lietojot COUNT, SUM vai AVG, visiem pārējiem laukiem jābūt GROUP BY sarakstā.

10.5 psycopg2 - Python un datubāze

import psycopg2
from psycopg2.extras import RealDictCursor

with psycopg2.connect(host="localhost", database="spele",
                      user="postgres", password=parole) as conn:
    with conn.cursor(cursor_factory=RealDictCursor) as cur:

        # DROŠI: %s ir vietturis, NEVIS f-string
        cur.execute(
            "INSERT INTO speletaji (vards, punkti) VALUES (%s, %s)",
            (vards, punkti)
        )
        conn.commit()              # BEZ ŠĪ dati nesaglabājas!

        cur.execute("SELECT * FROM speletaji WHERE punkti > %s", (100,))
        for rinda in cur.fetchall():
            print(rinda["vards"])   # RealDictCursor -> pieeja pēc nosaukuma

# NEKAD tā:
#   f"SELECT * FROM speletaji WHERE vards = '{vards}'"
#   ievadot   ' OR '1'='1   lietotājs dabū VISAS rindas (SQL injekcija)

Paroli glabā vides mainīgajā vai .env failā, kas ir .gitignore sarakstā - nekad kodā.

SQL SELECT * FROM highscore ORDER BY punkti DESC LIMIT 5; -- top 5

← 9. Tēma 11. Tēma →

Datubāzes //un PostgreSQL

6 stundas - SQL, PostgreSQL un psycopg2

Ievads datubāzēs un PostgreSQL

SQL CRUD: SELECT, INSERT, UPDATE, DELETE

Tabulu projektēšana un saites

JOIN un sarežģīti vaicājumi

Python ↔ PostgreSQL (psycopg2)

Noslēguma projekts: Highscore datubāze

10. tēmas špikeris - datubāzes

10.1 Tabulas izveide un ierobežojumi

10.2 CRUD - četras pamata darbības

10.3 Primārās un ārējās atslēgas

10.4 JOIN un GROUP BY

10.5 psycopg2 - Python un datubāze

Datubāzes //
un PostgreSQL