5. Tietokannat ohjelmoinnissa
Tietokannan käyttäminen
Python-kielen standardikirjastossa on moduuli sqlite3, jonka avulla voidaan käyttää SQLite-tietokantaa. Seuraava koodi on pohja tietokannan käyttämiselle:
import sqlite3
db = sqlite3.connect("testi.db")
db.isolation_level = None
# tietokantakomennot
Koodi luo olion db, jonka kautta voidaan käyttää tiedostossa testi.db olevaa tietokantaa. Jos tiedostoa ei ole valmiina olemassa, tiedosto luodaan ja tietokanta on aluksi tyhjä.
Koodi myös määrittelee, että isolation_level on None, mikä tarkoittaa, että kun tietokantaan tehdään muutoksia, ne tulevat voimaan välittömästi samaan tapaan kuin SQLite-tulkissa.
Komentojen suoritus
Metodi execute suorittaa halutun SQL-komennon tietokannassa. Esimerkiksi seuraavat komennot luovat taulun Tuotteet ja lisäävät sinne kolme riviä:
db.execute("""
CREATE TABLE Tuotteet (
id INTEGER PRIMARY KEY,
nimi TEXT,
hinta INTEGER
)
""")
db.execute("""
INSERT INTO Tuotteet (nimi, hinta)
VALUES ('selleri', 5)
""")
db.execute("""
INSERT INTO Tuotteet (nimi, hinta)
VALUES ('nauris', 8)
""")
db.execute("""
INSERT INTO Tuotteet (nimi, hinta)
VALUES ('lanttu', 4)
""")
Tässä on käytössä Pythonin """-syntaksi, jonka avulla voi määritellä monirivisen merkkijonon. Tämä syntaksi on kätevä SQL-komennoissa, jotka ovat usein pitkiä ja jotka pystyy jakamaan usealle riville tällä tavalla.
Huomaa, että SQL-komentojen lopussa ei tarvitse olla puolipistettä ;, koska on muutenkin selvää, mihin komento päättyy.
Metodilla execute voidaan myös hakea tietoa tietokannasta. Metodi fetchall antaa kyselyn tulokset listana, jossa jokaista tulostaulun riviä vastaa tuple:
tuotteet = db.execute("""
SELECT nimi, hinta
FROM Tuotteet
""").fetchall()
print(tuotteet)
[('selleri', 5), ('nauris', 8), ('lanttu', 4)]
Metodi fetchone puolestaan palauttaa ensimmäisen tulosrivin tuplena. Tämä metodi on erityisen hyödyllinen kyselyissä, jotka palauttavat aina yhden rivin:
hinta = db.execute("""
SELECT MAX(hinta)
FROM Tuotteet
""").fetchone()
print(hinta)
(8,)
Huomaa, että metodin palauttama arvo on tuple, jonka sisällä on luku, eikä sellaisenaan oleva luku. Seuraava koodi tulostaa luvun sellaisenaan:
hinta = db.execute("""
SELECT MAX(hinta)
FROM Tuotteet
""").fetchone()
print(hinta[0])
8
Parametrit
Seuraava koodi kysyy käyttäjältä tuotteen nimeä ja ilmoittaa sitten tuotteen hinnan tai tiedon siitä, että tuotetta ei ole tietokannassa.
nimi = input("Tuotteen nimi: ")
hinta = db.execute("""
SELECT hinta FROM Tuotteet WHERE nimi = ?
""", [nimi]).fetchone()
if hinta:
print("Hinta on", hinta[0])
else:
print("Ei löytynyt")
Tässä käyttäjän antama tieto yhdistetään kyselyyn parametrina: kyselyssä tiedon kohdalla on merkki ? ja sen kohdalle tuleva arvo annetaan listassa [nimi]. Esimerkiksi jos käyttäjä antaa nimen nauris, kyselystä tulee SELECT hinta FROM Tuotteet WHERE nimi = 'nauris'. Koska nimi on merkkijono, sen ympärille tulee SQL-komennossa automaattisesti '-merkit oikealla tavalla.
Jos SQL-kysely ei palauta riviä, metodi fetchone palauttaa arvon None. Tämän avulla voidaan tunnistaa tilanne, jossa tietokannassa ei ole haettua tietoa. Yllä olevassa koodissa ehto if hinta pätee silloin, kun hinta on jotain muuta kuin None eli kysely palautti rivin.
Seuraava koodi lisää uuden tuotteen tietokantaan:
nimi = input("Tuotteen nimi: ")
hinta = input("Tuotteen hinta: ")
db.execute("""
INSERT INTO Tuotteet (nimi, hinta) VALUES (?, ?)
""", [nimi, hinta])
Kun SQL-komennossa on useita parametreja, niiden arvot tulevat listan arvoista samassa järjestyksessä vasemmalta oikealle. Esimerkiksi jos käyttäjä antaa nimen nauris ja hinnan 5, äskeisestä komennosta tulee INSERT INTO Tuotteet (nimi, hinta) VALUES (̈́'nauris', 5).
Parametreja kannattaa käyttää aina, kun SQL-komennon osana on muuttuvaa tietoa. Parametrien etuna on, että tieto liitetään varmasti oikealla tavalla SQL-komennon osaksi. Esimerkiksi jos tuotteen nimi on Pepe's Drink, nimessä esiintyy '-merkki ja oikea tapa ilmoittaa nimi komennossa on 'Pepe\'s Drink'. Kun tieto annetaan parametrina, tämä muutos tehdään automaattisesti.
SQL-injektio
SQL-injektio on tietoturva-aukko, jossa pahantahtoinen käyttäjä muuttaa SQL-komennon rakennetta antamalla sopivanlaista tietoa. Tehokas tapa suojautua SQL-injektioilta on käyttää aina parametreja SQL-komennoissa, jolloin käyttäjän antama tieto lisätään komentoihin turvallisesti. Aihetta käsitellään tarkemmin kurssilla Tietokannat ja web-ohjelmointi.
Virheenkäsittely
Tietokannassa suoritettava komento saattaa epäonnistua. Esimerkiksi seuraava komento epäonnistuu, jos taulu Tuotteet on jo olemassa:
db.execute("""
CREATE TABLE Tuotteet (
id INTEGER PRIMARY KEY,
nimi TEXT,
hinta INTEGER
)
""")
Tällöin ohjelman suoritus päättyy seuraavaan virheeseen:
Traceback (most recent call last):
File "testi.py", line 6, in <module>
db.execute("""
sqlite3.OperationalError: table Tuotteet already exists
Virhe voidaan käsitellä myös Python-koodin puolella vaikkapa näin:
try:
db.execute("""
CREATE TABLE Tuotteet (
id INTEGER PRIMARY KEY,
nimi TEXT,
hinta INTEGER
)
""")
except:
print("Taulua ei voitu luoda")
Tällöin ohjelman suoritus jatkuu eteenpäin eikä pääty virheeseen.
Lisätyn rivin id-numero
Seuraava koodi ilmoittaa tietokantaan lisätyn rivin id-numeron:
tulos = db.execute("""
INSERT INTO Tuotteet (nimi, hinta)
VALUES ('lanttu', 4)
""")
print(tulos.lastrowid)
Tästä on hyötyä, jos tietokantaan lisätään tämän jälkeen muita rivejä, joka viittaavat ensin lisättyyn riviin.
Käyttöliittymä
Seuraava ohjelma toteuttaa käyttöliittymän, jonka avulla käyttäjä voi lisätä tietokantaan tuotteita, hakea tuotteen hinnan tai poistua ohjelmasta. Ohjelma olettaa, että tiedostossa testi.db on valmiina olemassa taulu Tuotteet.
import sqlite3
db = sqlite3.connect("testi.db")
db.isolation_level = None
print("1 - Lisää uusi tuote")
print("2 - Hae tuotteen hinta")
print("3 - Sulje ohjelma")
while True:
komento = input("Anna komento: ")
if komento == "1":
nimi = input("Tuotteen nimi: ")
hinta = input("Tuotteen hinta: ")
db.execute("""
INSERT INTO Tuotteet (nimi, hinta)
VALUES (?, ?)
""", [nimi, hinta])
if komento == "2":
nimi = input("Tuotteen nimi: ")
hinta = db.execute("""
SELECT hinta FROM Tuotteet
WHERE nimi = ?
""", [nimi]).fetchone()
if hinta:
print("Hinta on", hinta[0])
else:
print("Ei löytynyt")
if komento == "3":
break
Ohjelman suoritus voi näyttää seuraavalta:
1 - Lisää uusi tuote
2 - Hae tuotteen hinta
3 - Sulje ohjelma
Anna komento: 2
Tuotteen nimi: selleri
Hinta on 5
Anna komento: 2
Tuotteen nimi: palsternakka
Ei löytynyt
Anna komento: 1
Tuotteen nimi: palsternakka
Tuotteen hinta: 9
Anna komento: 2
Tuotteen nimi: palsternakka
Hinta on 9
Anna komento: 3
Koodin rakenne paremmaksi
Usein pidetään hyvänä, että tietokannan käsittely ja käyttöliittymän toteutus ovat toisistaan erillään ohjelmassa. Seuraava koodi toteuttaa tämän niin, että moduuli tuotteet.py käsittelee tietokantaa ja moduuli main.py on pääohjelma, joka näyttää käyttöliittymän.
tuotteet.py
import sqlite3
db = sqlite3.connect("testi.db")
db.isolation_level = None
def lisaa_tuote(nimi, hinta):
db.execute("""
INSERT INTO Tuotteet (nimi, hinta) VALUES (?, ?)
""", [nimi, hinta])
def hae_hinta(nimi):
hinta = db.execute("""
SELECT hinta FROM Tuotteet WHERE nimi = ?
""", [nimi]).fetchone()
if hinta:
return hinta[0]
else:
return None
main.py
import tuotteet
print("1 - Lisää uusi tuote")
print("2 - Hae tuotteen hinta")
print("3 - Sulje ohjelma")
while True:
komento = input("Anna komento: ")
if komento == "1":
nimi = input("Tuotteen nimi: ")
hinta = input("Tuotteen hinta: ")
tuotteet.lisaa_tuote(nimi, hinta)
if komento == "2":
nimi = input("Tuotteen nimi: ")
hinta = tuotteet.hae_hinta(nimi)
if hinta:
print("Hinta on", hinta)
else:
print("Ei löytynyt")
if komento == "3":
break
Tällaisessa toteutuksessa käyttöliittymässä ei näy mitään siitä, että tiedot tallennetaan nimenomaan SQLite-tietokantaan, vaan tallennustapaa voisi periaatteessa muuttaa ilman, että käyttöliittymään tulisi mitään muutoksia.
Laajemmassa sovelluksessa olisi mielekästä jakaa tietokannan käsittely useampaan tiedostoon. Tällaisia sovelluksia tehdään myöhemmillä tietojenkäsittelytieteen kursseilla.
Mitä tehdä missäkin?
Tietokannan ja koodin puolella voi usein tehdä samantapaisia asioita. Esimerkiksi seuraavassa on kaksi tapaa etsiä kallein tuotteen hinta tietokannasta:
kallein = db.execute("""
SELECT MAX(hinta) FROM Tuotteet
""").fetchone()
hinnat = db.execute("""
SELECT hinta FROM Tuotteet
""").fetchall()
kallein = max(hinnat)
Ensimmäisessä tavassa haetaan kallein hinta tietokannan puolella SQL:n MAX-funktiolla. Toisessa tavassa haetaan tietokannasta kaikkien tuotteiden hinnat listaan ja etsitään sitten koodin puolella listan kallein hinta Pythonin max-funktiolla.
Näistä kahdesta tavasta ensimmäinen tapa on selkeästi parempi: ei ole hyvä hakea turhaa tietoa koodin puolelle ja tehdä käsittelyä, jonka voi tehdä helposti myös tietokannassa.
Erityisesti kannattaa välttää tilannetta, jossa suoritetaan turhaan useita SQL-komentoja, vaikka vain yksi komento riittäisi. Esimerkiksi seuraavassa on huono tapa hakea tietokannasta jokaisen opettajan nimi ja kurssien määrä:
opettajat = db.execute("""
SELECT id, nimi FROM Opettajat
""").fetchall()
for opettaja in opettajat:
maara = db.execute("""
SELECT COUNT(*) FROM Kurssit
WHERE opettaja_id = ?
""", [opettaja[0]]).fetchone()
print(opettaja[1], maara[0])
Koodi hakee ensin listaan kunkin opettajan id-numeron ja nimen. Tämän jälkeen koodi hakee silmukassa jokaisesta opettajasta erikseen niiden kurssien määrän, joita kyseinen opettaja opettaa. Koodi on kyllä toimiva mutta se tekee valtavasti turhaa työtä hakiessaan jokaisen tiedon erikseen. Parempi ratkaisu on muodostaa yksi kysely, joka hakee suoraan kaiken tarvittavan:
tiedot = db.execute("""
SELECT O.nimi, COUNT(*)
FROM Opettajat O LEFT JOIN Kurssit K ON O.id = K.opettaja_id
GROUP BY O.id
""").fetchall()
for rivi in tiedot:
print(rivi[0], rivi[1])
Tuloksena oleva kysely on monimutkaisempi, mutta sen avulla tietokantajärjestelmä voi optimoida kokonaisuutena halutun tiedon hakemisen ja toimittaa tiedon mahdollisimman tehokkaasti koodille.
Kuitenkaan tietokannan puolella ei kannata tehdä kaikkea, mikä on teoriassa mahdollista. Tästä esimerkkinä on seuraava koodi, joka hakee tietokannasta tuloslistan, jossa pelaajat on järjestettynä pistemäärän ja nimen mukaan. Tulostuksessa pelaajista näytetään myös sija (1, 2, 3, jne.) listalla.
lista = db.execute("""
SELECT nimi, pisteet
FROM Tulokset
ORDER BY pisteet DESC, nimi
""").fetchall()
sija = 1
for tulos in lista:
print(sija, tulos[0], tulos[1])
sija += 1
Tässä tapauksessa pelaajien sijat lasketaan koodin puolella muuttujan sija avulla. Olisi mahdollista laatia monimutkainen SQL-kysely, jonka tulostaulussa on myös sijat, kuten on tehty kurssin materiaalin luvun 4 lopussa. Kuitenkin parempi ratkaisu on laskea sijat koodissa yllä olevalla tavalla, koska tämän voi tehdä helposti ja tehokkaasti ja SQL-kysely säilyy yksinkertaisena.