Python ja tietokannat, SQLite ja PostgreSQL

Tällä luennolla käsitellään tietokantoja Pythonin näkökulmasta. Pääosa luennosta käyttää SQLiteä; lopussa käsitellään PostgreSQL, yhteyspooli ja Cloud SQL.

• Mallitietokanta: resepti.sql, PostgreSQL-versio resepti-pg.sql
• Esimerkkisovellus videorekisteri: sovellus, lähdekoodi, template1, template2, video.sql
• Komentoriviltä suoritettava esimerkki: db.py (lähdekoodi)
• Käytännön ohjeet: SQLite-pääteohjaus

Yleistä tietokannoista

Tällä kurssilla oletetaan, että tietokannat ja niihin liittyvät SQL-lauseet ovat tuttuja jo esitietokursseilta. Jos kyseiset asiat eivät ole kovin hyvin hallussa, itseopiskelua voi harrastaa esimerkiksi näiden kurssien materiaaleilla:

• Tietokannat ja tiedonhallinnan perusteet
• Tietokannat

Käsiteltäessä tietokantoja on aina hyvä pitää mielessä se perussääntö, että annetaan tietokannan tehdä eri operaatiot mahdollisimman pitkälle. Viite-eheydet, rajoitteet ja lajittelut kannattaa aina tehdä suoraan kannassa, jos mahdollista. Myös haut kannattaa rajata mahdollisimman tarkkaan jo SQL-lausetta muodostettaessa. Kannassa operoiminen on merkittävästi tehokkaampaa kuin vastaavien asioiden tekeminen ohjelmakoodin puolella, ja se on myös vähemmän virhealtista.

Lisäksi kannattaa muistaa, että jokainen tietokantakutsu vie oman aikansa. Älä tee samaa kyselyä useaan kertaan samalla sivulla äläkä päivitystä, jos ei ole mitään päivitettävää.

SQLite-tietokanta

SQLite on pieni, tehokas ja ehkä maailman käytetyin tietokanta. SQLiten erikoisuuksia:

• Koko tietokanta on yhdessä tiedostossa
• Tietokantatiedoston muoto on ympäristöriippumaton
• Tietokantatiedostoja voi olla useita. Esimerkiksi jokaisella käyttäjällä voi olla oma tiedosto
• Ei tarvitse erillistä palvelinta tai ylläpitoa
• Erittäin nopea, jos tietokanta on paikallisella levyllä. Verkkolevyltä käyttäminen ei ole suositeltavaa
• Toimii erinomaisesti ohjelmistojen tallennusmuotona: esimerkiksi Firefox, Chrome, Windows ja GeoPackage käyttävät sitä
• Toimii erittäin hyvin web-sovellusten tietokantana
• Tietokantaa voi lukea rajoittamaton määrä yhtä aikaa, mutta kirjoittaa voi vain yksi kerrallaan. Kirjoitusoperaatiot ovat nopeita, jos levy on nopea
• SQLite on löysä tietotyyppien suhteen. Uusimmissa versioissa voidaan käyttää myös tiukkoja tietotyyppejä, kts. STRICT tables
• Viite-eheystarkistukset ovat oletuksena pois päältä

Python Database API Specification v2.0 (PEP 249)

PEP 249 määrittelee rajapinnan, jolla tietokantoja käsitellään. Kannattaa käyttää vain PEP 249 -rajapinnan toteuttavia kirjastoja, jolloin ohjelman siirrettävyys toiseen tietokantaan on parhaiten turvattu.

sqlite3-kirjasto on osa Pythonin vakiokirjastoa, joten sitä ei tarvitse asentaa erikseen. Se on mukana myös users.jyu.fi-palvelimen Pythonissa.

Muut tietokannat vaativat omat ajurinsa, esimerkiksi:

Tietokannan sijainti ja oikeudet

Tietokantaa voi käsitellä komentoriviltä sqlite3-komennolla, joka avaa suoran komentorivikäyttöliittymän. Mallitietokannan saa luotua ja WAL-lokituksen (Write-Ahead Logging) päälle näin:

sqlite3 resepti.db

.read resepti.sql
PRAGMA journal_mode = wal;

Tietokantayhteys

