Sessiot, autentikointi ja relaatiotietokannat

Harjoitellaan sessioiden käyttöä www-sovelluksessa, lisätään sovellukseen kirjautuminen ja tutustutaan relaatiotietokantoihin.

Tehtävän apuna kannattaa käyttää seuraavia dokumentteja:

• Evästeet ja sessiot
• Python, tietokannat ja SQLite
• Flask ja MVC-arkkitehtuuri
• sqlite3 — DB-API 2.0 interface for SQLite databases

Laskuri sessioilla

Etsi edellisessä pääteohjauksessa tekemäsi laskurisovellus. Korjataan laskuri toimimaan kunnolla sessioiden avulla: ohjelman pitää muistaa aiemmin saatu summa.

• Lisää ohjelmakoodisi alkuun:

  from flask import session

• Sessio tarvitsee salaisen avaimen, jolla sessioeväste allekirjoitetaan. Älä kirjoita avainta lähdekoodiin äläkä versionhallintaan. Avaimen tietävä voi väärentää allekirjoituksen ja kirjautua kenä tahansa.

  users.jyu.fi-palvelimella ei voi asettaa omia ympäristömuuttujia, joten avain tallennetaan tiedostoon www-hakemiston ulkopuolelle — samaan hidden-kansioon tietokannan kanssa. Luo tiedosto kerran ja rajaa sen oikeudet:

  python3 -c "import secrets; print(secrets.token_hex(32))" > ~/public_html/hidden/avain.txt
  chmod 600 ~/public_html/hidden/avain.txt

  Sovellus lukee avaimen käynnistyessään:

  import os
  
  AVAINTIEDOSTO = os.path.join(os.path.dirname(os.path.abspath(__file__)),
                               "..", "..", "..", "hidden", "avain.txt")
  
  def lue_salainen_avain(polku):
      try:
          with open(polku, encoding="UTF-8") as tiedosto:
              avain = tiedosto.read().strip()
      except OSError as virhe:
          raise RuntimeError(f"Salaista avainta ei saada luettua: {polku}") from virhe
  
      if not avain:
          raise RuntimeError(f"Avaintiedosto {polku} on tyhjä.")
  
      return avain
  
  app.secret_key = lue_salainen_avain(AVAINTIEDOSTO)

  Polku johdetaan tiedoston sijainnista eikä työhakemistosta, koska CGI-prosessin työhakemisto ei välttämättä ole se, mitä oletat. Sama ratkaisu toimii sellaisenaan myös omalla koneella ja PythonAnywheressä.

  Salaisen avaimen on säilyttävä samana suorituskertojen välillä. Jos se vaihtuu, kaikki voimassa olevat sessiot mitätöityvät.

• Aseta myös sessioevästeen turva-asetukset:

  app.config.update(
      SESSION_COOKIE_SAMESITE="Lax",     # torjuu tavallisimmat CSRF-tapaukset
      SESSION_COOKIE_SECURE=True,        # eväste vain https-yhteydellä
      SESSION_COOKIE_HTTPONLY=True,      # ei näy javascriptille (oletus)
  )

  Kts. Security Considerations ja Set-Cookie.

• Sessiota käytetään kuten sanakirjaa. Käytä get-metodia oletusarvon kanssa, niin puuttuvaa arvoa ei tarvitse käsitellä poikkeuksena:

  summa = session.get("laskuri", 0)
  session["laskuri"] = summa + luku

  Vanhoissa ohjeissa näkee try/except-rakennetta samaan tarkoitukseen. Se toimii, mutta hukkaa ensimmäisellä kerralla annetun luvun ja peittää alleen muutkin virheet.

• Sessiomuuttujat näkyvät suoraan templatessa. Kokeile tulostaa summa sivulle.
• Ota lomakkeelta annettu luku talteen ja tarkista, että se on kokonaisluku. request.form.get("luku", 0, type=int) palauttaa oletusarvon, jos muunnos ei onnistu.

• Käsittele lomake POST-metodilla ja vastaa uudelleenohjauksella, jotta sivun päivittäminen ei lisää samaa lukua uudelleen:

  @app.post("/laskuri")
  def laske():
      luku = request.form.get("luku", 0, type=int)
      session["laskuri"] = session.get("laskuri", 0) + luku
      return redirect(url_for("laskuri"), code=303)

• Tee sivu, joka nollaa laskurin:

  @app.post("/nollaa")
  def nollaa():
      session.pop("laskuri", None)
      return redirect(url_for("laskuri"), code=303)

