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 paljud muud. Kõige tähtsam: see on Pythonisse sisse ehitatud!
Ettevalmistus
Jätkamiseks ei pea paigaldama lisatarkvara. Võibolla tuleb kasuks SQLite Browser.
Et peatükist aru saada, peab läbima õpiku esimesed 3 peatükki ning tutvuma 7. peatüki järjendite ja 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 SQLite Online. Kõik edaspidised käsud tasub sinna kirjutada ja läbi proovida.
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:
CREATE TABLE Kasutajad (id INTEGER PRIMARY KEY, kasutajanimi TEXT, parool TEXT);
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:
INSERT INTO Kasutajad (kasutajanimi, parool) VALUES ('admin', 'parool123');
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:
INSERT INTO Kasutajad (kasutajanimi, parool) VALUES ('kasutaja', 'hunter2');
INSERT INTO Kasutajad (kasutajanimi, parool) VALUES ('hea_nimi', 'hea_parool');
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 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:
SELECT * FROM Kasutajad;
| 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:
SELECT id, kasutajanimi FROM Kasutajad;
| id | kasutajanimi |
|---|---|
| 1 | admin |
| 2 | kasutaja |
| 3 | hea_nimi |
Saame täpsustada, milliseid andmeid tahame võtmesõnaga WHERE:
SELECT parool FROM Kasutajad WHERE kasutajanimi='admin';
| parool |
|---|
| parool123 |
Andmete muutmine
Kui on vaja muuta näiteks kasutaja parooli, tuleb teha UPDATE käsk.
UPDATE Kasutajad SET parool='hunter3' WHERE kasutajanimi='kasutaja';
Tabeli kustutamine
Tabelit saab kustutada käsuga DROP TABLE. Selle käigus kustutatakse ka kõik read, seega selle käsuga peab olema ettevaatlik.
DROP TABLE Kasutajad;
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 SQLZOO leheküljel.
Enesekontroll
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.
>>> import sqlite3
Mooduli dokumentatsioon: 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.
>>> ühendus = sqlite3.connect("andmebaas.db")
Kui sellise failinimega andmebaasi veel pole, siis see luuakse automaatselt.
Et andmebaasi käske saata, peab veel tegema kursori, mille kaudu seda teha.
>>> kursor = ühendus.cursor()
Nüüd saab saata SQL-käske execute() meetodiga. Loome kõigepealt sama tabeli, mille lõime enne.
>>> käsk = "CREATE TABLE Kasutajad (id INTEGER PRIMARY KEY, kasutajanimi TEXT UNIQUE, parool TEXT);" >>> kursor.execute(käsk)
Veel ei ole tegelikult andmebaasis ühtegi muudatust tehtud. Kui käivitatakse käsud, mis andmebaasis midagi muudavad, on vaja need ka kinnitada.
>>> ühendus.commit()
Sarnaselt failidega tuleb andmebaasi ühendus pärast kasutamist kinni panna:
>>> ühendus.close()
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:
>>> käsk = "INSERT INTO Kasutajad (kasutajanimi, parool) VALUES ('" + kasutajanimi + "', '" + parool + "');"
Kui sisestatud kasutajanimi ja parool on tavalised, siis on käsk korralik:
>>> käsk
"INSERT INTO Kasutajad VALUES ('kasutaja', 'hunter2');"
Aga mis siis, kui muutuja kasutajanimi saab sellise väärtuse?
>>> kasutajanimi = "'; DROP TABLE Kasutajad;"
Siis terve käsk oleks selline:
>>> käsk
"INSERT INTO Kasutajad VALUES (''; DROP TABLE Kasutajad; , '');"
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 SQL-i süstimiseks (ingl. k. SQL injection) ja see on väga levinud rünnak rakenduste vastu:
Selle vältimiseks on sqlite3 moodulil võimalused olemas: muutujad tuleb asendada küsimärkidega ja nende väärtused lähevad ennikuna teise parameetrisse.
>>> kasutajanimi = "admin" >>> parool = "parool123" >>> käsk = "INSERT INTO Kasutajad VALUES (NULL, ?, ?);" >>> kursor.execute(käsk, (kasutajanimi, parool))
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.
>>> ühendus.commit()
Andmete pärimine
SELECT käskude tulemused saab kätte kursorist fetchall() meetodiga.
>>> kursor = ühendus.cursor()
>>> kursor.execute("SELECT * FROM Kasutajad;")
>>> kursor.fetchall()
[(1, 'admin', 'parool123'), (2, 'kasutaja', 'hunter2'), (3, 'hea_kasutaja', 'hea_parool')]
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 SQLAlchemy ja 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
Ü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.
Sisesta kasutaja: admin Sisesta parool: parimparoolmaailmas Sisesta lemmik arv: 1337 Kasutaja loodud!
Sisesta kasutaja: admin Kasutajanimi on juba võetud!
3. Kirjuta programm logisisse.py, milles saab sisse logida eelmises ülesandes loodud kasutajatesse.
Sisesta kasutaja: admin2 Kasutajat ei leitud! Sisesta kasutaja: admin Sisesta parool: kõigeparemparoolmaailmas Vale parool!
Sisesta kasutaja: admin Sisesta parool: parimparoolmaailmas Edukalt sisse logitud! Sinu lemmik arv on 1337.