Yhteys avataan connect-funktiolla. Samalla kannattaa asettaa row_factory, jolloin kyselyn tulosriveiltä voi hakea arvoja sarakkeen nimellä indeksin sijaan, ja kytkeä viite-eheystarkistukset päälle.

import os
import sqlite3

#Polku johdetaan tämän tiedoston sijainnista. Älä käytä pelkkää suhteellista
#polkua tai os.path.abspath-funktiota: CGI-prosessin työhakemisto ei
#välttämättä ole se, mitä oletat, jolloin kantaa ei löydy tai syntyy vahingossa
#uusi tyhjä kanta väärään paikkaan.
KANTA = os.path.join(os.path.dirname(os.path.abspath(__file__)),
                     "..", "hidden", "resepti.db")

def avaa_yhteys():
    yhteys = sqlite3.connect(KANTA)
    #sarakkeisiin voi viitata nimellä: rivi["Nimi"]
    yhteys.row_factory = sqlite3.Row
    #viite-eheystarkistukset päälle, yhteyskohtainen asetus
    yhteys.execute("PRAGMA foreign_keys = ON")
    return yhteys

Yhteyden avaaminen ei aina onnistu, joten se kannattaa ympäröidä try-lohkolla. Yhteys on myös aina suljettava. Molemmat hoituvat siististi contextlib.closing-apurilla:

import logging
from contextlib import closing

try:
    with closing(avaa_yhteys()) as yhteys:
        for rivi in yhteys.execute("SELECT Nimi FROM Resepti"):
            print(rivi["Nimi"])
except sqlite3.Error:
    #exception kirjaa lokiin myös pinolistauksen
    logging.exception("Tietokantaa ei saatu auki")

try:
    yhteys = sqlite3.connect(KANTA)
except Exception as virhe:
    logging.debug("Kanta ei aukea")
finally:
    yhteys.close()          #NameError, jos yhteyden avaaminen epäonnistui

Kyselyt

Yhteyden avaamisen jälkeen kantaan voidaan tehdä kyselyjä. Kysely palauttaa kursorin, jonka voi käydä läpi silmukassa:

kursori = yhteys.execute("""
    SELECT ReseptiID, Nimi, Kuvaus, Henkilomaara
    FROM Resepti
    ORDER BY Nimi
""")

for rivi in kursori:
    print(rivi["Nimi"], rivi["Henkilomaara"])

Kun row_factory on asetettu, rivit ovat sqlite3.Row-olioita. Niistä voi hakea arvon sekä nimellä että indeksillä, ne voi muuntaa sanakirjaksi funktiolla dict(rivi), ja sarakkeiden nimet saa metodilla rivi.keys(). Ilman row_factory-asetusta rivit ovat tavallisia tupleja.

Kursorin sijasta tulokset voi hakea kerralla listaksi metodilla fetchall, yhden rivin metodilla fetchone ja tietyn määrän metodilla fetchmany. Kursorin voi käydä läpi vain kerran; jos tulokset tarvitaan uudelleen, kysely on tehtävä uudestaan tai tulokset otettava talteen listaan.

#yksi rivi tai None, jos tulosta ei ole
rivi = yhteys.execute("SELECT * FROM Resepti WHERE ReseptiID = ?", (1,)).fetchone()
if rivi is not None:
    print(dict(rivi))

Kyselyn parametrit

#ÄLÄ TEE NÄIN
kursori.execute("SELECT * FROM Resepti WHERE Nimi = '%s'" % nimi)
kursori.execute(f"SELECT * FROM Resepti WHERE Nimi = '{nimi}'")

Kyselyparametrin merkintätapoja (englanniksi placeholder) on kaksi. Kysymysmerkki viittaa järjestykseen ja :nimi sanakirjan avaimeen:

nimi = "Porkkanapiirakka"

#Huomaa pilkku: (nimi,) on yhden alkion tuple, (nimi) on pelkkä merkkijono
kursori = yhteys.execute("""
    SELECT ReseptiID, Nimi, Henkilomaara
    FROM Resepti
    WHERE Nimi = ?
""", (nimi,))

for rivi in kursori:
    print(dict(rivi))

ehdot = {"eka": "Porkkanapiirakka", "toka": "Rommikakku"}

kursori = yhteys.execute("""
    SELECT ReseptiID, Nimi, Henkilomaara
    FROM Resepti
    WHERE Nimi = :eka OR Nimi = :toka
    ORDER BY Nimi
""", ehdot)