• Avaa selaimen kehittäjätyökalut (F12). Verkko-välilehdeltä näet Set-Cookie- ja Cookie-otsakkeet, ja Tallennustila- eli Storage-välilehdeltä evästeen sisällön. Poista eväste sieltä ja katso, palaako laskuri alkutilaan.
• Kokeile, toimiiko sovelluksesi kaikissa ympäristöissä: oma kone, users.jyu.fi ja PythonAnywhere.

Mallivastaus: ajossa oleva sovellus, oma.py, laskuri.html ja kirjaudu.html.

Sessio ei päivity?

Flask ei huomaa muutosta, jos muokkaat sessiossa olevaa listaa tai sanakirjaa paikallaan. Silloin sessio on merkittävä itse muuttuneeksi:

session["lista"].append("foo")
session.modified = True

Autentikointi

• Tee uusi sivu /kirjaudu ja sijoita sille lomake, joka kysyy tunnuksen ja salasanan. Muista type="password".

• Salasanaa ei koskaan tallenneta eikä vertailla selkokielisenä, vaan siitä lasketaan tarkiste. Käytä Flaskin mukana tulevaa werkzeug.security-moduulia, joka arpoo jokaiselle salasanalle oman suolan ja käyttää tarkoituksella hidasta algoritmia:

  from werkzeug.security import check_password_hash, generate_password_hash
  
  # tarkiste lasketaan kerran ja tallennetaan esimerkiksi tietokantaan
  tarkiste = generate_password_hash("testi")
  
  # kirjautumisessa verrataan annettua salasanaa tarkisteeseen
  if check_password_hash(tarkiste, salasana):
      ...

  Tarkisteen saa komentoriviltä näin:

  python3 -c "from werkzeug.security import generate_password_hash; print(generate_password_hash('testi'))"

  Tarkista sivullasi, että käyttäjätunnus on ties4080 ja salasana testi.

• Kun tunnus ja salasana täsmäävät, tyhjennä vanha sessio ja merkitse kirjautuminen:

  session.clear()
  session["kirjautunut"] = tunnus
  return redirect(url_for("laskuri"), code=303)

  session.clear() on tärkeä: ilman sitä hyökkääjä voi asettaa uhrin selaimeen tuntemansa sessiotunnisteen ja päästä sisään sen avulla (session fixation).

• Tee uloskirjautuminen, joka kutsuu session.clear()-metodia ja ohjaa kirjautumissivulle. Käytä POST-metodia: uloskirjautuminen muuttaa tilaa, eikä sen pidä tapahtua linkkiä klikkaamalla tai selaimen esiladatessa sivua.

• Virheilmoitus välitetään uudelleenohjauksen yli flash-viestinä:

  flash("Tunnus tai salasana on väärin.")

• Tee vielä oma dekoraattori, jolla kirjautumisvaatimuksen saa lisättyä haluamilleen sivuille:

  from functools import wraps
  
  def auth(f):
      """Vaatii kirjautumisen ja ohjaa tarvittaessa kirjautumislomakkeelle."""
      @wraps(f)
      def kaaritty(*args, **kwargs):
          if "kirjautunut" not in session:
              return redirect(url_for("kirjautumislomake"), code=303)
          return f(*args, **kwargs)
      return kaaritty

  @app.get("/laskuri")
  @auth
  def laskuri():
      ...

• Kokeile mennä selaimella laskurisivulle. Ohjaudutko kirjautumiseen? Jos et, kirjaudu ensin ulos.

Mallivastaus: ajossa oleva sovellus, kirjaudu.py, kirjaudu2.html ja kirjautunut.html.

Tietokannat

Harjoitellaan tietokantojen käyttämistä. Tehtävissä käytetään SQLite-tietokantaa, jota hallitaan komentoriviltä sqlite3-työkalulla. Tietokantatehtäviä voi kokeilla myös ajamalla Python-ohjelmaa suoraan komentoriviltä.

Tämän osuuden ajossa olevat mallisovellukset: reseptit ja kirjautuminen tietokantaa vasten.

Tämän osuuden tiedostot:

• resepti.sql — mallitietokannan luontiskripti
• db.py — mallivastaus reseptisovelluksesta: yhteyden avaaminen, kyselyt, lisäys, poisto, päivitys ja transaktiot
• reseptit.html — mallivastauksen template
• dbkirjaudu.py ja kirjaudu-db.html — kirjautuminen tietokantaa vasten

