5. Tietokannat ohjelmoinnissa

Testitietokanta

Tämän luvun esimerkeissä ja kurssin tehtävissä käytetään testitietokantaa, joka sisältää tiedot kaupunkipyörillä tehdyistä matkoista vuonna 2024 Helsingin ja Espoon alueella. Tietokanta perustuu HSL:n julkaisemaan avoimen datan aineistoon, joka on muutettu tätä kurssia varten SQLite-tietokannaksi.

Voit kopioida tietokannan itsellesi tästä: bikes_2024.zip

Tietokannassa on seuraavat kaksi taulua:

CREATE TABLE Stations (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE Trips (
    id INTEGER PRIMARY KEY,
    start_time TEXT,
    end_time TEXT,
    start_station_id INTEGER REFERENCES Stations,
    end_station_id INTEGER REFERENCES Stations,
    distance INTEGER,
    duration INTEGER
);

Taulu Stations sisältää tiedot kaupunkipyöräasemista. Taulussa on kaksi saraketta: id (id-numero) sekä name (aseman nimi).

Taulu Trips sisältää tiedot matkoista. Taulussa on seuraavat sarakkeet:

• id: id-numero
• start_time: matkan aloitusaika
• end_time: matkan lopetusaika
• start_station_id: aloitusaseman id-numero
• end_station_id: lopetusaseman id-numero
• distance: matkan pituus (metreinä)
• duration: matkan kesto (sekunteina)

Voimme tutkia tietokannan sisältöä SQLite-tulkin kautta seuraavaan tapaan:

$ sqlite3 bikes_2024.db
sqlite> .tables
Stations  Trips
sqlite> SELECT COUNT(*) FROM Stations;
458
sqlite> SELECT COUNT(*) FROM Trips;
2585668
sqlite> SELECT * FROM Stations LIMIT 10;
1|Kaivopuisto
2|Laivasillankatu
3|Kapteeninpuistikko
4|Viiskulma
5|Sepänkatu
6|Hietalahdentori
7|Designmuseo
8|Vanha kirkkopuisto
9|Erottajan aukio
10|Kasarmitori
sqlite> SELECT * FROM Trips WHERE id = 100;
100|2024-04-01T10:05:03|2024-04-01T10:26:19|119|259|4627|1271
sqlite> SELECT name FROM Stations WHERE id = 119;
Gebhardinaukio
sqlite> SELECT name FROM Stations WHERE id = 259;
Petter Wetterin tie
sqlite> .quit

Tämän perusteella tietokannassa on 458 asemaa ja 2585668 matkaa. Esimerkiksi matka id-numerolla 100 alkoi Gebhardinaukiolta ja päättyi Petter Wetterin tielle. Matkan pituus oli 4,6 kilometriä ja kesto reilut 21 minuuttia.

Katsotaan seuraavaksi, miten voimme käsitellä tätä tietokantaa Python- ja R-kielillä.

SQLite Pythonissa

Python-kielen standardikirjastossa on moduuli sqlite3, jonka avulla voidaan käyttää SQLite-tietokantaa. Seuraava koodi muodostaa yhteyden tietokantaan ja hakee taulusta Stations tietoa SQL-kyselyillä:

import sqlite3

db = sqlite3.connect("bikes_2024.db")

result = db.execute("SELECT id, name FROM Stations WHERE id = 5")
print(result.fetchone())

result = db.execute("SELECT id, name FROM Stations ORDER BY id LIMIT 10")
print(result.fetchall())

Koodin tulostus on seuraava:

(5, 'Sepänkatu')
[(1, 'Kaivopuisto'), (2, 'Laivasillankatu'), (3, 'Kapteeninpuistikko'), (4, 'Viiskulma'), (5, 'Sepänkatu'), (6, 'Hietalahdentori'), (7, 'Designmuseo'), (8, 'Vanha kirkkopuisto'), (9, 'Erottajan aukio'), (10, 'Kasarmitori')]

Tässä db on tietokantaolio, jonka kautta voidaan suorittaa SQL-komentoja metodilla execute. Tässä koodissa suoritetaan kaksi SELECT-komentoa.

Ensimmäinen SELECT-komento hakee taulusta Stations rivin, jonka id-numero on 5. Koska kysely palauttaa yhden rivin, käytetään metodia fetchone, joka palauttaa yhden rivin tuplena.

Toinen SELECT-komento hakee taulusta Stations kymmenen ensimmäistä riviä. Nyt käytetään metodia fetchall, joka palauttaa listan, jossa jokainen tuple vastaa yhtä tulostaulun riviä.

Tietokantatiedoston sijainti

Esimerkki: Aseman nimi

Seuraava koodi kysyy käyttäjältä aseman id-numeroa ja hakee sen perusteella aseman nimen tietokannasta:

import sqlite3

db = sqlite3.connect("bikes_2024.db")

station_id = input("Anna aseman id-numero: ")
result = db.execute("SELECT name FROM Stations WHERE id = ?", [station_id])
station_name = result.fetchone()[0]
print("Aseman nimi:", station_name)

Koodin suoritus voi näyttää seuraavalta:

Anna aseman id-numero: 42
Aseman nimi: Haapaniemenkatu

Tässä kyselyn osana on parametri ?, jonka arvoksi tulee muuttujassa station_id oleva id-numero. Koodi näyttää, miten metodin execute kutsumisen yhteydessä annetaan parametrien arvot listana.

Tässä tapauksessa metodi fetchone palauttaa tuplen, jossa on yksi alkio. Tämän alkion sisältö saadaan muuttujaan []-syntaksilla, jossa 0 tarkoittaa tuplen ensimmäisen alkion hakemista.

Koodin heikkoutena on vielä, että se ei ota huomioon tilannetta, jossa tietokannassa ei ole asemaa annetulla id-numerolla:

Anna aseman id-numero: 666
Traceback (most recent call last):
  File "test.py", line 7, in <module>
    station_name = result.fetchone()[0]
                   ~~~~~~~~~~~~~~~~~^^^
TypeError: 'NoneType' object is not subscriptable

Tässä tilanteessa metodi fetchone palauttaa arvon None, jolloin ei ole mahdollista hakea tuplen ensimmäistä alkiota. Voimme käsitellä tämän tilanteen esimerkiksi seuraavasti try/except-rakenteen avulla:

import sqlite3

db = sqlite3.connect("bikes_2024.db")

station_id = input("Anna aseman id-numero: ")
result = db.execute("SELECT name FROM Stations WHERE id = ?", [station_id])

try:
    station_name = result.fetchone()[0]
    print("Aseman nimi:", station_name)
except TypeError:
    print("Asemaa ei löytynyt")

Nyt koodi antaa selkeän viestin siitä, ettei asemaa löytynyt:

Anna aseman id-numero: 666
Asemaa ei löytynyt

Tässä on vielä toinen toteutustapa, jossa tietokantahaku suoritetaan funktion find_station_name kautta. Tämä funktio palauttaa aseman nimen tai merkkijonon --, jos asemaa ei löytynyt.

import sqlite3

db = sqlite3.connect("bikes_2024.db")

def find_station_name(station_id):
    result = db.execute("SELECT name FROM Stations WHERE id = ?",
                        [station_id])
    result_row = result.fetchone()
    return result_row[0] if result_row else "--"

station_id = input("Anna aseman id-numero: ")

station_name = find_station_name(station_id)
print("Aseman nimi:", station_name)

Nyt koodi toimii seuraavasti:

Anna aseman id-numero: 42
Aseman nimi: Haapaniemenkatu

Anna aseman id-numero: 666
Aseman nimi: --

Esimerkki: Kohdeasemat

Seuraava koodi kysyy käyttäjältä lähtöaseman ja päivämäärän ja etsii sitten kaikki kohdeasemat, joihin päättyi lähtöasemalta alkanut matka annettuna päivänä.

import sqlite3

db = sqlite3.connect("bikes_2024.db")

def find_destinations(station_name, date):
    sql = """SELECT
                 DISTINCT B.name
             FROM
                 Stations AS A, Stations AS B, Trips AS T
             WHERE
                 T.start_station_id = A.id AND
                 T.end_station_id = B.id AND
                 A.name = ? AND
                 T.start_time LIKE ?
             ORDER BY
                 B.name"""
    result = db.execute(sql, [station_name, date + "%"])
    return [row[0] for row in result.fetchall()]

station_name = input("Anna aseman nimi: ")
date = input("Anna päivämäärä: ")

destinations = find_destinations(station_name, date)
print("Number of destinations:", len(destinations))
for destination in destinations:
    print(destination)

Tässä on esimerkki koodin toiminnasta:

Anna aseman nimi: Syystie
Anna päivämäärä: 2024-05-16
Number of destinations: 5
A.I. Virtasen aukio
Ala-Malmin tori
Huhtakuja
Pukinmäen asema
Vanha Tapanilantie

Tässä tarvittava SQL-komento on monimutkainen, minkä takia se on jaettu koodissa usealle riville. Tässä on kätevä Pythonin """-syntaksi, jonka avulla pystyy määrittelemään usean rivin merkkijonon.

Komennolle annetaan kaksi parametria, jotka sijoitetaan merkkien ? kohdalle siinä järjestyksessä kuin ne on annettu listassa. Listan ensimmäinen alkio menee ensimmäisen merkin ? kohdalle ja toinen alkio menee toisen merkin ? kohdalle. Koska parametrit ovat merkkijonoja, ne sijoitetaan '-merkkien sisään SQL:ssä.

Tiettynä päivänä alkavat matkat saadaan etsittyä LIKE-syntaksin avulla rajoittamalla hakua niin, että sarakkeen start_time arvon alussa tulee olla annettu päivämäärä. Merkki % ilmaisee, että päivämäärän jälkeen voi tulla mikä tahansa kellonaika.

SQLite R-kielessä

R:ssä SQLite-tietokantaa käytetään tyypillisesti kirjaston RSQLite avulla, jonka voi asentaa komennolla install.packages("RSQLite"). Seuraava koodi muodostaa yhteyden tietokantaan ja hakee taulusta Stations tietoa SQL-kyselyillä:

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

result <- dbGetQuery(db, "SELECT id, name FROM Stations WHERE id = 5")
print(result)

result <- dbGetQuery(db, "SELECT id, name FROM Stations ORDER BY id LIMIT 10")
print(result)

Koodin tulostus on seuraava:

  id      name
1  5 Sepänkatu

   id               name
1   1        Kaivopuisto
2   2    Laivasillankatu
3   3 Kapteeninpuistikko
4   4          Viiskulma
5   5          Sepänkatu
6   6    Hietalahdentori
7   7        Designmuseo
8   8 Vanha kirkkopuisto
9   9    Erottajan aukio
10 10        Kasarmitori

Tässä db on tietokantaolio, jonka kautta voidaan suorittaa SQL-komentoja funktiolla dbGetQuery. Tässä koodissa suoritetaan kaksi SELECT-komentoa.

Ensimmäinen SELECT-komento hakee taulusta Stations rivin, jonka id-numero on 5. Toinen SELECT-komento hakee taulusta Stations kymmenen ensimmäistä riviä.

Tietokantatiedoston sijainti

Esimerkki: Aseman nimi

Seuraava koodi kysyy käyttäjältä aseman id-numeroa ja hakee sen perusteella aseman nimen tietokannasta:

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

station_id <- readline(prompt = "Anna aseman id-numero: ")
result <- dbGetQuery(db,
                     "SELECT name FROM Stations WHERE id = ?",
                     params = list(station_id))

station_name <- result$name[1]
cat("Aseman nimi:", station_name, "\n")

Koodin suoritus voi näyttää seuraavalta:

Anna aseman id-numero: 42
Aseman nimi: Haapaniemenkatu

Tässä kyselyn osana on parametri ?, jonka arvoksi tulee muuttujassa station_id oleva id-numero. Koodi näyttää, miten funktion dbGetQuery kutsumisen yhteydessä annetaan parametrien arvot listana.

Tässä syntaksi result$name[1] tarkoittaa, että haetaan sarakkeen name arvo tulostaulun rivillä 1 eli tässä tapauksessa ainoalla rivillä.

Jos tietokannassa ei ole asemaa annetulla id-numerolla, koodi antaa seuraavan tuloksen (NA tarkoittaa puuttuvaa arvoa):

Anna aseman id-numero: 666
Aseman nimi: NA

Voimme antaa tässä tilanteessa selkeämmän viestin asiasta esimerkiksi tarkastamalla tulostaulun rivien määrän:

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

station_id <- readline(prompt = "Anna aseman id-numero: ")

result <- dbGetQuery(db,
                     "SELECT name FROM Stations WHERE id = ?",
                     params = list(station_id))

if (nrow(result) == 1) {
  station_name <- result$name[1]
  cat("Aseman nimi:", station_name, "\n")
} else {
  cat("Asemaa ei löytynyt\n")
}

Nyt koodi antaa selkeän viestin siitä, ettei asemaa löytynyt:

Anna aseman id-numero: 666
Asemaa ei löytynyt

Tässä on vielä toinen toteutustapa, jossa tietokantahaku suoritetaan funktion find_station_name kautta. Tämä funktio palauttaa aseman nimen tai merkkijonon --, jos asemaa ei löytynyt.

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

find_station_name <- function(station_id) {
  result <- dbGetQuery(db,
                       "SELECT name FROM Stations WHERE id = ?",
                       params = list(station_id))
  if (nrow(result) == 1) result$name[1] else "--"
}

station_id <- readline(prompt = "Anna aseman id-numero: ")
station_name <- find_station_name(station_id)
cat("Aseman nimi:", station_name, "\n")

Nyt koodi toimii seuraavasti:

Anna aseman id-numero: 42
Aseman nimi: Haapaniemenkatu

Anna aseman id-numero: 666
Aseman nimi: --

Esimerkki: Kohdeasemat

Seuraava koodi kysyy käyttäjältä lähtöaseman ja päivämäärän ja etsii sitten kaikki kohdeasemat, joihin päättyi lähtöasemalta alkanut matka annettuna päivänä.

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

find_destinations <- function(station_name, date) {
  sql <- "
    SELECT DISTINCT B.name
    FROM Stations AS A, Stations AS B, Trips AS T
    WHERE
      T.start_station_id = A.id AND
      T.end_station_id = B.id AND
      A.name = ? AND
      T.start_time LIKE ?
    ORDER BY B.name
  "
  res <- dbGetQuery(db, sql, params = list(station_name, paste0(date, "%")))
  res$name
}

station_name <- readline(prompt = "Anna aseman nimi: ")
date <- readline(prompt = "Anna päivämäärä: ")

destinations <- find_destinations(station_name, date)
cat("Number of destinations:", length(destinations), "\n")
for (destination in destinations) {
  cat(destination, "\n")
}

Tässä on esimerkki koodin toiminnasta:

Anna aseman nimi: Syystie
Anna päivämäärä: 2024-05-16
Number of destinations: 5
A.I. Virtasen aukio
Ala-Malmin tori
Huhtakuja
Pukinmäen asema
Vanha Tapanilantie

Komennolle annetaan kaksi parametria, jotka sijoitetaan merkkien ? kohdalle siinä järjestyksessä kuin ne on annettu listassa. Listan ensimmäinen alkio menee ensimmäisen merkin ? kohdalle ja toinen alkio menee toisen merkin ? kohdalle. Koska parametrit ovat merkkijonoja, ne sijoitetaan '-merkkien sisään SQL:ssä.

Tiettynä päivänä alkavat matkat saadaan etsittyä LIKE-syntaksin avulla rajoittamalla hakua niin, että sarakkeen start_time arvon alussa tulee olla annettu päivämäärä. Funktio paste0 lisää päivämäärän perään merkin %, joka ilmaisee, että päivämäärän jälkeen voi tulla mikä tahansa kellonaika.

Mitä tehdä missäkin?

Tietokannan ja koodin puolella voi usein tehdä samantapaisia asioita. Esimerkiksi seuraavassa on kaksi tapaa etsiä pisin matka tietokannasta:

result = db.execute("SELECT MAX(distance) FROM Trips")
max_distance = result.fetchone()[0]
print(max_distance)

result = db.execute("SELECT distance FROM Trips")
max_distance = max(result.fetchall())[0]
print(max_distance)

Ensimmäisessä tavassa haetaan pisin matka tietokannan puolella SQL:n MAX-funktiolla. Toisessa tavassa haetaan tietokannasta kaikkien matkojen pituudet listaan ja etsitään sitten koodin puolella listan pisin matka 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 jokaisesta asemasta matkojen määrä:

result = db.execute("SELECT id, name FROM Stations")
stations = result.fetchall()

for station_id, station_name in stations:
    result = db.execute("""SELECT COUNT(*) FROM Trips
                           WHERE start_station_id = ?""",
                        [station_id])
    trip_count = result.fetchone()[0]
    print(station_name, trip_count)

Koodi hakee ensin listaan kunkin aseman id-numeron ja nimen. Tämän jälkeen koodi hakee silmukassa jokaisesta asemasta erikseen niiden matkojen määrän, jotka alkoivat kyseiseltä asemalta. 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:

sql = """SELECT S.name, COUNT(*)
         FROM Stations AS S
           LEFT JOIN Trips AS T ON S.id = T.start_station_id
         GROUP BY S.id"""

data = db.execute(sql).fetchall()
for station_name, trip_count in data:
    print(station_name, trip_count)

Tuloksena oleva kysely on monimutkaisempi, mutta sen avulla tietokantajärjestelmä voi optimoida kokonaisuutena halutun tiedon hakemisen ja toimittaa tiedon mahdollisimman tehokkaasti koodille.