Andmebaasi algus: SQLite ja Python

Selles peatükis teeme esimese praktilise silla tabelite, SQL-i ja Pythoni vahele.

Loogika

SQLite on hea esimene andmebaas, sest ta ei vaja eraldi serverit ja elab lihtsalt failina.

See teeb ta heaks sillaks nende teemade vahel:

• failid ja failisüsteem
• SQL päringud
• relatsiooniline andmemudel
• Pythoni programm, mis andmebaasi kasutab

Oluline mõte on:

• CSV fail on lihtsalt tekstifail
• andmebaas lisab sellele struktuuri, päringud ja seosed

Relatsioonilise mõtteviisi kõige tähtsamad algmõisted on:

• tabel: ühe teema andmed
• rida: üks kirje
• veerg: ühe omaduse koht
• primaarvõti: rea unikaalne tunnus
• võõrvõti: viide teise tabeli reale
• JOIN: kahe tabeli kokku sidumine seose järgi

SQLite on hea, sest need mõisted saab läbi proovida ilma, et peaksid kohe serverit või pilvekeskkonda haldama.

Kiirspikker

• sqlite3 andmed.db ava andmebaas
• .tables näita tabeleid
• .schema näita tabelite struktuuri
• select * from tabel limit 5; kuva paar esimest rida
• select ... from a join b on ...; ühenda kaks tabelit
• select city, count(*) from students group by city; koonda read rühmade kaupa

Väga praktilised 1-linerid on:

sqlite3 andmed.db '.tables'
sqlite3 andmed.db '.schema'
sqlite3 andmed.db 'select * from students limit 5;'
sqlite3 andmed.db 'select s.name, r.score from results r join students s on s.id = r.student_id;'
sqlite3 andmed.db 'select city, count(*) from students group by city order by count(*) desc;'

Need on head just sellepärast, et ei pea alati minema interaktiivsesse sqlite3 shelli, kui tahad lihtsalt kiiret vastust.

Kõige tavalisemad käsukujud

• create table ...; loo tabel
• insert into ... values (...); lisa read
• select * from ...; kuva kõik read
• select ... from ... where ...; filtreeri
• select ... from a join b on ...; ühenda tabelid
• select ..., count(*) from ... group by ...; loenda rühmade kaupa

Hea rusikareegel:

• üks tabel kirjeldab üht tüüpi asja
• teise tabeli viide esimesele tabelile tehakse võõrvõtmega
• JOIN ei ole eraldi maagia, vaid viis need read omavahel kokku viia

Näited

Näide: üks tabel

Loome väga väikese andmebaasi ühe tabeliga:

sqlite3 andmed.db <<'EOF'
drop table if exists students;
create table students (
  id integer primary key,
  name text not null,
  city text,
  score integer
);
insert into students (name, city, score) values
  ('Mari', 'Tartu', 91),
  ('Jaan', 'Tallinn', 84),
  ('Liis', 'Narva', 88);
select * from students;
EOF

Siin:

• id integer primary key annab igale reale unikaalse tunnuse
• name, city ja score on veerud
• iga insert lisab ridu

Kasulikud järgmised vaated:

sqlite3 andmed.db '.schema students'
sqlite3 andmed.db 'select name, score from students order by score desc;'
sqlite3 andmed.db 'select city, count(*) from students group by city;'

Näide: kaks tabelit ja JOIN

Nüüd teeme andmemudeli natuke realistlikumaks. Punktid ei pea olema samas tabelis nagu tudengi põhiandmed.

sqlite3 andmed.db <<'EOF'
drop table if exists results;
drop table if exists students;

create table students (
  id integer primary key,
  name text not null,
  city text
);

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 students (id, name, city) values
  (1, 'Mari', 'Tartu'),
  (2, 'Jaan', 'Tallinn'),
  (3, 'Liis', 'Narva');

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);
EOF

Nüüd:

• tabel students hoiab tudengite põhiandmeid
• tabel results hoiab tulemusi
• results.student_id viitab students.id väljale

See ongi relatsioonilise andmemudeli põhiidee: seosed tehakse võtmete kaudu, mitte suvalise tekstilise kokkusobitamisega.

Vaatame tulemusi:

sqlite3 andmed.db 'select * from students;'
sqlite3 andmed.db 'select * from results;'

Ja nüüd ühendame need:

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

Siin:

• r ja s on lühikesed aliased tabelinimedele
• join students s on s.id = r.student_id ütleb, kuidas read kokku viiakse
• väljundis näed ühe tabeli asemel kahe tabeli kombineeritud pilti

Näide: GROUP BY

Sageli ei taheta näha kõiki ridu, vaid kokkuvõtet.

Näiteks tudengite keskmine tulemus:

sqlite3 andmed.db "
select s.name, round(avg(r.score), 1) as avg_score
from results r
join students s on s.id = r.student_id
group by s.id, s.name
order by avg_score desc;
"

Ja näiteks linnade kaupa tudengite arv:

sqlite3 andmed.db "
select city, count(*) as students_in_city
from students
group by city
order by students_in_city desc, city;
"

See on oluline vahe:

• JOIN toob seotud read kokku
• GROUP BY teeb neist koondvaate

Näide Pythoniga

SQLite ja Python sobivad hästi kokku, sest Pythonis on sqlite3 moodul kohe olemas.

import sqlite3

conn = sqlite3.connect("andmed.db")
cur = conn.cursor()

cur.execute("""
select s.name, round(avg(r.score), 1) as avg_score
from results r
join students s on s.id = r.student_id
group by s.id, s.name
order by avg_score desc
""")

for name, avg_score in cur.fetchall():
    print(f"{name}: {avg_score}")

conn.close()

Siin:

• Python ei asenda SQL-i, vaid kasutab seda
• SQL teeb andmete valiku ja koondamise
• Python saab tulemuse kätte ja teeb sellega edasi, mida vaja

Hea tööjaotus on sageli just selline:

• SQL: vali, ühenda, koonda
• Python: töötle, teisenda, visualiseeri, ehita suurem programm

Minitest

1. Loo tabel students, kus on vähemalt väljad id, name ja city.
2. Loo teine tabel, mis viitab tudengi id väärtusele võõrvõtmega.
3. Tee päring, mis kasutab JOIN-i, et kuvada mõlema tabeli info koos.
4. Tee päring, mis kasutab GROUP BY-d, et anda väike kokkuvõte.
5. Selgita ühe lausega, miks CSV fail ja andmebaasitabel ei ole sama asi.