Kannattaa tehdä tehtävät ensin itse ja vilkaista mallivastausta vasta sitten. Yksittäisiin kohtiin liittyvät mallivastaukset on linkitetty myös kunkin luvun loppuun.

Tietokannan luominen

• Ota SSH-yhteys halava.cc.jyu.fi-palvelimeen.
• Siirry public_html-kansioosi ja luo sinne alihakemisto hidden, ellet ole jo luonut sitä.

• Hae mallitietokannan luontiskripti:

  wget https://appro.mit.jyu.fi/ties4080/ohjaus/ohjaus3/resepti.sql

• Luo tietokanta ja ota WAL-lokitus käyttöön:

  sqlite3 resepti.db

  .read resepti.sql
  PRAGMA journal_mode = wal;

  journal_mode tallentuu itse tietokantatiedostoon, joten se asetetaan kerran. Useimmat muut PRAGMA-asetukset, kuten viite-eheystarkistukset, ovat yhteyskohtaisia ja on annettava joka kerta uudelleen.

• Tutki tietokannan rakennetta komennoilla .tables, .schema Resepti ja .schema Ruokalaji. Komennolla .help näet kaikki työkalun komennot ja .quit palaa komentoriville.

• Katso, mitä taulut sisältävät:

  SELECT * FROM Resepti;
  SELECT * FROM Ruokalaji;

chmod 700 ~/public_html/hidden
chmod 600 ~/public_html/hidden/resepti.db
chmod 600 ~/public_html/hidden/avain.txt

Tietokantayhteys

Web-sovelluksessa yhteys avataan kerran pyyntöä kohti ja suljetaan pyynnön päätteeksi. Flaskissa se hoituu g-oliolla ja teardown_appcontext-koukulla:

import os
import sqlite3
from flask import g

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

def kanta():
    if "kanta" not in g:
        g.kanta = sqlite3.connect(KANTA)
        # sarakkeisiin voi viitata nimellä: rivi["Nimi"]
        g.kanta.row_factory = sqlite3.Row
        # viite-eheystarkistukset, yhteyskohtainen asetus
        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()

Polku johdetaan tiedoston sijainnista eikä työhakemistosta: CGI-prosessin työhakemisto ei välttämättä ole se, mitä oletat, ja väärällä polulla SQLite luo hiljaisesti uuden tyhjän kannan.

try:
    yhteys = sqlite3.connect(KANTA)
except sqlite3.Error:
    return "tietokanta ei aukea"
finally:
    yhteys.close()          # NameError, jos avaaminen epäonnistui

Kyselyt

• Lisää sovellukseesi sivu /reseptit ja tee siellä ensimmäinen kysely:

  rivit = kanta().execute("""
      SELECT ReseptiID, Nimi, Kuvaus, Henkilomaara
      FROM Resepti
      ORDER BY Nimi
  """).fetchall()
  
  return render_template("reseptit.html", reseptit=rivit)

  Voit ensin tulostaa tietorakenteen templatessa sellaisenaan merkinnällä {{ reseptit }} ja tehdä vasta sitten siistin taulukon.

• Luettele kyselyssä ne sarakkeet, joita tarvitset. SELECT * ja sarakkeisiin viittaaminen järjestysnumerolla hajoavat heti, kun tauluun lisätään sarake.

• Kysely, jossa on ehto, kirjoitetaan aina kyselyparametreilla:

  rivit = kanta().execute("""
      SELECT Nimi, Henkilomaara
      FROM Resepti
      WHERE Henkilomaara = :lukumaara
  """, {"lukumaara": 4}).fetchall()

  Parametrin nimessä isoilla ja pienillä kirjaimilla on merkitystä: :lukumaara on eri asia kuin :Lukumaara. Sama onnistuu myös kysymysmerkillä: WHERE Henkilomaara = ? ja parametrina (4,) — huomaa pilkku yhden alkion tuplessa.

• Kokeile käydä sama kursori läpi kahdesti. Jälkimmäisellä kerralla ei löydy mitään: kursori antaa tulokset kerran, ja jos ne tarvitaan toistamiseen, kysely on suoritettava uudelleen tai tulokset otettava talteen listaan.

Lisääminen

• Lisää tietokantaan uusi resepti:

  INSERT INTO Resepti (Nimi, Kuvaus, Henkilomaara, RuokalajiID)
  VALUES (:nimi, :kuvaus, :henkilomaara, :ruokalaji)

  Huomaa, ettei ReseptiID-saraketta anneta lainkaan. Se on määritelty INTEGER PRIMARY KEY -sarakkeeksi, jolloin tietokanta valitsee arvon itse. PostgreSQLissä sama tehdään tyypillä SERIAL.

