!!4. Andmebaasid
Siiani oleme andmeid lugenud ja salvestanud tekstifailidesse. Väheste andmetega on see lihtne ja kiire lahendus, aga mida rohkem andmeid koguneb, seda aeglasemaks läheb nende otsimine. Mida keerulisemaks lähemad andmestruktuurid, seda keerulisem on neid failides hoida. Kõigi selle lahendamiseks on välja töötatud andmebaasisüsteemid, mis lubavad hoida palju keerulisi andmeid organiseeritult nii, et neile pääseb kiiresti ligi. 

Mõned sellised süsteemid on MySQL, PostgreSQL, MongoDB, Oracle Database ja Sybase SQL Anywhere. Siin õppematerjalides keskendume hoopis süsteemile SQLite. See on üks lihtsamatest süsteemidest, mida kasutavad tihti rakendused andmete hoidmiseks. Seda kasutavad näiteks Apple, Google, Facebook, Firefox ja [[https://www.sqlite.org/famous.html | paljud muud]]. Kõige tähtsam: see on Pythonisse sisse ehitatud!

!!!Ettevalmistus
Jätkamiseks ei pea paigaldama lisatarkvara. Võibolla tuleb kasuks [[https://sqlitebrowser.org/ | SQLite Browser]]. 

Et peatükist aru saada, peab läbima [[https://progeopik.cs.ut.ee/osa1.html | õpiku]] esimesed 3 peatükki ning tutvuma 7. peatüki [[https://progeopik.cs.ut.ee/07_listid.html#jarjendid | järjendite]] ja [[https://progeopik.cs.ut.ee/07_listid.html#ennikud | ennikutega]].

!!!SQL keel
Paljudel eelmainitud andmebaasi süsteemidel on midagi ühist: need kasutavad SQL-i. SQL (''Structured Query Language'') on päringukeel, mida kasutatakse andmebaaside loomiseks, andmete kirjutamiseks, pärimiseks ja muutmiseks ning paljuks muuks. 

SQLite'i saab katsetada lehel [[https://sqliteonline.com/ | SQLite Online]]. Kõik edaspidised käsud tasub sinna kirjutada ja läbi proovida.

Attach:silmaring4_sqliteonline.png


!!!!Tabeli loomine
Enne, kui saame andmeid andmebaasi salvestada, peame looma tabeli, mis kirjeldab andmete struktuuri. 

Oletame, et meil on veebileht, kus saab kasutajaks registreerida ja sisse logida. Igal kasutajal on mingi järjenumber ehk ID, tekstiline kasutajanimi ja tekstiline parool. Selliseid omadusi nimetatakse väljadeks. 

Loome tabeli selliste väljadega ja paneme selle nimeks "Kasutajad". Tabelit saab luua käsuga @@CREATE TABLE Tabel (...)@@ ning sulgudesse lähevad väljade nimed ja nende tüübid:

(:codestart sql gutter='false':)
CREATE TABLE Kasutajad (id INTEGER PRIMARY KEY, kasutajanimi TEXT, parool TEXT);
(:codeend:)

Kõik käsud tuleb lõpetada semikooloniga. 

PRIMARY KEY tähendab seda, et see on põhiline väli, millega sellele kasutajale viidata saab ning et iga järgmine kasutaja saab ühe võrra suurema järjenumbri.

!!!!Andmete lisamine tabelisse
Nüüd on tabel olemas ja puudu on ainult andmed. Andmeid saab tabelisse lisada käsuga @@INSERT INTO Tabel (...) VALUES (...)@@ nii, et esimestesse sulgudesse lähevad väljade nimed ja teistesse lähevad nende väärtused:

(:codestart sql gutter='false':)
INSERT INTO Kasutajad (kasutajanimi, parool) VALUES ('admin', 'parool123');
(:codeend:)

Väljale ID ei pea väärtust omistama, sest siis määratakse selle väärtuseks automaatselt alguses @@1@@, edaspidi tuleb selle väärtus eelmisest ühe võrra suurem. See on PRIMARY KEY tõttu. Sõned peavad olema ümbritsetud ülakomadega, mitte jutumärkidega.

Lisame veel mõned kasutajad:

(:codestart sql gutter='false':)
INSERT INTO Kasutajad (kasutajanimi, parool) VALUES ('kasutaja', 'hunter2');
INSERT INTO Kasutajad (kasutajanimi, parool) VALUES ('hea_nimi', 'hea_parool');
(:codeend:)

'''NB!''' Paroole ei tohi kunagi salvestada tavalise tekstina. Neid tuleks räsida ühepoolse algoritmiga nagu SHA-256. Näiteks tuleb "parool123" asemel salvestada andmebaasi räsi "f9c80861456cdd34bebfa8886ae3436f22bbc7343e27df6a3376bcce23ed330d". Parooli kaudu on võimalik saada räsi, aga räsi kaudu ei ole võimalik saada parooli. Kui keegi tahab sisse logida, tuleb sisselogimisparool ka räsida ja seejärel räsisid võrrelda. Kui räsid on võrdsed, siis sisestatud parool on õige. Räsimine on tähtis, sest kui keegi kunagi andmebaasile ligi saab, ei saada kätte paroole. Pythonis saab räsisid arvutada näiteks [[https://pypi.org/project/bcrypt/#password-hashing | bcrypt]] mooduliga.

!!!!Andmete pärimine tabelist
Kui andmed on tabelisse lisatud, peab neid kuidagi kätte saama. Seda tehakse @@SELECT@@ käsuga. Et kuvada kõik tabeli read, saab kirjutada käsu:

(:codestart sql gutter='false':)
SELECT * FROM Kasutajad;
(:codeend:)

|| border=1
||! id ||! kasutajanimi ||! parool ||
||1 ||admin ||parool123 ||
||2 ||kasutaja ||hunter2 ||
||3 ||hea_nimi ||hea_parool ||

Tärn tähendab seda, et tagastatakse kõik väljad. Saab ka valida, milliseid tagastada:

(:codestart sql gutter='false':)
SELECT id, kasutajanimi FROM Kasutajad;
(:codeend:)

|| border=1
||! id ||! kasutajanimi ||
||1 ||admin ||
||2 ||kasutaja ||
||3 ||hea_nimi ||

Saame täpsustada, milliseid andmeid tahame võtmesõnaga @@WHERE@@:

(:codestart sql gutter='false':)
SELECT parool FROM Kasutajad WHERE kasutajanimi='admin';
(:codeend:)

|| border=1
||! parool ||
||parool123 ||

!!!!Andmete muutmine
Kui on vaja muuta näiteks kasutaja parooli, tuleb teha @@UPDATE@@ käsk.

(:codestart sql gutter='false':)
UPDATE Kasutajad SET parool='hunter3' WHERE kasutajanimi='kasutaja';
(:codeend:)

!!!!Tabeli kustutamine
Tabelit saab kustutada käsuga DROP TABLE. Selle käigus kustutatakse ka kõik read, seega selle käsuga peab olema ettevaatlik.

(:codestart sql gutter='false':)
DROP TABLE Kasutajad;
(:codeend:)

!!!!Veel SQL-i!
SQL-il on veel palju rohkem võimalusi nagu mitme tabeli omavahelised suhted, vaated, funktsioonid jne. Siin kursusel keerulisemat SQL-i ei käsitleta. Andmebaasidesse ja SQL-i süvenetakse täies rauas aines "Andmebaasid" (LTAT.03.004). Enne seda saab SQL-i harjutada ja edasi õppida [[https://sqlzoo.net/wiki/SELECT_basics | SQLZOO]] leheküljel.

!!!!Enesekontroll
(:includeurl https://courses.cs.ut.ee/LTAT.03.001/2020_fall/uploads/Main/silmaring4_1.html width=100% height="230px" border="0" :)
(:includeurl https://courses.cs.ut.ee/LTAT.03.001/2020_fall/uploads/Main/silmaring4_2.html width=100% height="230px" border="0" :)
(:includeurl https://courses.cs.ut.ee/LTAT.03.001/2020_fall/uploads/Main/silmaring4_3.html width=100% height="230px" border="0" :)

!!!Andmebaasid Pythoniga
Kõige põhilisemad SQL-käsud on nüüd selged. Proovime teha samasuguse andmebaasi Pythonis. Nagu eelnevalt mainitud, on SQLite Pythonisse sisseehitatud. Selle kasutamiseks tuleb importida moodul @@sqlite3@@.

(:codestart python gutter='false':)
>>> import sqlite3
(:codeend:)

Mooduli dokumentatsioon: [[https://docs.python.org/3/library/sqlite3.html | https://docs.python.org/3/library/sqlite3.html]] 

!!!!Andmebaasiga ühendamine
Et andmebaasiga suhelda, tuleb kõigepealt luua ühendus. See on sarnane faili avamisele. Ühtlasi hoitakse SQLite andmebaase failides. Teiste andmebaasisüsteemidega on tavaliselt keerulisem ühendada.

(:codestart python gutter='false':)
>>> ühendus = sqlite3.connect("andmebaas.db")
(:codeend:)

Kui sellise failinimega andmebaasi veel pole, siis see luuakse automaatselt.

Et andmebaasi käske saata, peab veel tegema kursori, mille kaudu seda teha.

(:codestart python gutter='false':)
>>> kursor = ühendus.cursor()
(:codeend:)

Nüüd saab saata SQL-käske @@execute()@@ meetodiga. Loome kõigepealt sama tabeli, mille lõime enne.

(:codestart python gutter='false':)
>>> käsk = "CREATE TABLE Kasutajad (id INTEGER PRIMARY KEY, kasutajanimi TEXT UNIQUE, parool TEXT);"
>>> kursor.execute(käsk)
(:codeend:)

Veel ei ole tegelikult andmebaasis ühtegi muudatust tehtud. Kui käivitatakse käsud, mis andmebaasis midagi muudavad, on vaja need ka kinnitada. 

(:codestart python gutter='false':)
>>> ühendus.commit()
(:codeend:)

Sarnaselt failidega tuleb andmebaasi ühendus pärast kasutamist kinni panna:

(:codestart python gutter='false':)
>>> ühendus.close()
(:codeend:)

Kui ühendus on avatud, ei saa teised programmid samal ajal sama andmebaasiga ühendada: visatakse erind, et andmebaas on lukus. 

!!!!Andmete lisamine
Loome uuesti ühenduse andmebaasiga. 

Andmete lisamiseks saab muidugi lihtsalt teha @@kursor.execute()@@ nagu enne ja sisestada @@INSERT@@ käsk, aga siin tuleb väga ettevaatlik olla, sest andmebaasi lisatavad andmed tulevad tihti kasutajatelt. 

Oletame, et saame kasutajalt kasutajanime ja parooli ning meie käsk on järgmine:

(:codestart python gutter='false':)
>>> käsk = "INSERT INTO Kasutajad (kasutajanimi, parool) VALUES ('" + kasutajanimi + "', '" + parool + "');"
(:codeend:)

Kui sisestatud kasutajanimi ja parool on tavalised, siis on käsk korralik:

(:codestart python gutter='false':)
>>> käsk
"INSERT INTO Kasutajad VALUES ('kasutaja', 'hunter2');"
(:codeend:)

Aga mis siis, kui muutuja @@kasutajanimi@@ saab sellise väärtuse?

(:codestart python gutter='false':)
>>> kasutajanimi = "'; DROP TABLE Kasutajad;"
(:codeend:)

Siis terve käsk oleks selline:

(:codestart python gutter='false':)
>>> käsk
"INSERT INTO Kasutajad VALUES (''; DROP TABLE Kasutajad; , '');"
(:codeend:)

@@INSERT@@ käsk lõpetatakse varakult ära, sest kasutajanimi lõpetas ülakomaga sõne ära ja semikooloniga. See ei ole korrektne käsk, sest üks muutuja on puudu ja seda ignoreeritakse. Siis tehakse käsk @@DROP TABLE Kasutajad@@, mis kustutab terve tabeli ära. Lõpus on mõned sümbolid, mis ei tee ka midagi. Kasutajal õnnestus andmebaasile ligipääsuta kustutada ära terve tabel.

Sellist manöövrit kutsutakse [[https://en.wikipedia.org/wiki/SQL_injection | SQL-i süstimiseks]] (ingl. k. ''SQL injection'') ja see on väga levinud rünnak rakenduste vastu: 

Attach:silmaring4_xkcd.png
[[https://xkcd.com/327/ | XKCD 327: Exploits of a Mom]]

Selle vältimiseks on @@sqlite3@@ moodulil võimalused olemas: muutujad tuleb asendada küsimärkidega ja nende väärtused lähevad ennikuna teise parameetrisse.

(:codestart python gutter='false':)
>>> kasutajanimi = "admin"
>>> parool = "parool123"
>>> käsk = "INSERT INTO Kasutajad VALUES (NULL, ?, ?);"
>>> kursor.execute(käsk, (kasutajanimi, parool))
(:codeend:)

Nii ei lõpeta ülakoma sõne ja kasutajanimeks saab päriselt see, mis on muutuja väärtus. 

'''NB!''' Ühe elemendiga ennik on Pythonis vormingus @@(muutuja,)@@. Lihtsalt sulgudes muutuja on sama, mis muutuja ise.

Lisa ka teised kasutajad tabelisse. Et muutused sisse läheks, peab jälle ühenduse kinnitama.

(:codestart python gutter='false':)
>>> ühendus.commit()
(:codeend:)

!!!!Andmete pärimine
@@SELECT@@ käskude tulemused saab kätte kursorist @@fetchall()@@ meetodiga.

(:codestart python gutter='false':)
>>> kursor = ühendus.cursor()
>>> kursor.execute("SELECT * FROM Kasutajad;")
>>> kursor.fetchall()
[(1, 'admin', 'parool123'), (2, 'kasutaja', 'hunter2'), (3, 'hea_kasutaja', 'hea_parool')]
(:codeend:)

Tagastatakse järjend ennikutest, mis sisaldavad iga rea andmeid soovitud järjekorras. Kui järjekorda pole täpsustatud ja on kasutatud tärni, võetakse järjekord tabeli loomise käsust: id, kasutajanimi, parool.

!!!!Keerulisemad andmebaasid
Siin kursusel rohkem andmebaase ei käsitleta. Kui sarnaselt jätkata @@sqlite3@@ mooduliga, võivad mahukad projektid minna liiga keeruliseks. Selle vastu on loodud moodulid nagu [[https://www.sqlalchemy.org/ | SQLAlchemy]] ja [[http://docs.peewee-orm.com/en/latest/ | Peewee]], mis viivad vastavusse andmebaasi tabelid ja Pythoni objektid (ingl. k. ''Object Relational Mapping''). See teeb andmebaasiga suhtlemise koodi loetavamaks ning SQL-i ei pea kirjutamagi. Pythoni objektidest räägitakse lähemalt peatükis "Objektorienteeritud programmeerimine". 

!!!Enesekontrolliküsimused
(:includeurl https://courses.cs.ut.ee/LTAT.03.001/2020_fall/uploads/Main/silmaring4_4.html width=100% height="230px" border="0" :)
(:includeurl https://courses.cs.ut.ee/LTAT.03.001/2020_fall/uploads/Main/silmaring4_5.html width=100% height="230px" border="0" :)
(:includeurl https://courses.cs.ut.ee/LTAT.03.001/2020_fall/uploads/Main/silmaring4_6.html width=100% height="230px" border="0" :)

!!!Ülesanded
1. Kirjuta programm @@loo_andmebaas.py@@, mis loob tabeli, millel on 4 välja:

# id: arv, primaarne võti
# kasutajanimi: tekst
# parool: tekst
# lemmikarv: täisarv

2. Kirjuta programm @@registreeri.py@@, millega luuakse eelmises ülesandes loodud tabelisse uus kasutaja. 

(:codestart gutter='false':)
Sisesta kasutaja: admin
Sisesta parool: parimparoolmaailmas
Sisesta lemmik arv: 1337
Kasutaja loodud!
(:codeend:)

(:codestart gutter='false':)
Sisesta kasutaja: admin
Kasutajanimi on juba võetud!
(:codeend:)

3. Kirjuta programm @@logisisse.py@@, milles saab sisse logida eelmises ülesandes loodud kasutajatesse. 

(:codestart gutter='false':)
Sisesta kasutaja: admin2
Kasutajat ei leitud!
Sisesta kasutaja: admin
Sisesta parool: kõigeparemparoolmaailmas
Vale parool!
(:codeend:)

(:codestart gutter='false':)
Sisesta kasutaja: admin
Sisesta parool: parimparoolmaailmas
Edukalt sisse logitud! Sinu lemmik arv on 1337.
(:codeend:)
