"""TIES4080 pääteohjaus 3: reseptitietokanta. Esimerkki näyttää, miten SQLite-tietokantaa käytetään Flask-sovelluksesta: yhteyden avaaminen ja sulkeminen, kyselyt parametreineen, lisäys, poisto, päivitys ja transaktiot. Tietokanta luodaan resepti.sql-tiedostosta ja se sijoitetaan www-hakemiston ulkopuolelle kansioon hidden. """ import os import sqlite3 from flask import Flask, flash, g, redirect, render_template, request, url_for app = Flask(__name__) # Salainen avain allekirjoittaa sessioevästeen. Sen tietävä voi väärentää # session, joten avainta ei kirjoiteta lähdekoodiin eikä versionhallintaan. # users.jyu.fi-palvelimella ei voi asettaa omia ympäristömuuttujia, joten avain # luetaan tiedostosta, joka on www-hakemiston ulkopuolella samassa kansiossa # tietokannan kanssa. # # Tiedosto luodaan kerran komennoilla: # python3 -c "import secrets; print(secrets.token_hex(32))" > ~/public_html/hidden/avain.txt # chmod 600 ~/public_html/hidden/avain.txt 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 tiedostosta {polku}. " "Luo tiedosto komennolla: " "python3 -c \"import secrets; print(secrets.token_hex(32))\" > avain.txt" ) from virhe if not avain: raise RuntimeError(f"Avaintiedosto {polku} on tyhjä.") return avain app.secret_key = lue_salainen_avain(AVAINTIEDOSTO) app.config.update( SESSION_COOKIE_SAMESITE="Lax", SESSION_COOKIE_SECURE=True, ) # Polku johdetaan tämän tiedoston sijainnista, ei työhakemistosta: CGI-prosessin # työhakemisto ei välttämättä ole se, mitä oletat. KANTA = os.path.join(os.path.dirname(os.path.abspath(__file__)), "..", "..", "..", "hidden", "resepti.db") # -------------------------------------------------------------------------- # Tietokantayhteys # -------------------------------------------------------------------------- def kanta(): """Palauttaa tämän pyynnön tietokantayhteyden ja avaa sen tarvittaessa.""" if "kanta" not in g: g.kanta = sqlite3.connect(KANTA) # sarakkeisiin voi viitata nimellä: rivi["Nimi"] g.kanta.row_factory = sqlite3.Row # viite-eheystarkistukset ovat yhteyskohtainen asetus g.kanta.execute("PRAGMA foreign_keys = ON") return g.kanta @app.teardown_appcontext def sulje_kanta(poikkeus): """Suoritetaan aina pyynnön päätteeksi, myös virhetilanteessa.""" yhteys = g.pop("kanta", None) if yhteys is not None: yhteys.close() # -------------------------------------------------------------------------- # Kyselyt # -------------------------------------------------------------------------- def hae_reseptit(): return kanta().execute(""" SELECT ReseptiID, Nimi, Kuvaus, Henkilomaara FROM Resepti ORDER BY Nimi """).fetchall() def hae_ruokalajit(): return kanta().execute(""" SELECT RuokalajiID, Nimi, Kuvaus FROM Ruokalaji ORDER BY Nimi """).fetchall() def hae_reseptit_lajeittain(): """Kaikki ruokalajit ja niihin liittyvät reseptit. LEFT OUTER JOIN ottaa mukaan myös ne ruokalajit, joihin ei liity yhtään reseptiä. Silloin sarake Resepti on NULL. """ return kanta().execute(""" 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 """).fetchall() def hae_isot_reseptit(henkilomaara): """Esimerkki kyselystä, jossa on parametri. Arvoa EI koskaan sijoiteta SQL-lauseeseen merkkijono-operaatioilla, vaan aina kyselyparametrina. Muuten syötteellä voi muuttaa kyselyn rakennetta (SQL-injektio). """ return kanta().execute(""" SELECT ReseptiID, Nimi, Henkilomaara FROM Resepti WHERE Henkilomaara >= :vahintaan ORDER BY Henkilomaara DESC """, {"vahintaan": henkilomaara}).fetchall() # -------------------------------------------------------------------------- # Näkymät # -------------------------------------------------------------------------- @app.get("/") def etusivu(): return redirect(url_for("reseptit"), code=303) @app.get("/reseptit") def reseptit(): return render_template("reseptit.html", reseptit=hae_reseptit(), ruokalajit=hae_ruokalajit(), lajeittain=hae_reseptit_lajeittain(), isot=hae_isot_reseptit(6)) @app.post("/reseptit") def lisaa_resepti(): nimi = request.form.get("nimi", "").strip()[:100] kuvaus = request.form.get("kuvaus", "").strip()[:250] or "-" henkilomaara = request.form.get("henkilomaara", 2, type=int) ruokalaji = request.form.get("ruokalaji", 0, type=int) if not nimi: flash("Reseptille on annettava nimi.") return redirect(url_for("reseptit"), code=303) if henkilomaara < 1: flash("Henkilömäärän on oltava vähintään yksi.") return redirect(url_for("reseptit"), code=303) try: yhteys = kanta() # with-lohko hoitaa transaktion: commit lopuksi, rollback poikkeuksesta. # ReseptiID jätetään antamatta, jolloin tietokanta valitsee sen itse. with yhteys: yhteys.execute(""" INSERT INTO Resepti (Nimi, Kuvaus, Henkilomaara, RuokalajiID) VALUES (:nimi, :kuvaus, :henkilomaara, :ruokalaji) """, {"nimi": nimi, "kuvaus": kuvaus, "henkilomaara": henkilomaara, "ruokalaji": ruokalaji}) flash(f"Resepti {nimi} lisättiin.") except sqlite3.IntegrityError: # esimerkiksi olematon ruokalaji: viite-eheys estää lisäyksen app.logger.info("Reseptin lisäys epäonnistui: %s", nimi) flash("Lisäys ei onnistunut. Tarkista ruokalaji.") return redirect(url_for("reseptit"), code=303) @app.post("/reseptit//poista") def poista_resepti(resepti_id): yhteys = kanta() with yhteys: # Poistossa viitataan aina perusavaimeen. Muilla ehdoilla poistuu # helposti vääriä rivejä. kursori = yhteys.execute("DELETE FROM Resepti WHERE ReseptiID = ?", (resepti_id,)) if kursori.rowcount: flash("Resepti poistettiin.") else: flash("Reseptiä ei löytynyt.") return redirect(url_for("reseptit"), code=303) @app.post("/reseptit//henkilomaara") def muuta_henkilomaaraa(resepti_id): maara = request.form.get("henkilomaara", 0, type=int) if maara < 1: flash("Henkilömäärän on oltava vähintään yksi.") return redirect(url_for("reseptit"), code=303) yhteys = kanta() with yhteys: yhteys.execute(""" UPDATE Resepti SET Henkilomaara = :maara WHERE ReseptiID = :id """, {"maara": maara, "id": resepti_id}) return redirect(url_for("reseptit"), code=303) @app.post("/ruokalaji") def lisaa_ruokalaji_ja_resepti(): """Esimerkki transaktiosta, jossa on kaksi toisiinsa liittyvää lisäystä. Jos jälkimmäinen epäonnistuu, myös ensimmäinen perutaan. Kokeile lisätä ruokalaji, joka on jo olemassa, tai resepti ilman nimeä. """ ruokalaji = request.form.get("ruokalaji", "").strip()[:100] resepti = request.form.get("resepti", "").strip()[:100] if not ruokalaji or not resepti: flash("Anna sekä ruokalajin että reseptin nimi.") return redirect(url_for("reseptit"), code=303) try: yhteys = kanta() with yhteys: kursori = yhteys.execute( "INSERT INTO Ruokalaji (Nimi) VALUES (?)", (ruokalaji,)) # lastrowid kertoo juuri lisätyn rivin avaimen. Se toimii, koska # RuokalajiID on määritelty INTEGER PRIMARY KEY -sarakkeeksi. uusi_laji = kursori.lastrowid yhteys.execute(""" INSERT INTO Resepti (Nimi, Henkilomaara, RuokalajiID) VALUES (:nimi, 2, :laji) """, {"nimi": resepti, "laji": uusi_laji}) flash(f"Ruokalaji {ruokalaji} ja resepti {resepti} lisättiin.") except sqlite3.IntegrityError: # Molemmat lisäykset peruuntuivat: kantaan ei jäänyt kumpaakaan. flash("Lisäys ei onnistunut, muutokset peruttiin. " "Onko samanniminen ruokalaji jo olemassa?") return redirect(url_for("reseptit"), code=303) if __name__ == "__main__": # kehityspalvelin omalla koneella; palvelimella sovelluksen käynnistää # flask.cgi tai WSGI-palvelin app.run(debug=True)