• Kokeile antaa RuokalajiID-arvoksi numero, jota ei ole olemassa. Viite-eheys estää lisäyksen:

  sqlite3.IntegrityError: FOREIGN KEY constraint failed

  Kokeile samaa ilman PRAGMA foreign_keys = ON -riviä. Nyt lisäys menee läpi ja kantaan jää rivi, joka viittaa olemattomaan ruokalajiin. Siksi asetus on annettava jokaiselle yhteydelle.

• Kokeile lisätä kahdesti sama ruokalaji. Nimi on määritelty uniikiksi, joten toinen lisäys epäonnistuu. Ota virhe kiinni except sqlite3.IntegrityError -lohkolla ja kerro käyttäjälle, mikä meni pieleen.

Poistaminen ja päivittäminen

DELETE FROM Resepti WHERE ReseptiID = :id

UPDATE Resepti SET Henkilomaara = :maara WHERE ReseptiID = :id

• Kokeile poistaa ruokalaji, johon liittyy reseptejä. Viite-eheys estää senkin.
• Useamman rivin saa poistettua kerralla esimerkiksi ehdolla WHERE ReseptiID IN (1, 2, 3).
• kursori.rowcount kertoo, montako riviä operaatio muutti. Sillä erottaa onnistuneen poiston siitä, ettei riviä ollutkaan.

Transaktiot

Transaktiolla hallitaan operaatiokokonaisuuksia: jos yksikin lisäys epäonnistuu, kaikki perutaan. Siisteintä on antaa with-lohkon hoitaa asia. Lohkon lopussa tehdään commit, ja jos lohkosta poistutaan poikkeuksen takia, tehdään rollback:

try:
    yhteys = kanta()
    with yhteys:
        kursori = yhteys.execute(
            "INSERT INTO Ruokalaji (Nimi) VALUES (?)", (ruokalaji,))

        # lastrowid kertoo juuri lisätyn rivin avaimen
        uusi_laji = kursori.lastrowid

        yhteys.execute("""
            INSERT INTO Resepti (Nimi, Henkilomaara, RuokalajiID)
            VALUES (:nimi, 2, :laji)
        """, {"nimi": resepti, "laji": uusi_laji})

except sqlite3.IntegrityError:
    # molemmat lisäykset peruuntuivat: kantaan ei jäänyt kumpaakaan
    flash("Lisäys ei onnistunut, muutokset peruttiin.")

Kokeile antaa ruokalajille nimi, joka on jo olemassa, ja tarkista komentoriviltä, ettei kumpikaan rivi jäänyt kantaan. Viimeksi lisätyn rivin avaimen saa muuten myös kyselyllä SELECT last_insert_rowid().

Mitä with-lohko tekee

Tietokantayhteyden käyttäminen with-lohkossa on dokumentoitu sqlite3-kirjaston ohjeessa. Lohko hoitaa transaktion — ei yhteyttä:

Verkossa näkee usein väitteen, että with-lohko sulkisi myös yhteyden. Se ei pidä paikkaansa — kokeile itse: yhteydellä voi tehdä kyselyitä vielä lohkon jälkeen.

Sama ilman with-lohkoa

Näin sama transaktio kirjoitetaan käsin. Vertaa tätä edelliseen esimerkkiin: with-lohko tekee saman ilman else-haaraa ja ilman erillisiä commit- ja rollback-kutsuja.

yhteys = kanta()

try:
    kursori = yhteys.execute(
        "INSERT INTO Ruokalaji (Nimi) VALUES (?)", (ruokalaji,))

    uusi_laji = kursori.lastrowid

    yhteys.execute("""
        INSERT INTO Resepti (Nimi, Henkilomaara, RuokalajiID)
        VALUES (:nimi, 2, :laji)
    """, {"nimi": resepti, "laji": uusi_laji})

except sqlite3.Error:
    # Peruminen on tehtävä itse. Huomaa myös, ettei suoritus saa jatkua
    # normaalisti tämän jälkeen: ilman return-lausetta tai else-haaraa koodi
    # etenisi riville, jolla tehdään commit.
    yhteys.rollback()
    flash("Lisäys ei onnistunut, muutokset peruttiin.")

else:
    # commit kuuluu else-haaraan eikä try-lohkon loppuun. Muuten try nappaisi
    # myös commitin omat virheet ja perisi ne väärässä kohdassa.
    yhteys.commit()
    flash("Ruokalaji ja resepti lisättiin.")