for rivi in kursori:
    for sarake in rivi.keys():
        print(f"{sarake}:\t{rivi[sarake]}")

Kyselyparametrin voi sijoittaa vain arvon paikalle. Taulun tai sarakkeen nimeä ei voi antaa parametrina — jos ne pitää valita ohjelmallisesti, valinta on tehtävä omasta sallittujen nimien luettelosta, ei käyttäjän syötteestä.

Pythonin sqlite3 valmistelee lauseet sisäisesti ja säilyttää valmistellut lauseet välimuistissa, joten sama kysely suoritetaan seuraavilla kerroilla nopeammin. Erillistä prepare-rajapintaa ei ole eikä tarvita: parametrisoitu execute tekee saman asian.

Lajittelu, kirjainkoko ja ääkköset

Lajittelu ja vertailu kuuluvat tietokannalle, eivät Python-koodille. SQLitessä on kuitenkin yksi merkittävä rajoitus, joka tulee suomenkielisessä aineistossa heti vastaan: sen sisäänrakennetut merkkijonofunktiot osaavat vain ASCII-merkit.

ORDER BY nimi lajittelee tavujärjestyksessä, jolloin isot kirjaimet tulevat ennen pieniä ja ääkköset kaikkien muiden jälkeen. SQLiten mukana tulee kolme vertailutapaa: BINARY (oletus), NOCASE ja RTRIM. Niistä NOCASE jättää kirjainkoon huomiotta — mutta vain ASCII-kirjaimille:

SELECT nimi FROM t ORDER BY nimi COLLATE NOCASE;

[' Zulu', 'aalto', 'ossi', 'Ähtäri', 'Ääliö', 'Öljy']

Järjestys ei ole se, jota käyttäjä odottaa: alussa oleva välilyönti nostaa Zulu-rivin ensimmäiseksi, ja ääkkösellä alkavat nimet jäävät kaikkien muiden perään. Sama rajoitus koskee UPPER- ja LOWER-funktioita:

SELECT UPPER(nimi) FROM t;

['ÄHTäRI', 'AALTO', ' ZULU', 'ÖLJY', 'OSSI', 'ÄäLIö']

Ä ja ö jäivät muuttamatta. Myös LIKE-vertailu on kirjainkokoriippumaton vain ASCII-merkeille: 'Aalto' LIKE 'aalto' on tosi, mutta 'Ähtäri' LIKE 'ähtäri' on epätosi.

Oma vertailufunktio

Pythonin sqlite3 antaa määritellä yhteyskohtaisen vertailufunktion metodilla create_collation. Vertailu tehdään silloin Pythonissa, joka osaa ääkköset:

def suomi(eka, toka):
    """Palauttaa -1, 0 tai 1 kuten vertailufunktiolta odotetaan."""
    eka, toka = eka.strip().lower(), toka.strip().lower()
    return (eka > toka) - (eka < toka)

yhteys.create_collation("suomi", suomi)

SELECT nimi FROM t ORDER BY nimi COLLATE suomi;

Toinen tapa on rekisteröidä oma funktio create_function-metodilla ja käyttää sitä kyselyssä:

yhteys.create_function("lower_fi", 1, lambda arvo: arvo.lower() if arvo else arvo)

SELECT nimi FROM t ORDER BY lower_fi(TRIM(nimi));

Molemmat tuottavat saman, oikean järjestyksen:

['aalto', 'ossi', ' Zulu', 'Ähtäri', 'Ääliö', 'Öljy']

Transaktiot

Transaktioiden avulla hallitaan tietokantaoperaatioiden kokonaisuuksia. Jos kantaan pitää syöttää useita tietueita peräkkäin ja yksikin lisäys epäonnistuu, kaikki lisäykset pitää pystyä peruuttamaan (vrt. verkkopankki). Transaktio varmistaa myös, etteivät keskeneräisen transaktion vaikutukset näy muille käyttäjille ennen kuin transaktio on hyväksytty.

SQLite on oletuksena autocommit-tilassa: jokainen lause on oma transaktionsa. Pythonin sqlite3-kirjasto kuitenkin aloittaa oletusarvoisesti implisiittisen transaktion ennen tietoa muuttavia lauseita, joten ilman commit-kutsua muutokset eivät jää voimaan.

