Andmebaasi algus: SQLite ja Python

SQLite salvestab andmebaasi ühte faili ega vaja eraldi serverit. Kasutame seda küsimuse lahendamiseks: kuidas siduda õppijad nende tulemustega ja arvutada keskmine, kordamata igal real õppija põhiandmeid?

Turvaline harjutuskoht ja tööriist

Vaja on käsku sqlite3. Pythoni moodul sqlite3 võib olla olemas ka siis, kui eraldi käsureaprogramm puudub. Vajadusel vaata paketihaldust.

mktemp -d loob uue kordumatu nimega kataloogi ja väljastab selle tee. Käsuasendus $(...) salvestab tee muutujasse, nii et näide ei kirjuta varasemat andmebaasi üle:

katse=$(mktemp -d)
cd "$katse"
pwd

Jäta tee meelde. Ajutises kataloogis olevat tööd võib süsteem hiljem koristada; see on katse, mitte püsivate andmete asukoht.

sqlite3 andmed.db avab interaktiivse SQL-käsurea; väljumiseks kirjuta .quit. Punktiga käsud, näiteks .tables ja .schema, on SQLite'i kliendi käsud, mitte SQL.

Loo õppijate tabel

CREATE TABLE loob tabeli, INSERT INTO lisab kirjed ja SELECT loeb neid. INTEGER on täisarv, TEXT tekst ning NOT NULL keelab puuduva väärtuse. SQL-laused lõpevad semikooloniga.

Shelli <<'SQL' annab käsule mitmerealise sisendi kuni eraldi real oleva SQL-ini. Valikud -header -column kuvavad tulemuse veerupealkirjadega tabelina.

sqlite3 -header -column andmed.db <<'SQL'
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);
INSERT INTO students VALUES
  (1, 'Mari', 'Tartu'),
  (2, 'Jaan', 'Tallinn'),
  (3, 'Liis', 'Narva');
SELECT * FROM students;
SQL

Näed kolme õppijat. Tabeli loomine ja lisamine ise tavaliselt teksti ei väljasta. Ära käivita loomise plokki samas andmebaasis uuesti: tabel on juba olemas. Uueks katseks loo uus ajutine kataloog, mitte ära kustuta seniseid tabeleid.

Struktuuri saad hiljem kontrollida shellist käsuga sqlite3 andmed.db '.schema students'.

Lisa tulemused eraldi tabelisse

Ühel õppijal võib olla mitu tulemust. results.student_id viitab tabeli students.id väärtusele. PRAGMA foreign_keys = ON lülitab viidete kontrolli sisse; SQLite'is tuleb seda teha igas andmeid muutvas ühenduses, kui kontroll pole juba lubatud.

sqlite3 andmed.db <<'SQL'
PRAGMA foreign_keys = ON;
CREATE TABLE results (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL,
  subject TEXT NOT NULL,
  score INTEGER NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(id)
);
INSERT INTO results (student_id, subject, score) VALUES
  (1, 'matemaatika', 91), (1, 'python', 95),
  (2, 'matemaatika', 84), (2, 'python', 79),
  (3, 'matemaatika', 88), (3, 'python', 90);
SQL

Viidete kontroll takistab tulemuse lisamist olematule õppijale. See ei kontrolli automaatselt näiteks punktisumma mõistlikku vahemikku; sellised reeglid tuleb eraldi määrata.

Seo ja koonda

Järgmises päringus on s ja r tabelite lühinimed. ON määrab seose ja ORDER BY tulemuse järjekorra:

sqlite3 -header -column andmed.db "
SELECT s.name, s.city, r.subject, r.score
FROM results r
JOIN students s ON s.id = r.student_id
ORDER BY s.name, r.subject;
"

Näed kuut tulemust koos õppijanimedega. See JOIN jätab välja õppijad, kellel tulemust pole; kõigi õppijate säilitamiseks oleks vaja LEFT JOIN-i.

Keskmiseks rühmita õppija järgi. AVG arvutab keskmise, ROUND(..., 1) ümardab ühe kümnendkohani, AS annab tulemuse veerule nime ja DESC järjestab kahanevalt:

sqlite3 -header -column andmed.db "
SELECT s.name, ROUND(AVG(r.score), 1) AS keskmine
FROM results r
JOIN students s ON s.id = r.student_id
GROUP BY s.id, s.name
ORDER BY keskmine DESC;
"

Tulemused on Mari 93.0, Liis 89.0 ja Jaan 81.5. Päring ei muuda salvestatud hindeid.

Sama andmebaas Pythonist

Salvesta järgmine kood faili naide.py samas kataloogis. Käivita python3 naide.py. Päringu ? on parameetri kohatäitja: kasutaja väärtusi ei liideta SQL-i stringi sisse.

import sqlite3

conn = sqlite3.connect("andmed.db")
try:
    for name, in conn.execute(
        "SELECT name FROM students WHERE city = ?", ("Tartu",)
    ):
        print(name)
finally:
    conn.close()

Näed Mari. SQL valib andmed; Python saab tulemuse edasi töödelda. finally sulgeb ühenduse ka vea korral.

Minitest

1. Leia kõigi õppijate asemel ainult Tallinna õppija nimi.
2. Miks ei tohiks seost teha nime järgi, kui kahel õppijal võib olla sama nimi?
3. Mis muutuks keskmise päringus, kui eemaldada GROUP BY?
4. Miks ei piisa SQLite'is ainult võõrvõtme kirjutamisest tabeli kirjeldusse?
5. Muuda Pythoni näites linna parameetrit, jättes SQL-teksti samaks.

Lisalugemine

SQLite'i võõrvõtmete juhend selgitab viidete kontrolli. Üldised viited on andmevormingute ja SQLite'i lisalugemises.