Käsin kirjoitettuna virhemahdollisuuksia on kolme: rollback unohtuu, commit unohtuu, tai suoritus jatkuu virheen jälkeen kuin mitään ei olisi tapahtunut. with-lohko poistaa kaikki kolme, joten käytä sitä aina kun voit.

Monimutkaisemmat tulostukset

Listataan kaikki ruokalajit otsikoiksi ja kunkin alle siihen liittyvät reseptit. Tee yksi kysely, joka hakee kaiken kerralla ja järjestää tuloksen valmiiksi — älä tee silmukassa uutta kyselyä jokaiselle ruokalajille.

SELECT Ruokalaji.Nimi AS Ruokalaji, Resepti.Nimi AS Resepti
FROM Ruokalaji LEFT OUTER JOIN Resepti
     ON Ruokalaji.RuokalajiID = Resepti.RuokalajiID
ORDER BY Ruokalaji.Nimi, Resepti.Nimi

LEFT OUTER JOIN ottaa mukaan myös ne ruokalajit, joihin ei liity yhtään reseptiä. Niiden riveillä Resepti on tyhjä. Ryhmittely tehdään templatessa Jinjan groupby-filtterillä. Filtteri lajittelee rivit itse ryhmittelyavaimen mukaan, joten ryhmät tulevat aakkosjärjestyksessä; ORDER BY ratkaisee sen, missä järjestyksessä rivit ovat kunkin ryhmän sisällä:

{% for ruokalaji, rivit in lajeittain|groupby("Ruokalaji") %}
  <h3>{{ ruokalaji }}</h3>

  {% set omat = rivit|selectattr("Resepti")|list %}

  {% if omat %}
    <ul>
      {% for r in omat %}
        <li>{{ r["Resepti"] }}</li>
      {% endfor %}
    </ul>
  {% else %}
    <p>Ei reseptejä.</p>
  {% endif %}
{% endfor %}

Filtteri palauttaa listan pareja, jotka puretaan silmukassa kahteen muuttujaan: ensimmäinen on ryhmittelyavain ja toinen tavallinen lista kyseisen ryhmän riveistä. Listan voi suodattaa, laskea tai käydä läpi useampaan kertaan. selectattr("Resepti") poimii ne rivit, joilla sarakkeessa on arvo, eli pudottaa pois LEFT OUTER JOIN -kyselyn tuottamat tyhjät rivit.

Vanhemmissa esimerkeissä sama tehtiin vertaamalla riviä edelliseen loop.previtem-muuttujan avulla. Se toimii, mutta tuottaa helposti epävalidia merkkausta — listan aloitus ja lopetus jäävät eri haaroihin — ja kaatuu UndefinedError-virheeseen, jos tuloksessa on vain yksi rivi. groupby on sekä lyhyempi että turvallisempi.

SELECT Ruokalaji.Nimi, COUNT(Resepti.ReseptiID) AS Lukumaara
FROM Ruokalaji LEFT OUTER JOIN Resepti
     ON Ruokalaji.RuokalajiID = Resepti.RuokalajiID
GROUP BY Ruokalaji.RuokalajiID, Ruokalaji.Nimi

Ryhmittely Python-koodissa

Ryhmittelyn voi tehdä myös Python-koodissa ja viedä templatelle valmiiksi ryhmitelty rakenne. MVC-mielessä se on jopa puhtaampaa, koska templateen jää vain kaksi sisäkkäistä silmukkaa.

Yksinkertaisin tapa on koota sanakirja, jonka avaimena on ruokalaji ja arvona lista sen resepteistä:

lajeittain = {}

for rivi in rivit_kannasta:
    # setdefault luo tyhjän listan, jos avainta ei vielä ole, ja palauttaa sen
    reseptit = lajeittain.setdefault(rivi["Ruokalaji"], [])

    # LEFT OUTER JOIN tuottaa reseptittömälle ruokalajille rivin, jossa
    # Resepti on None. Ruokalaji tulee mukaan, mutta listaan ei lisätä mitään.
    if rivi["Resepti"]:
        reseptit.append(rivi["Resepti"])

{% for ruokalaji, reseptit in lajeittain.items() %}
  <h3>{{ ruokalaji }}</h3>

  {% if reseptit %}
    <ul>
      {% for nimi in reseptit %}
        <li>{{ nimi }}</li>
      {% endfor %}
    </ul>
  {% else %}
    <p>Ei reseptejä.</p>
  {% endif %}
{% endfor %}