Näin se tehdään

Alla on kokonainen esimerkki, jossa avataan yhteys, tehdään yksi lisäys ja suljetaan yhteys. Tämä on se tapa, jota kannattaa käyttää — muut vaihtoehdot esitellään lopuksi lähinnä siksi, että niitä näkee vanhoissa esimerkeissä.

import logging
import os
import sqlite3
from contextlib import closing

KANTA = os.path.join(os.path.dirname(os.path.abspath(__file__)),
                     "..", "hidden", "resepti.db")

try:
    #closing sulkee yhteyden, kun lohkosta poistutaan
    with closing(sqlite3.connect(KANTA)) as yhteys:
        yhteys.row_factory = sqlite3.Row
        yhteys.execute("PRAGMA foreign_keys = ON")

        #with yhteys hoitaa transaktion: commit lopuksi, rollback poikkeuksesta
        with yhteys:
            yhteys.execute("""
                INSERT INTO Resepti (Nimi, Kuvaus, Henkilomaara, RuokalajiID)
                VALUES (?, ?, ?, ?)
            """, ("Talkkunahyve", "perinteinen jälkiruoka", 4, 7))

except sqlite3.Error:
    logging.exception("Tietokantaoperaatio epäonnistui")

Useampi lause samassa transaktiossa

Transaktion idea tulee esiin vasta, kun lauseita on useampi. Jos toinen epäonnistuu, myös ensimmäinen perutaan:

try:
    with yhteys:
        kursori = yhteys.execute("""
            INSERT INTO Resepti (Nimi, Kuvaus, Henkilomaara, RuokalajiID)
            VALUES (:nimi, :kuvaus, :henkilomaara, :ruokalaji)
        """, {"nimi": "Talkkunahyve", "kuvaus": "perinteinen jälkiruoka",
              "henkilomaara": 4, "ruokalaji": 7})

        #lastrowid toimii INSERT-lauseen jälkeen ja palauttaa rivin rowid-arvon.
        #Se vastaa perusavainta vain, jos sarake on määritelty INTEGER PRIMARY KEY.
        uusi_id = kursori.lastrowid

        yhteys.execute("""
            UPDATE Resepti
            SET Nimi = ?
            WHERE ReseptiID = ?
        """, ("Puolukkahyve", uusi_id))

except sqlite3.Error:
    #with-lohko on jo perunut molemmat muutokset
    logging.exception("Lisäys epäonnistui, muutokset peruttiin")

Muita tapoja, joita näkee vanhoissa esimerkeissä

Transaktion voi hoitaa myös käsin: yhteys.commit() hyväksyy ja yhteys.rollback() peruu. Silloin vastuu on kokonaan itsellä, ja tavallisin virhe on se, että suoritus jatkuu virheen jälkeen kuin mitään ei olisi tapahtunut. Alla epäonnistuneen lisäyksen jälkeen edetään silti seuraavaan lauseeseen ja päivitetään väärä rivi:

#ÄLÄ TEE NÄIN
try:
    kursori.execute("INSERT INTO Resepti ...")
except sqlite3.Error:
    yhteys.rollback()
#suoritus jatkuu tänne myös virhetilanteessa
uusi_id = kursori.lastrowid

sqlite3.OperationalError: cannot start a transaction within a transaction

Yhteyttä avattaessa näkee usein muodon sqlite3.connect(..., isolation_level=None), joka kytkee kirjaston implisiittiset transaktiot pois päältä. Python 3.12 toi connect-funktiolle autocommit-parametrin, joka noudattaa PEP 249:ää ja korvaa vanhan isolation_level-parametrin. Molemmat toimivat, mutta kumpaakaan ei tarvita, jos transaktiot hoidetaan with-lohkolla kuten edellä.

• SQL As Understood By SQLite: BEGIN TRANSACTION
• Controlling transactions
• ROWIDs and the INTEGER PRIMARY KEY

Tietokanta Flask-sovelluksessa

Web-sovelluksessa yhteyttä ei avata ja suljeta jokaisen kyselyn ympärillä, vaan kerran pyyntöä kohti. Flaskissa yhteys säilytetään g-oliossa ja suljetaan pyynnön päätteeksi:

import sqlite3
from flask import Flask, g

app = Flask(__name__)

