5. Databaser i programmering

Testdatabas

I exemplen i det här kapitlet och i kursuppgifterna används en testdatabas som innehåller uppgifter om cykelturer med stadscyklarna under år 2024 i Helsingfors och Esbo. Databasen bygger på offentlig data som publicerats av HRT. Datan har bearbetats till en SQL-databas för denna kurs.

Du kan ladda ner databasen här: bikes_2024.zip

Databasen innehåller två tabeller:

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

Tabellen Stations innehåller uppgifter om stadscykelstationerna. Tabellen har två kolumner: id (ID-nummer) och name (stationens namn).

Tabellen Trips innehåller uppgifter om utförda cykelturer. Tabellen har följande kolumner:

id: ID-nummer
start_time: cykelturens starttid (i formen yyyy-mm-ddThh:mm:ss)
end_time: cykelturens sluttid (i formen yyyy-mm-ddThh:mm:ss)
start_station_id: ID-nummer för startstationen
end_station_id: ID-nummer för slutstationen
distance: cykelturens längd (i meter)
duration: cykelturens varaktighet (i sekunder)

Vi kan utforska innehållet i databasen via SQLite-tolken på följande sätt:

$ 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

Vi ser att databasen innehåller 458 stationer och 2 585 668 cykelturer. Till exempel började cykelturen med ID-nummer 100 vid Gebhardsplatsen (Gebhardinaukio) och slutade vid Petter Wetters väg (Petter Wetterin tie). Turen var 4,6 kilometer lång och tog drygt 21 minuter.

Låt oss se hur vi kan hantera denna databas med Python och R.

SQLite i Python

I Pythons standardbibliotek finns modulen sqlite3, som gör det möjligt att använda en SQLite-databas. Följande kod skapar en anslutning till databasen och hämtar information från tabellen Stations med SQL-frågor:

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())

Koden ger följande resultat:

(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')]

Här är db ett databasobjekt genom vilket SQL-kommandon kan köras med metoden execute. I den här koden körs två SELECT-kommandon.

Det första SELECT-kommandot hämtar från tabellen Stations raden med ID-numret 5. Eftersom frågan returnerar en rad används metoden fetchone, som returnerar raden som en tupel (eng. tuple).

Det andra SELECT-kommandot hämtar de tio första raderna från tabellen Stations. Här används metoden fetchall som returnerar en lista där varje tupel motsvarar en rad i resultattabellen.

Var finns databasfilen?

Ett vanligt problem i en kod som använder en databas är att databasfilen finns på en annan plats på datorn än vad koden förväntar sig. Om du inte får koden ovan att fungera är detta troligtvis orsaken.

Om den databasfil som koden hänvisar till inte finns, skapar koden en ny, tom databasfil. Eftersom det i denna databas inte finns någon tabell Stations kommer ett försök att hämta information från den tabellen att misslyckas.

Exempel: Stationens namn

Följande kod frågar användaren efter ett stations ID-nummer och hämtar därefter stationens namn från databasen.

import sqlite3

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

station_id = input("Stationens ID-nummmer: ")
result = db.execute("SELECT name FROM Stations WHERE id = ?", [station_id])
station_name = result.fetchone()[0]
print("Stationens namn:", station_name)

Resultatet kan se ut på följande sätt:

Stationens ID-nummer: 42
Stationens namn: Haapaniemenkatu

I frågan används parametern ?, vars värde blir ID-nummret i variabeln station_id. Koden visar hur värden för parametrar anges som en lista när metoden execute används.

I det här fallet returnerar fetchone en tupel med ett element. Innehållet i detta element hämtas med hjälp av syntaxen [], där 0 anger att det första elementet i tupeln ska hämtas.

En nackdel med koden är att den inte tar hänsyn till situationer där det i databasen inte finns någon station med det angivna ID-numret.

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

I det här fallet returnerar metoden fetchone värdet None, vilket gör att det inte går att hämta det första elementet i tupeln. För att lösa detta kan man använda en try/except-struktur på följande sätt:

import sqlite3

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

station_id = input("Stationens ID-nummer: ")
result = db.execute("SELECT name FROM Stations WHERE id = ?", [station_id])

try:
    station_name = result.fetchone()[0]
    print("Stationens namn:", station_name)
except TypeError:
    print("Stationen hittades inte")

Koden meddelar nu att stationen inte hittades:

Stationens ID-nummer: 666
Stationen hittades inte

Nedan utförs databassökningen via funktionen find_station_name. Funktionen returnerar stationens namn eller strängen -- om stationen inte hittas.

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("Stationens ID-nummer: ")

station_name = find_station_name(station_id)
print("Stationens namn:", station_name)

Koden fungerar på följande sätt:

Stationens ID-nummer: 42
Stationens namn: Haapaniemenkatu

Stationens ID-nummer: 666
Stationens namn: --

Exempel: Slutstationer

Följande kod frågar användaren efter en startstation och ett datum, och söker sedan upp alla slutstationer för det angivna datumet dit resor som började vid startstationen avslutades.

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("Stationens namn: ")
date = input("Datum: ")

destinations = find_destinations(station_name, date)
print("Antal slutstationer:", len(destinations))
for destination in destinations:
    print(destination)

Nedan följer ett exempel på hur koden fungerar:

Stationens namn: Syystie
Datum: 2024-05-16
Antal slutstationer: 5
A.I. Virtasen aukio
Ala-Malmin tori
Huhtakuja
Pukinmäen asema
Vanha Tapanilantie

Här är SQL-frågan rätt komplicerad, vilket är anledningen till att den delas upp på flera rader i koden. I detta fall är det praktiskt att använda Pythons syntax """ som gör det möjligt att definiera en sträng med flera rader.

Till kommandot ges två parametrar som placeras på tecknen ? i den ordning de anges i listan. Listans första element ersätter det första tecknet ? och det andra elementet ersätter det andra tecknet ?. Eftersom parametrarna är strängar placeras de inom '-tecken i SQL.

Cykelturer som börjar på ett visst datum kan hämtas med hjälp av syntaxen LIKE genom att begränsa sökningen så att värdet i kolumnen start_time börjar med det angivna datumet. Tecknet % anger att vilket som helst klockslag kan följa efter datumet.

SQLite i R

I R används SQLite-databaser vanligtvis med paketet RSQLite som kan installeras med kommandot install.packages("RSQLite"). Följande kod skapar en anslutning till databasen och hämtar information från tabellen Stations med SQL-frågor:

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)

Koden ger följande resultat:

  id      name
5 Sepänkatu

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

Här är db ett databasobjekt genom vilket SQL-kommandon kan köras med funktionen dbGetQuery. I den här koden körs två SELECT-kommandon.

Det första SELECT-kommandot hämtar raden med ID-numret 5 från tabellen Stations. Det andra SELECT-kommandot hämtar de tio första raderna från tabellen Stations.

Var finns databasfilen?

I RStudio kan katalogen anges via Session -> Set Working Directory -> To Source File Location. Därefter bör koden fungera om databasfilen finns i samma katalog som kodfilen.

Exempel: Stationens namn

Följande kod frågar användaren efter ett stations ID-nummer och hämtar därefter stationens namn från databasen.

library(RSQLite)

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

station_id <- readline(prompt = "Stationens ID-nummer: ")
result <- dbGetQuery(db,
                     "SELECT name FROM Stations WHERE id = ?",
                     params = list(station_id))

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

Resultatet kan se ut på följande sätt:

Stationens ID-nummer: 42
Stationens namn: Haapaniemenkatu

I frågan används parametern ?, vars värde hämtas från variabeln station_id. Koden visar hur värden för parametrar anges som en lista när funktionen dbGetQuery används.

Syntaxen result$name[1] betyder att värdet i kolumnen name hämtas från rad 1 i resultattabellen, vilket i det här fallet är den enda raden.

Om det inte finns någon station med det angivna ID-numret i databasen ger koden följande resultat (NA betyder att värdet saknas):

stationens ID-nummer: 666
Stationens namn: NA

Vi kan vid behov ge ett tydligare meddelande som svar genom att till exempel kontrollera antalet rader i resultatet:

library(RSQLite)

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

station_id <- readline(prompt = "Stationens ID-nummer: ")

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

if (nrow(result) == 1) {
  station_name <- result$name[1]
  cat("Stationens namn:", station_name, "\n")
} else {
  cat("Stationen hittades inte\n")
}

Nu fås ett tydligare svar där det meddelas att stationen inte kunde hittas:

Stationens ID-nummer: 666
Stationen hittades inte

Nedan utförs databassökningen via funktionen find_station_name. Funktionen returnerar stationens namn eller strängen -- om stationen inte hittas.

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 = "Stationens ID-nummer: ")
station_name <- find_station_name(station_id)
cat("Stationens namn:", station_name, "\n")

Koden fungerar på följande sätt:

Stationens ID-nummer: 42
Stationens namn: Haapaniemenkatu

Stationens ID-nummer: 666
Stationens namn: --

Exempel: Slutstationer

Följande kod frågar användaren efter en startstation och ett datum, och söker sedan upp alla slutstationer för det angivna datumet dit cykelturer som började vid startstationen avslutades.

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 = "Stationens namn: ")
date <- readline(prompt = "Datum: ")

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

Nedan följer ett exempel på hur koden fungerar:

Stationens namn: Syystie
Datum: 2024-05-16
Antal slutstationer: 5
A.I. Virtasen aukio
Ala-Malmin tori
Huhtakuja
Pukinmäen asema
Vanha Tapanilantie

Cykelturer som börjar på ett visst datum kan hittas med hjälp av LIKE-syntaxen genom att begränsa sökningen så att värdet i kolumnen start_time börjar med det angivna datumet. Funktionen paste0 lägger till tecknet % efter datumet, vilket anger att vilket som helst klockslag kan följa efter datumet.

Vad gör man var?

I databasen och i koden kan man ofta göra liknande saker. Nedan presenteras två olika sätt att söka fram den längsta cykelturen ur databasen:

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)

I den första lösningen hämtar man den längsta cykelturen direkt i databasen med SQL-funktionen MAX. I den andra lösningen samlar man längden på alla cykelturer från databasen till en lista och söker sedan den längsta turen i koden med Pythons max-funktion.

Av de två metoderna är den första klart bättre. Det lönar sig inte att hämta onödig information och behandla den i koden när samma sak kan göras enkelt direkt i databasen.

Det lönar sig särskilt att undvika situationer där man i onödan kör flera SQL-kommandon när ett enda kommando skulle räcka. Nedan finns ett dåligt exempel på hur man hämtar antalet cykelturer som startat från varje station ur databasen.

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)

Koden samlar först in varje stations ID-nummer och namn till en lista. Därefter hämtar koden i en loop, för varje station separat, antalet cykelturer som startade från just den stationen. Koden fungerar i och för sig, men den gör nu en massa onödigt arbete genom att hämta varje antal turer med en egen fråga. En bättre lösning är att skapa en enda fråga som direkt hämtar all nödvändig information:

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)

Den uppdaterade frågan är visserligen mer komplicerad än den tidigare, men den gör det nu möjligt för databashanteraren att optimera hämtningen av datan som en helhet och leverera den så effektivt som möjligt till koden.

Databasernas grunder

vår 2026

5. Databaser i programmering

Testdatabas

SQLite i Python

Exempel: Stationens namn

Exempel: Slutstationer

SQLite i R

Exempel: Stationens namn

Exempel: Slutstationer

Vad gör man var?