Sanakirja säilyttää lisäysjärjestyksen, joten ryhmät tulevat siinä järjestyksessä, jossa ne kyselyn tuloksessa ensimmäisen kerran esiintyivät.

Toinen vaihtoehto on itertools.groupby, joka ei rakenna uutta tietorakennetta, vaan niputtaa peräkkäiset rivit:

from itertools import groupby
from operator import itemgetter

lajeittain = [
    (laji, [rivi for rivi in rivit if rivi["Resepti"]])
    for laji, rivit in groupby(rivit_kannasta, key=itemgetter("Ruokalaji"))
]

itertools.groupby: [('Jälkiruoka', ['Aprikoosikiisseli']),
                    ('Pääruoka',   ['Banaanicurry']),
                    ('Jälkiruoka', ['Rommikakku'])]

sanakirja:         {'Jälkiruoka': ['Aprikoosikiisseli', 'Rommikakku'],
                    'Pääruoka':   ['Banaanicurry']}

Milloin kumpikin? Sanakirja on selkein silloin, kun tiedät jo valmiiksi, mitä kustakin rivistä tarvitaan, ja se kestää järjestyksen muutokset. itertools.groupby on kätevä, kun haluat säilyttää rivit kokonaisuudessaan etkä halua päättää etukäteen, mitä kenttiä niistä käytetään — ja kun rivejä on niin paljon, ettei niitä haluta kopioida uuteen rakenteeseen. Jinjan groupby taas säästää Python-puolen kokonaan, jos ryhmittely on ainoa asia, joka tulokselle tehdään.

Tarkista lopuksi validaattorilla, että otsikot ja listat tulostuvat oikein rakenteellisesti.

Mallivastaus: ajossa oleva sovellus, db.py ja reseptit.html.

Kirjautuminen ja tietokanta

Yleensä tunnus ja salasana tarkistetaan tietokannasta. Mallitietokannassa on Kayttaja-taulu, jossa on kolme tunnusta:

Kannassa ei tietenkään ole salasanoja, vaan niistä lasketut tarkisteet, jotka on tehty generate_password_hash-funktiolla. Muokkaa aiemmin tekemääsi kirjautumista siten, että tunnus haetaan kannasta:

rivi = kanta().execute("""
    SELECT Tarkiste AS tarkiste
    FROM Kayttaja
    WHERE Tunnus = :tunnus
""", {"tunnus": tunnus}).fetchone()

if rivi is None or not check_password_hash(rivi["tarkiste"], salasana):
    flash("Tunnus tai salasana on väärin.")
    return redirect(url_for("start"), code=303)

Edellytä kirjautumista reseptisivuille @auth-dekoraattorilla.

Mallivastaus: ajossa oleva sovellus, dbkirjaudu.py ja kirjaudu-db.html.

PostgreSQL

PostgreSQL-tietokantaa käsitellään samalla PEP 249 -rajapinnalla kuin SQLiteä; vain ajuri ja yhteyden avaaminen vaihtuvat. Nykyinen ajuri on psycopg 3:

pip install "psycopg[binary]"

import os
import psycopg

# Yhteystiedot luetaan ympäristöstä. Niitä ei kirjoiteta lähdekoodiin eikä
# versionhallintaan.
with psycopg.connect(os.environ["DATABASE_URL"]) as yhteys:
    with yhteys.cursor() as kursori:
        kursori.execute("SELECT Nimi FROM Resepti WHERE Henkilomaara = %s", (4,))
        print(kursori.fetchall())

Huomaa, että PostgreSQL käyttää eri parametrimerkintää kuin SQLite: %s tai %(nimi)s SQLiten ?- ja :nimi-merkintöjen sijaan.

PostgreSQL-tietokantaa pääsee kokeilemaan kurssin viimeisessä viikkotehtävässä.

MySQL

Jos törmäät MySQL:ään muualla, muista ainakin nämä erot: kyselyparametrit ovat muotoa %(nimi)s, automaattinen avain on AUTO_INCREMENT, transaktio aloitetaan komennolla START TRANSACTION, ja merkkijonovertailu on oletuksena kirjainkokoriippumaton toisin kuin useimmissa muissa tietokannoissa.

Lisätietoa

• sqlite3 — DB-API 2.0 interface for SQLite databases
• SQLite Foreign Key Support ja Write-Ahead Logging
• Jinja: groupby
• werkzeug.security
• OWASP: Password Storage Cheat Sheet
• Tietokannat ja tiedonhallinnan perusteet