def kanta():
    """Palauttaa tämän pyynnön tietokantayhteyden ja avaa sen tarvittaessa."""
    if "kanta" not in g:
        g.kanta = sqlite3.connect(KANTA)
        g.kanta.row_factory = sqlite3.Row
        g.kanta.execute("PRAGMA foreign_keys = ON")
    return g.kanta

@app.teardown_appcontext
def sulje_kanta(poikkeus):
    yhteys = g.pop("kanta", None)
    if yhteys is not None:
        yhteys.close()

@app.route("/reseptit")
def reseptit():
    rivit = kanta().execute("""
        SELECT ReseptiID, Nimi, Henkilomaara
        FROM Resepti
        ORDER BY Nimi
    """).fetchall()
    return render_template("reseptit.html", reseptit=rivit)

Huomaa, että näkymäfunktio vain hakee tiedot ja antaa ne templatelle. SQL-lauseet kuuluvat mallin puolelle, eivät templateen. Kts. MVC-arkkitehtuuri.

PostgreSQL

PostgreSQL on avoimen lähdekoodin relaatiotietokanta, joka toimii erillisenä palvelimena. Sovellus ei avaa tiedostoa kuten SQLitessä, vaan ottaa verkon yli yhteyden tietokantapalvelimeen, tunnistautuu ja lähettää sille SQL-lauseet. Samaan tietokantaan voi siksi olla yhteydessä monta sovellusta ja monta palvelinta yhtä aikaa.

Tästä syystä PostgreSQL on luonteva valinta pilvipalveluun. Cloud Run -palvelussa sovelluksella ei ole pysyvää levyä, ja samaa sovellusta voi ajaa yhtä aikaa usea instanssi: SQLite-tiedosto katoaisi instanssin mukana, eikä sitä voisi jakaa instanssien kesken. Pienissä ja yhden palvelimen sovelluksissa SQLite on edelleen erinomainen valinta.

Tärkeimmät erot SQLiteen

Muuten käyttö on hyvin samanlaista, koska PostgreSQL-ajuri psycopg 3 noudattaa samaa PEP 249 -rajapintaa kuin sqlite3:

pip install "psycopg[binary]" psycopg_pool

import psycopg
from psycopg.rows import dict_row

with psycopg.connect("host=127.0.0.1 dbname=resepti user=opiskelija password=...",
                     row_factory=dict_row) as yhteys:
    rivi = yhteys.execute(
        "SELECT ReseptiID, Nimi FROM Resepti WHERE Nimi = %s", ("Tiramisu",)
    ).fetchone()
    print(rivi)             # {'reseptiid': 2, 'nimi': 'Tiramisu'}

Esimerkissä näkyy kolme eroa:

• Parametrin merkintä on %s, ei ?. Nimetyt parametrit kirjoitetaan %(nimi)s. Merkintä ei ole Pythonin merkkijonomuotoilua: arvot annetaan edelleen erillisenä parametrina, eikä niitä saa yhdistää SQL-lauseeseen itse. Kts. Kyselyn parametrit.
• row_factory=dict_row vastaa SQLiten sqlite3.Row-asetusta: rivit ovat sanakirjoja.
• Sarakkeiden nimet ovat pienaakkosin, vaikka kyselyssä luki ReseptiID. PostgreSQL muuttaa lainaamattomat nimet pienaakkosiksi, ja toisin kuin sqlite3.Row, sanakirja on kirjainkokoherkkä: rivi["ReseptiID"] aiheuttaa KeyError-virheen. Kirjoita nimet pienaakkosin tai anna sarakkeille nimet AS-määreellä.

Mallitietokanta PostgreSQL:lle

Mallitietokannan PostgreSQL-versio on tiedostossa resepti-pg.sql. Juokseva perusavain ja viiteavain kirjoitetaan näin:

CREATE TABLE Resepti (
    ReseptiID    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Nimi         VARCHAR(100) NOT NULL,
    Kuvaus       VARCHAR(250) DEFAULT '-',
    Henkilomaara INTEGER DEFAULT 2 CHECK (Henkilomaara > 0),
    RuokalajiID  INTEGER NOT NULL REFERENCES Ruokalaji (RuokalajiID)
);

SQLite-version triggereitä ei tarvita, koska PostgreSQL valvoo viite-eheyttä aina. GENERATED ALWAYS estää myös antamasta avainta itse, jolloin kaksi samaan aikaan tehtyä lisäystä ei voi saada samaa tunnistetta.

Uuden rivin tunniste

psycopg ei tarjoa lastrowid-ominaisuutta. Uuden rivin tunniste kysytään RETURNING-lauseella, joka palauttaa lisätyn rivin sarakkeet samalla kertaa:

uusi = yhteys.execute("""
    INSERT INTO Resepti (Nimi, Kuvaus, Henkilomaara, RuokalajiID)
    VALUES (%s, %s, %s, %s)
    RETURNING ReseptiID
""", ("Talkkunahyve", "perinteinen jälkiruoka", 4, 5)).fetchone()

print(uusi["reseptiid"])

Tiukat tyypit ja virheet

SQLite tallentaisi tekstin "neljä" kokonaislukusarakkeeseen. PostgreSQL hylkää sen, ja virheet ovat omia luokkiaan, jotka voi napata psycopg.Error-yliluokalla:

psycopg.errors.InvalidTextRepresentation: invalid input syntax for type integer: "neljä"
psycopg.errors.ForeignKeyViolation: insert or update on table "resepti" violates foreign key constraint

Lisää tai päivitä

Usein halutaan lisätä rivi tai päivittää sitä, jos se on jo olemassa, esimerkiksi kun kirjataan käyttäjän katsomia paikkoja. Tähän on ON CONFLICT-lause (sama toimii myös uusissa SQLite-versioissa):

INSERT INTO Katsottu (Kayttaja, Paikka) VALUES (%s, %s)
ON CONFLICT (Kayttaja, Paikka)
DO UPDATE SET Kerrat = Katsottu.Kerrat + 1, Viimeksi = now();

Lajittelu ja ääkköset

PostgreSQL:n merkkijonofunktiot osaavat kaikki merkit, joten UPPER('Ähtäri') on ÄHTÄRI ja 'Ähtäri' ILIKE 'ähtäri' on tosi. Lajittelujärjestys riippuu tietokannan kollaatiosta. Suomalaisen järjestyksen saa ICU-kollaatiolla:

SELECT nimi FROM t ORDER BY TRIM(nimi) COLLATE "fi-x-icu";

C-kollaatio:  [' Zulu', 'Aalto', 'bertta', 'zeta', 'Ähtäri', 'Åland', 'Öljy']
fi-x-icu:     ['Aalto', 'bertta', 'zeta', ' Zulu', 'Åland', 'Ähtäri', 'Öljy']

C-kollaatio lajittelee merkkikoodien mukaan, jolloin isot kirjaimet tulevat ennen pieniä ja Ä ennen Å:ta. ICU-kollaatio noudattaa suomen aakkosjärjestystä. Käytettävissä olevat kollaatiot näkee kyselyllä SELECT collname FROM pg_collation WHERE collname LIKE 'fi%';

Yhteyspooli

PostgreSQL-yhteyden avaaminen on raskas toimenpide: sovellus muodostaa verkkoyhteyden, neuvottelee salauksen ja tunnistautuu, ja palvelin käynnistää jokaiselle yhteydelle oman prosessin. Jos jokainen HTTP-pyyntö avaa ja sulkee oman yhteytensä, suuri osa pyynnön ajasta kuluu pelkkään yhteyden avaamiseen.

Yhteyspooli (engl. connection pool) pitää joukon yhteyksiä valmiiksi auki. Kun sovellus tarvitsee yhteyden, se lainaa sen poolista, ja kun työ on tehty, yhteys palautetaan poolin seuraavan käyttäjän käyttöön sen sijaan, että se suljettaisiin. Poolista on kaksi hyötyä:

• Nopeus. Kun sama kysely tehtiin 200 kertaa samalla koneella olevaan tietokantaan, uuden yhteyden avaaminen joka kerta kesti keskimäärin 3,3 ms, poolista lainattuna 0,2 ms. Pilvessä tietokanta on verkon takana, joten ero on vielä suurempi.
• Yhteyksien määrän hallinta. Tietokantapalvelin sallii vain rajallisen määrän yhteyksiä (SHOW max_connections;). Poolin enimmäiskoko rajaa sen, montako yhteyttä yksi sovellusprosessi voi enintään avata. Jos kaikki yhteydet ovat käytössä, seuraava pyyntö odottaa vuoroaan eikä kaada tietokantaa.

psycopg 3:n pooli on paketissa psycopg_pool. Pooli luodaan kerran sovelluksen käynnistyessä, ei jokaisessa pyynnössä. Seuraava moduuli lukee yhteystiedot ympäristömuuttujista, jolloin salasana ei päädy koodiin eikä versionhallintaan:

"""PostgreSQL-yhteyspooli. Asetukset luetaan ympäristömuuttujista."""

import atexit
import os

from psycopg.conninfo import make_conninfo
from psycopg.rows import dict_row
from psycopg_pool import ConnectionPool


def yhteystiedot():
    # Cloud Runissa Cloud SQL näkyy Unix-sokettina hakemistossa /cloudsql/.
    # Omalla koneella yhteys otetaan TCP:llä, esim. Cloud SQL Auth Proxyn kautta.
    instanssi = os.environ.get("CLOUD_SQL_YHTEYS")      # projekti:alue:instanssi
    palvelin = f"/cloudsql/{instanssi}" if instanssi else os.environ.get("DB_HOST", "127.0.0.1")
    return make_conninfo(
        host=palvelin,
        port=os.environ.get("DB_PORT", "5432"),
        dbname=os.environ["DB_NIMI"],
        user=os.environ["DB_KAYTTAJA"],
        password=os.environ["DB_SALASANA"],
    )


pooli = ConnectionPool(
    yhteystiedot(),
    kwargs={"row_factory": dict_row},
    min_size=1,
    max_size=int(os.environ.get("POOLIN_KOKO", "5")),
    check=ConnectionPool.check_connection,
    open=True,
)
atexit.register(pooli.close)

• make_conninfo muodostaa yhteysmerkkijonon ja hoitaa erikoismerkit, jos niitä on esimerkiksi salasanassa.
• min_size ja max_size määräävät, montako yhteyttä pidetään vähintään auki ja montako avataan enintään.
• check tarkistaa lainattavan yhteyden ennen käyttöä. Yhteys voi katketa esimerkiksi tietokannan huoltokatkon aikana, ja ilman tarkistusta ensimmäinen pyyntö katkoksen jälkeen epäonnistuisi.
• atexit sulkee yhteydet siististi, kun prosessi päättyy.

Flask-näkymä lainaa yhteyden with-lohkolla. Lohkon lopussa transaktio hyväksytään (poikkeuksessa perutaan) ja yhteys palautetaan pooliin:

from flask import Flask, render_template
from tietokanta import pooli

app = Flask(__name__)


@app.get("/reseptit")
def reseptit():
    with pooli.connection() as yhteys:
        rivit = yhteys.execute("""
            SELECT ReseptiID, Nimi, Henkilomaara
            FROM Resepti
            ORDER BY Nimi COLLATE "fi-x-icu"
        """).fetchall()
    return render_template("reseptit.html", reseptit=rivit)

Poolin koko

Jokaisella sovellusprosessilla on oma poolinsa. Yhteyksiä voi siis olla enintään

instansseja × prosesseja instanssissa × max_size

ja tämän on oltava pienempi kuin tietokannan max_connections, josta osa on varattu ylläpidolle. Pienessä Cloud SQL -instanssissa yhteyksiä on vähän, joten max_size pidetään pienenä (esimerkiksi 2–5) ja Cloud Run -palvelun instanssien enimmäismäärä rajataan (--max-instances). Cloud Run sallii lisäksi enintään 100 tietokantayhteyttä instanssia kohti.

Pooli ei auta CGI-ympäristössä, jossa jokainen pyyntö käynnistää uuden prosessin: pooli syntyisi ja kuolisi pyynnön mukana. Kts. CGI-ympäristö.

Cloud SQL

Cloud SQL on Googlen hallinnoima tietokantapalvelu. Google huolehtii palvelimesta, päivityksistä ja varmuuskopioista, ja sovellus käyttää tietokantaa tavallisena PostgreSQL-tietokantana. Cloud Run -palvelu saa yhteyden Cloud SQL -instanssiin Unix-soketin kautta hakemistossa /cloudsql/, eikä tietokantaa tarvitse avata julkiseen verkkoon. Edellä esitetty tietokanta.py käyttää tätä soketia, kun ympäristömuuttuja CLOUD_SQL_YHTEYS on asetettu.

Instanssin ja tietokannan luominen

# pienin ja edullisin instanssi; jaetun ytimen koneet vaativat Enterprise-version
gcloud sql instances create vt6 \
    --database-version=POSTGRES_17 --edition=enterprise \
    --tier=db-f1-micro --region=europe-north1

gcloud sql databases create vt6 --instance=vt6
gcloud sql users create vt6 --instance=vt6 --password="$(openssl rand -base64 24)"

Instanssin yhteysnimi on muotoa projekti:alue:instanssi, esimerkiksi oma-projekti:europe-north1:vt6. Sen näkee komennolla gcloud sql instances describe vt6 --format="value(connectionName)".

Salasana Secret Manageriin

Salasana tallennetaan Secret Manageriin, josta Cloud Run antaa sen sovellukselle ympäristömuuttujana. Salasanaa ei kirjoiteta koodiin, asetustiedostoon eikä versionhallintaan. Palvelutilille annetaan oikeudet sekä salaisuuden lukemiseen että Cloud SQL -yhteyteen:

printf '%s' "SALASANA" | gcloud secrets create vt6-db-salasana --data-file=-

gcloud projects add-iam-policy-binding oma-projekti \
    --member="serviceAccount:PALVELUTILI" --role="roles/cloudsql.client"
gcloud secrets add-iam-policy-binding vt6-db-salasana \
    --member="serviceAccount:PALVELUTILI" --role="roles/secretmanager.secretAccessor"

Sovelluksen julkaisu

gcloud run deploy vt6 --source . --region=europe-north1 \
    --add-cloudsql-instances=oma-projekti:europe-north1:vt6 \
    --set-env-vars=CLOUD_SQL_YHTEYS=oma-projekti:europe-north1:vt6,DB_NIMI=vt6,DB_KAYTTAJA=vt6 \
    --set-secrets=DB_SALASANA=vt6-db-salasana:latest \
    --max-instances=3

Kehitys omalla koneella

Omalta koneelta tietokantaan pääsee Cloud SQL Auth Proxyn kautta. Proxy avaa paikallisen portin ja välittää yhteyden salattuna Cloud SQL -instanssiin. Sovellus käyttää silloin TCP-yhteyttä, eikä CLOUD_SQL_YHTEYS-muuttujaa aseteta:

gcloud auth application-default login
./cloud-sql-proxy --port 5432 oma-projekti:europe-north1:vt6

# toisessa ikkunassa
DB_HOST=127.0.0.1 DB_NIMI=vt6 DB_KAYTTAJA=vt6 DB_SALASANA=... flask --app sovellus run

Taulut voi luoda samalla yhteydellä esimerkiksi komennolla psql -h 127.0.0.1 -U vt6 -d vt6 -f resepti-pg.sql.

gcloud sql instances patch vt6 --activation-policy=never     # pysäytä
gcloud sql instances patch vt6 --activation-policy=always    # käynnistä
gcloud sql instances delete vt6                              # poista

Tarvitsetko oikeasti tietokantaa?

Mieti aina tarkkaan, onko tietokannasta varmasti hyötyä vai riittäisikö esimerkiksi pelkkä JSON-tiedosto. Onko tarpeen tehdä kyselyjä? Onko tietoja päivitettävä usein? Onko kirjoittajia monta yhtä aikaa?

Kannattaa lukea Firefoxin kehittäjien kommentit SQLiten käyttämisestä: Performance/Avoid SQLite In Your Next Firefox Feature.

Lisätietoa

• sqlite3 — DB-API 2.0 interface for SQLite databases
• PEP 249 — Python Database API Specification v2.0
• SQLite ja SQLite Foreign Key Support
• Write-Ahead Logging
• PostgreSQL-dokumentaatio ja psycopg 3
• psycopg 3: Connection pools
• Connect from Cloud Run to Cloud SQL for PostgreSQL
• Cloud SQL Auth Proxy
• OWASP: SQL Injection
• Going Fast with SQLite and Python
• Multi-threaded SQLite without the OperationalErrors
• consider sqlite
• SQLite: the only database you will ever need in most cases
• Flask-luento ja MVC-arkkitehtuuri