Sessiot, autentikointi ja relaatiotietokannat
- Laskuri sessioilla
- Autentikointi
- Tietokannat
- Tietokannan luominen
- Tietokantayhteys
- Kyselyt
- Lisääminen
- Poistaminen
- Päivittäminen
- Monimutkaisemmat tulostukset
- Transaktiot
- Automaattiset avainkentät
- Kirjautuminen ja tietokanta
- Lisätietoa
- PostgreSQL
- PythonAnywhere ja MySQL
Harjoitellaan sessioiden käyttöä WWW-sovelluksen tekemisessä. Lisätään sovellukseen autentikointi. Tutustutaan relaatiotietokantoihin.
Laskuri sessioilla
Etsi edellisessä pääteohjauksessa tekemäsi laskurisovellus. Korjataan laskuri toimimaan kunnolla sessioiden avulla.
- Ohjelman pitää siis muistaa aiemmin saatu laskurin summa. Tämä onnistuu session avulla.
Sessioon tallennetut muuttujat säilyttävät arvonsa koko istunnon ajan. Lisää
ohjelmakooditiedostosi alkuun rivi:
Myös seuraava asetus kannattaa tehdä:from flask import session
app.config.update( SESSION_COOKIE_SAMESITE='Lax' )
Kts. Security Considerations: Set-Cookie options ja SameSite cookies
Tämän lisäksi tarvit salaisen avaimen jota käytetään sessioon liittyvän evästeen käsittelyssä. Samaa salaista avainta käytettiin jo aiemmin lomakkeiden yhteydessä.
app.secret_key = '"\xf9$T\x88\xefT8[\xf1\xc4Y-r@\t\xec!5d\xf9\xcc\xa2\xaa'
Sinun pitäisi muodostaa itse oma salainen avain. Se onnistuu esimerkiksi käynnistämällä python-tulkki komentoriviltä ja antamalla komennot:
Salaisen avaimen täytyy säilyä samana sovelluksesi eri kutsukertojen välillä. Et voi luoda sitä aina uudelleen ja uudelleen.import os os.urandom(24)
- Luo uusi sessiomuuttuja:
sessiomuuttujia käytetään samaan tapaan kuin dictejäsession['laskuri'] = 0
- sessio-muuttujien pitäisi näkyä suoraan templateen. Kokeile tulostaa
session['laskuri']
-muuttujan arvo www-sivulle - Yritä kasvattaa sessiomuuttujasi arvoa ohjelman jokaisella suorituskerralla.
Ensimmäisellä käyttökerralla sessio-muuttujaa ei ole määritelty joten varminta on testata pythonin poikkeuskäsittelyn (try...except) avulla onko sessiomuuttujalla jo arvo vai ei. Jos muuttuja on alustamaton niin yritys lisätä muuttujan arvoa aiheuttaa poikkeuksen, jolloin voidaan tehdä muuttujan alustaminen.
try: session['laskuri'] = session['laskuri'] + 1 except: session['laskuri'] = 0
- Ota lomakkeelle annettu luku talteen. Tarkista, että lomakkeelle on varmasti syötetty kokonaisluku. Lisää luku sessiomuuttujaan. Tulosta yhteenlaskettu summa sivulle.
-
Istunnon ja siihen liittyvien muuttujien ylläpitäminen edellyttää, että
käytetty selain tukee evästeitä. Evästeessä kuljetetaan selaimen ja WWW-palvelimen
välillä istuntoon liittyvää tunnistetta.
Katso Firefoxin Tools|Web developer|Network-välilehdeltä minkälaisia HTTP-otsakkeita liikkuu selaimesi ja WWW-palvelimen välillä.
Sieltä pitäisi löytyä Set-Cookie-alkuisia rivej. Kokeile myös Web Developer Toolbarin
Cookies|View Cookie Information-valinnan avulla millaisen evästeen flask asettaa käyttäessäsi sessioita.
Testauksessa kannattaa käyttää myös Web Developer Toolbarista löytyvää valintaa Cookies | Delete Domain Cookies. Tämä poistaa näkyvillä olevaan sivuun liittyvät evästeet. Poistamalla evästeet palaa laskuri alkutilaan.
- Tee uusi sivu joka poistaa laskurisi session.pop('laskuri',None)-metodilla. Kokeile nollautuuko laskuri.
@app.route('/nollaa') def nollaa(): session.pop('laskuri',None) # url_for-metodilla voidaan muodostaa osoite haluttuun funktioon. redirect taas ohjaa suoraan tälle sivulle joten # nollaa-osoite ei tarvitse omaa sisältöä return redirect(url_for('laskuri'))
- Kokeile toimiiko sovelluksesi kaikissa ympäristöissä: oma kone, users.jyu.fi ja pythonanywhere
- mallivastaus ( oma.py, laskuri.html )
Autentikointi
Käyttäjän kirjautuminen ja autentikointi järjestelmään tehdään sessioiden avulla seuraavalla tavalla:
- Täytyy ensimmäisenä luoda sivu jolla voi kirjautua sisään laskurisovellukseen. Tee uusi sivu (/kirjaudu) ja sijoita sivulle kirjautumislomake jolla kysytään tunnus ja salasana
- Tarkista sivulla, että käyttäjätunnus on ties4080 ja salasanaksi
on syötetty testi. Salasanan vertailu on tehtävä seuraavalla tavalla:
import hashlib m = hashlib.sha512() avain = u"omasalainenavain" m.update(avain.encode("UTF-8")) m.update(salasana.encode("UTF-8")) if tunnus=="ties4080" and m.hexdigest() == "366e90b5fe29a9d9c1420afa334c4b19c4d63dcd200f424b7a9fe3328a352da5818fc03cffa463c2362db3535b612df4eb27df33d4720fbf592964571ad7572e": # jos kaikki ok niin asetetaan sessioon tieto kirjautumisesta ja ohjataan laskurisivulle session['kirjautunut'] = "ok" return redirect(url_for('laskuri')) # jos ei ollut oikea salasana niin pysytään kirjautumissivulla. return render_template('kirjaudu.html')
Selkokielistä salasanaa ei siis vertailla vaan salasanasta jollakin algoritmilla laskettua merkkijonoa. Testi-salasanaa vastaava merkkijono on tuotettu seuraavalla tavalla. Oikean salasanan vertailu pitää tehdä samalla tavalla jolloin voi verrata onko m.hexdigest-funktion palauttama merkkijono sama kuin haluttu.
>>> import hashlib >>> m = hashlib.sha512() >>> m.update("omasalainenavain".encode("UTF-8")) >>> m.update("testi".encode("UTF-8")) >>> m.hexdigest() "366e90b5fe29a9d9c1420afa334c4b19c4d63dcd200f424b7a9fe3328a352da5818fc03cffa463c2362db3535b612df4eb27df33d4720fbf592964571ad7572e"
- Kokeile toimiiko kirjautuminen eli ohjaudutko laskurisivulle, jos syötät oikean salasanan
- Tee myös logout-sivu, joka poistaa sessiosta kirjautunut-avaimen, ja ohjaa sen jälkeen kirjaudu-sivulle
- Vielä tarvitaan oma decorator, jolla saadaan helposti yhdistettyä kirjautumisvaatimus halutuille sivuille. Vrt. app.routes.
Lisää sivun alkuun:
Kirjoita seuraavanlainen funktio:from functools import wraps
def auth(f): ''' Tämä decorator hoitaa kirjautumisen tarkistamisen ja ohjaa tarvittaessa kirjautumissivulle ''' @wraps(f) def decorated(*args, **kwargs): # tässä voisi olla monimutkaisempiakin tarkistuksia mutta yleensä tämä riittää if not 'kirjautunut' in session: return redirect(url_for('kirjaudu')) return f(*args, **kwargs) return decorated
- Kokeile nyt käyttää omaa @auth-decoratoria. Lisää se esim. laskuri-funktion eteen:
@app.route('/laskuri', methods=['POST','GET']) @auth def laskuri():
- Kokeile mennä selaimella laskurisivulle. Ohjaudutko kirjautumiseen? Jos et, niin muista poistaa vanha kirjautuminen logout-sivulla ja kokeile sen jälkeen uudelleen.
- mallivastaus ( oma.py, kirjaudu.html )
- mallivastaus v2 - 2024 ( kirjaudu.py, kirjaudu2.html )
Tietokannat
Harjoitellaan tietokantojen käyttämistä.
Mallivastaus: reseptit (Lähdekoodi, Template)
Katso ennen näiden tehtävien tekemistä luennot: Tietokannat ja Python ja Tietokannat ja python jatkoa.
Tietokantatehtävät voi tehdä myös ajamalla python-sovellusta suoraan halavassa/jalavassa komentoriviltä.
Muista myös Pythonin sqlite3-kirjaston dokumentaatio
Tietokannan luominen
Tehtävissä käytetään SQLite3-tietokantaa, jota voidaan hallinnoida suoraan komentoriviltä käynnistyvällä työkalulla. Samaan tapaan onnistuu myös hienompien tietokannan hallintajärjestelmien hallinnointi (PostgreSQL, MySQL). SQLite-tietokannan kanssa mahdollisesti vastaantulevista ongelmista on oma FAQ.
- Ota Puttylla SSH-yhteys halava.cc.jyu.fi:hin.
- Siirry public_html-kansioosi eli symbolisen linkin kautta W:-asemaa vastaavaan paikkaan. Luo tänne alihakemisto hidden ellet luonut sitä jo aiemmin.
- Kopioi itsellesi resepti.sql-tiedosto wget-komennolla:
wget https://appro.mit.jyu.fi/ties4080/ohjaus/ohjaus3/resepti.sql
Tietokannan rakenne:
- Avaa hidden-kansiossa sqlite3 komennolla:
sqlite3 resepti
- Luo tietokannan rakenne komennolla:
.read resepti.sql
SQLite lukee resepti.sql-tiedoston ja suorittaa sen sisältämät SQL-komennot. Jos SQLite kaatuu, käynnistä se uudelleen.
SQLite luo nyt uuden resepti-tiedoston ja tekee siitä tietokannan. SQLite saattaa kaatua, mutta sen pitäisi silti luoda tietokanta. Kokeile käynnistämällä SQlite uudelleen ja kirjoittamalla .tables. Jos saat listauksen tietokannan tauluista niin kaikki on ok.
- Asetetaan tietokantaan WAL (Write-Ahead Logging) päälle:
Useimmat PRAGMA-komennoista täytyy antaa joka kerta sovelluksen tietokantayhteyden avaamisen yhteydessä, mutta WAL-moden asettaminen jää kertoasetuksella voimaan.PRAGMA journal_mode = wal;
- Voit tutustua sqlite3-työkalun osaamiin komentoihin kirjoittamalla .help.
- Tutki tietokantasi rakennetta seuraavilla komennoilla: .tables, .schema resepti ja .schema ruokalaji.
- Tutki mitä taulut sisältävät yksinkertaisilla SQL-kyselyillä:
SELECT * FROM Resepti;
SELECT * FROM Ruokalaji;
- .quit-komennolla pääset takaisin unix-shelliin.
- Anna tietokantaan ja kansioon (hidden), jossa tietokantatiedosto sijaitsee, kaikille kirjoitusoikeus (chmod a+rw resepti ja chmod a+rwx ./), niin tietokantaan kohdistuvat muutosoperaatiot toimivat jatkossa myös WWW:n kautta. Näiden oikeuksien antaminen on selkeä tietoturvaongelma, mutta muuta vaihtoehtoa ei ole users.jyu.fi-palvelimella.
Tietokantayhteys
Sovelluksen alussa pitää luoda tietokantaan yhteys, jota käytetään koko suorituksen ajan. Yhteyden avaaminen on aina raskas prosessi, joten turhaan yhteyttä ei pidä aukoa ja sulkea. Suurissa sovelluksissa tietokantayhteys pidetään auki pitempään ja samaa yhteyttä käytetään uudelleen ja uudelleen.
Kts. Database Connections in Python: Extensible, Reusable, and Secure
- Lisää Flask-ohjelmaasi uusi sivu /reseptit.
Avaa ensimmäisenä tietokantayhteys:
import sqlite3 import os @app.route('/reseptit') def reseptit(): # Oletetaan että sovellus on kansiossa /~omatunnus/cgi-bin/ties4080/ohjaus4/ # ja tietokanta on kansiossa /~omatunnus/hidden/ # os.path.abspath muuntaa suhteellisen polun absoluuttiseksi, joka kelpaa sqlitelle # asetetaan myös autocommit-moodi päälle (isolation_level=None) try: con = sqlite3.connect(os.path.abspath('../../../hidden/resepti'), isolation_level=None) except sqlite3.Error: # mitäs nyt tehdään? return Response("tietokanta ei aukea") finally: #tämä täytyy tehdä aina lopuksi. Hoituu helpoiten finallylla con.close() return Response("Toimii")
-
Asetetaan tietokantayhteydessä viite-eheydet (foreign keys) käyttöön:
con.execute("PRAGMA foreign_keys = ON;")
-
Aseta tietokantayhteyteen seuraavat asetukset jotta voidaan kursoreissa viitata kenttiin niiden nimillä:
# voidaan käsitellä palautettuja tietueita niiden kenttien nimillä con.row_factory = sqlite3.Row
- Sinulla pitäisi olla nyt seuraavankaltainen koodi:
#tietokantayhteyden avaaminen ja olennaiset asetukset con = sqlite3.connect(os.path.abspath('../../../hidden/resepti')) con.row_factory = sqlite3.Row con.execute("PRAGMA foreign_keys = ON")
sqlite3.Row
on useimmiten paras valinta kyselyn tulosrivien käsittelyyn (row_factory), mutta joskus tarvitsee rakentaa oma. Jos haluat käsitellä kyselyn tuloksia dicteinä eli esim. helposti muuttaa tuloksia, niin tee seuraavasti:
Kts. How can I get dict from SQLite querydef dict_factory(cursor, row): d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d con.row_factory = dict_factory
- Kokeile selaimella toimiiko ohjelmasi ilman virheilmoituksia
- Kun tietokantayhteyttä ei enää tarvita niin sulje yhteys:
con.close()
Yhteyden sulkeminen pitää muistaa tehdä aina, koska tietokantayhteyksiä on normaalisti vain rajattu määrä. Varaudu sulkemaan tietokantayhteys myös sovelluksesi kaatuessa. Kts. finally, Python Exception Handling: Use case for finally?, Finally, a use case for finally – Python Exception Handling Part II
Kyselyt
Tietokantaan kohdistuvat kyselyt ovat jokaisen sovelluksen perusta. Jos SQL-kyselyiden kirjoittaminen tuottaa suuria hankaluuksia voit harjoitella niitä Henkilökohtaisen tiedonhallinnan perusteet -kurssin demotehtävillä.
-
Valmistele yksinkertainen kysely:
sql = """ SELECT nimi as Nimi, kuvaus as Kuvaus, henkilomaara as Henkilomaara FROM resepti """
- Luo Cursor-objekti ja suorita (execute) kysely. Mahdolliset virhetilanteet kannattaa
napata kiinni
try..except
-lohkolla.cur = con.cursor() cur.execute(sql)
-
Luo tuloksena saaduista tietueista Jinja-templatelle sopiva tietorakenne. Seuraavassa on esimerkki
sql = """ SELECT nimi as Nimi, kuvaus as Kuvaus, henkilomaara as Henkilomaara FROM resepti """ cur = con.cursor() cur.execute(sql) # suoritetaan kysely reseptit = cur.fetchall() #haetaan kaikki kyselyn tulokset return render_template('reseptit.html', reseptit=reseptit)
- Vie reseptit templatelle. Voit ensimmäisessä versiossa suoraan tulostaa tietorakenteen templatessa näkyville.
Kokeile suorittaa ohjelmasi selaimessa. Tee tämän jälkeen siistimpi tulostusversio eli käy näkymässä silmukassa läpi reseptit-rakenne, joka toimii kuten dict, ja tulosta sen sisältö kauniisti taulukkona. Esim.{{ reseptit }}
<table> <caption>Reseptit</caption> {%for r in reseptit:%} <tr><th>{{r['nimi']}}</th> <td>{{r['kuvaus']}}</td> <td>{{r['henkilomaara']}}</td> {%endfor%} </table>
Kenttien nimissä isoilla ja pienillä kirjaimilla ei ole merkitystä.
- Yleensä kyselyjä pitää rajoittaa sopivilla ehdoilla, jotka muuttuvat ohjelman suorituksen mukana. Tällöin kyselystä
tulee dynaaminen. Muuta edellistä kyselyä seuraavanlaiseksi:
# valmistellaan kysely ja sijoitetaan dynaamisen arvon tilalle :lkm-muuttuja sql = '''SELECT Nimi, Kuvaus, Henkilomaara, ReseptiID, RuokaLajiID FROM Resepti WHERE henkilomaara = :lukumaara ''' lkm = 4 cur.execute( sql, {"lukumaara":lkm})
Kokeile sivun toimintaa Kokeile muuttaa lkm-muuttujan arvoa.
lukumaara-placeholderin nimessä on merkitystä isoilla ja pienillä kirjaimilla. :lukumaara on eri asia kuin :Lukumaara. - Yritä käydä sama kursori läpi silmukalla kahteen kertaan. Jälkimmäisellä kerralla ei pitäisi löytyä mitään. Kursori antaa kertaalleen tietokantakyselyn tulokset ja jos haluaa ne uudelleen on suoritettava execute uudelleen. Samaa kyselyä ei kannata suorittaa useaan kertaan vaan kannattaa ottaa heti talteen mitä tarvitsee.
- Hae tietokannasta kaikki reseptit ja vie ne templatelle. Hae myös kaikki ruokalajit. Tulosta nämä kaikki siististi näkyville www-sivullesi niin voit helpommin testata seuraavia tehtäviä, kun sivua uudelleen ladattaessa näet heti mitä muutoksia sisältöön on tullut.
Muuttujia saa sijoittaa kyselyihin vain edellämainitulla tavalla tai käyttäen ?-merkki placeholdereita! Missään tapauksessa sijoittamista ei saa tehdä normaaleilla merkkijono-operaatioilla. Oikein toimiessa tietokanta pitää itse huolen siitä, että sijoitettavat arvot ovat tarpeen mukaan heittomerkkien sisällä tai ilman. Tietokanta pitää myös huolen, että kenttiin ei tule vääräntyyppistä tietoa. Kyselyjen valmistelu parantaa tietoturvaa SQL Injection -tyyppisiä hyökkäyksiä vastaan.
Lisääminen
Tietojen lisääminen tapahtuu valmisteltujen kyselyjen avulla aivan samaan tapaan kuin kyselyjenkin tekeminen:
- Lisätään tietokantaan uusi resepti. Luo suusi valmisteltava kysely muotoon:
INSERT INTO resepti (Nimi, Kuvaus, Henkilomaara, RuokalajiID, ReseptiID) VALUES (:nimi, :kuvaus, :henkilomaara, :ruokalaji, :reseptiid)
- Kehittele jokin reseptin nimi ja lyhyt kuvaus (esim. makaronilaatikko) ja luo muuttujat kaikille parametreille. resepti.sql:stä voit katsoa millaisilla ID-arvoilla ruokalajeja on olemassa. Anna ReseptiID:ksi joku sellainen numero mitä ei vielä ole tietokannassa (esim. 10).
- Sido muuttujat kyselyyn ja suorita sovelluksesi lataamalla sivu uudelleen.
- Kokeile suorittaa saman reseptin lisääminen uudelleen. Onnistuuko vai saatko virheilmoituksen?
Ota try..exceptillä kiinni mahdolliset virheet.
Muistathan, että lisäys ei jää voimaan, ellei sinulla ole autocommit-moodi (isolation_level=none) päällä jolloin
transaktio täytyy hyväksyä:
Muistathan myös, että kahta reseptiä ei voi lisätä samalla reseptiID:llä (perusavaimen eheys!). Kokeile millaisen virheilmoituksen saat. Entäs jos yrität käyttää ruokalajin id:nä väärää arvoa? Kokeile millainen virheilmoitus nyt tulee.con.commit()
- IntegrityError: PRIMARY KEY must be unique
- IntegrityError: Constraint failed: Ruokalajia ei löydy
- Kokeile jättää reseptiid kokonaan pois INSERT-lauseesta:
Poista reseptiid myös execute-kutsun parametreista. Miksi tämä versio toimii?INSERT INTO resepti (Nimi, Kuvaus, Henkilomaara, RuokalajiID) VALUES (:nimi, :kuvaus, :henkilomaara, :ruokalaji)
Tietokannan rakenteessa on määritelty, että reseptiid on tyypiltään autoincrement, jolloin tietokanta keksii sen tarvittaessa itse. Tämä ei ole standardia SQL-kieltä vaan sqlite3:n ominaisuus. Esim. Postgresql-tietokannassa sama onnistuisi käyttämällä tyyppiä SERIAL.
ReseptiID INTEGER PRIMARY KEY AUTOINCREMENT
Poistaminen
- Lisää uusi kysely. Poistetaan edellä lisätty resepti.
DELETE FROM resepti WHERE reseptiID = :id
- Luo id-muuttuja ja sille arvo, joka löytyy tietokannasta. Esim. numero 2. (kts. resepti.sql). Sido muuttuja kyselyyn. Kokeile onnistuuko poistaminen.
- Yritä poistaa ruokalajeja. Onnistuuko se ilman ongelmia?
Voitko vapaasti poistaa minkä tahansa ruokalajin?
IntegrityError: constraint failed: Ruokalajia ei voida poistaa, koska se on käytössä
Käytössä olevia ruokalajea ei voi poistaa viite-eheyden takia:
CONSTRAINT Resepti_RuokalajiID FOREIGN KEY (RuokalajiID) REFERENCES Ruokalaji (RuokalajiID)
- Kokeile poistaa kerralla useampia tietueita. Tämä onnistuu esim. kyselyllä:
DELETE FROM taulu WHERE tunniste IN (1, 2, 3)
- Pohdi miten tekisit poistamisen hakusanan perusteella (ei tarvitse tässä toteuttaa).
- Ole hyvin tarkkana tehdessäsi poistoja ja päivityksiä. Jos haluat poistaa tai päivittää juuri tietyn tietueen, sinun pitää aina viitata siihen käyttäen perusavainta, joka yksilöi tietueen. Jos käytät muita ehtoja, helposti poistat tai päivität vääriä tietueita.
Päivittäminen
- Päivittäminen tapahtuu tismalleen samaan tapaan kuin poistaminenkin, mutta nyt pitää antaa myös päivitettävien kenttien uusi sisältö.
- Tee uusi kysely
UPDATE resepti SET henkilomaara = :maara WHERE reseptiID = :reseptiID
- Muuta jonkun lisäämäsi tietueen henkilömäärä toiseksi luvuksi.
- Aja kysely napauttamalla selaimen Refresh-painiketta.
- Tarkista, että muutos tapahtui tietokannan komentorivikäyttöliittymästä antamalla SQL-kysely:
SELECT * FROM resepti WHERE reseptiid = ?;
Monimutkaisemmat tulostukset
Kyselyjen kohdistuessa useampaan tauluun voi tulla kiusaus kirjoittaa silmukka, joka tekee useita hyvin samankaltaisia kyselyjä tietokantaan. Tietokanta menee kuitenkin helposti jumiin, jos siihen kohdistuu kyselyjä hirvittävän nopealla tahdilla. Hyvänä perussääntönä voi pitää: minimoi tietokantaan kohdistuvien erillisten operaatioiden määrä. Yksittäisessä kyselyssä kannattaa kuitenkin tietokanta laittaa tekemään kaikki mahdolliset laskutoimitukset ja järjestämiset.
- Listataan kaikki ruokalajit otsikkoina ja otsikon alle ruokalajiin liittyvät reseptit välilyönneillä eroteltuna.
Haetaan ensin kaikki ruokalajit ja niihin liittyvät reseptit.
SELECT ruokalaji.nimi AS ruokalaji, resepti.nimi AS resepti FROM resepti, ruokalaji WHERE ruokalaji.ruokalajiID = resepti.ruokalajiID ORDER BY ruokalaji.nimi, resepti.nimi
- Aja kysely ja käy for-silmukalla se läpi ja tulosta aluksi kultakin riviltä löytyvä ruokalaji h2-elementin sisälle.
- Nyt ruokalaji toistuu turhan moneen kertaan. Tutki onko ruokalaji sama kuin edellisellä silmukan kierroksella ja tee tulostus vain ensimmäisellä kerralla. Tässä kannattaa käyttää apuna Jinjan loop-muuttujia. Esim. loop.previtem.
- Nyt halutaan tulostaa vielä reseptit ruokalajin alle. Tulosta reseptien nimet aina niihin liittyvän ruokalajin nimen alle esim. p-elementin sisään.
- Tarkista, että
h2
jap
-elementit tulostuvat järkevästi niin, että koodi pystyy validina. - Mitäs jos haluaisit listata reseptit listana? (ul ja li-elementeillä). Kokeile muuttaa listaksi. Varmista, että rakenne pysyy validina.
- Entäs jos halutaankin listata kaikki ruokalajit? Nythän listattiin
vain sellaiset, joille on määritelty reseptejä. Kyselyä pitää muuttaa seuraavasti:
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
- Lisäksi on tarkistettava onko rivillä ruokalaji ja resepti vai pelkkä ruokalaji ja tehtävä tulostus sen mukaisesti.
- Kokeile miten uusittu tulostus toimii.
Transaktiot
Transaktioiden avulla pystytään hallitsemaan tietokantaoperaatiokokonaisuuksia. Esim. tietokantaan pitää syöttää useita tietueita peräkkäin ja jos yksikin lisäys epäonnistuu, niin kaikki lisäykset pitää pystyä peruuttamaan. Transaktio varmistaa, että keskeneräisen transaktion vaikutukset tietokantaan eivät heijastu muille tietokannan käyttäjille, kuin vasta transaktion hyväksymisen jälkeen.
- Harjoitellaan transaktioiden toimintaa lisäämällä samalla kerralla uusi ruokalaji ja siihen liittyviä reseptejä. Valmistele kaksi kyselyä, joista toinen lisää ruokalajin ja toinen reseptin.
- Transaktio täytyy aloittaa BEGIN-komennolla, jos autocommit-moodi (isolation_level=None) on päällä:
cur.execute( "BEGIN" )
sqlite-kirjastossa on oletuksena transaktiot käytössä koko ajan eli jokainen tietokannan sisältöön kohdistuva muutos täytyy erikseen kommitoida. autocommit-moodi (isolation_level=None) taas kommitoi automaattisesti kaiken. Web-sovelluksessa kannattaa useimmiten pitää autocommit-moodi päällä ja vain erikseen tarvittaessa aloittaa transaktio BEGIN-komennolla.
- Tee lisäys kuten edellä eli luo muuttuja ja sido ne kyselyihin. Aseta reseptin ruokalajiID:ksi sama kuin mikä keksimälläsi ruokalajilla on. Laita reseptin reseptiID:ksi jokin sellainen numero mikä on jo olemassa tietokannassa eli reseptin lisääminen epäonnistuu.
- Kokeile suorittaa kysely. Muista tehdä tarkistukset kyselyiden onnistumiselle (try...except). Nyt pitäisi tulla virheilmoitus reseptin lisäyksen
epäonnistumisesta. Virheen tapahtuessa tee rollback eli peruuta jo tehdyt muutokset:
Tarkista tietokannan komentorivikäyttöliittymästä onnistuiko ruokalajin lisäys.con.rollback()
- Lisää laskuri virheiden määrälle, joka on aluksi nolla.
- Jos SQL-lauseiden suorituksessa tulee virhe, niin lisää virhelaskurin määrää.
- Kaikkien SQL-lauseiden lopussa tee tarkistus onko tullut virheitä. Jos virheitä ei ole tullut, niin suorita con.commit() ja muussa tapauksessa con.rollback().
Automaattiset avainkentät
Usein käytetään avaimina kenttiä, joiden arvon keksiminen jätetään tietokannan harteille. Esim. reseptiID ja ruokalajiID ovat tällaisia kenttiä. Tähän saakka ne on keksitty itse, mutta entäs jos tietokanta keksii ne?
- Muutetaan edellistä lisäystä hieman. Poista kyselyistä reseptin INSERT-kyselystä reseptiID ja ruokalajin lisäyksestä ruokalajiID.
- Mistä nyt tiedämme mikä on reseptin ruokalajin id-numero?
Tämän saa selville pyytämällä cursorilta:
ruokalajiID = cur.lastrowid
saman voi myös kysyä kyselynä:
SELECT last_insert_rowid()
- Kokeile ohjelmaa. Saat luultavasti virheen, miksi? Korjaa ruokalajin tietoja niin, että lisäys onnistuu.
Kirjautuminen ja tietokanta
Yleensä kirjautumisessa salasana ja tunnus tarkistetaan tietokannasta. Reseptitietokannassa on mukana user-taulu, jossa on kolme eri tunnusta ja salasanaa:
Tunnus | Salasana |
---|---|
foo | bar |
bar | foo |
opiskelija | ties4080 |
salt = "suolaasuolaa"
m = hashlib.sha512()
m.update(salt.encode("UTF-8"))
m.update(salasana.encode("UTF-8"))
m.hexdigest() # tämä on tietokantaan tallennettu salasanan tarkiste
Muokkaa aiemmin tekemääsi kirjautumista siten, että edellytät kirjautumista reseptitietokannan sivuille ja
käytät tietokantaan tallennettuja tunnuksia.
Malliratkaisu, lähdekoodi ja template
Lisätietoa
Kannattaa ehdottomasti tutustua Philip Greenspunin kirjaan: SQL for Web Nerds
PostgreSQL
Voit käsitellä tietokantaasi aivan samalla tavalla kuin sqlite3-tietokantaa. Joudut vain avaamaan tietokantayhteyden eri kirjaston avulla- Asenna psycopg-kirjasto
taipip install psycopg2
pip install psycopg2-binary
- Tietokantayhteys kannattaa muodostaa psycopg2-kirjastolla. Yritä seuraavan esimerkin avulla yhdistää Googlen pilvessä olevaan postgresql-tietokantaan, joka on samanlainen, kuin edellä olet luonut sqlite3:n avulla. Huom. yhdistäminen ei onnistu yliopiston halava/jalava/users.jyu.fi-koneilta, koska niiltä on yhteydet yliopiston verkon ulkopuolelle estetty. Sinun pitää kokeilla tätä omalta koneelta. Myöskään Pythonanywheren ilmaisilta tunnuksilta ei saa yhteyttä.
import psycopg2 #yhteystiedot kannattaa lukea ympäristömuuttujista tai tiedostosta eikä tallentaa suoraan ohjelmakoodiin conn = psycopg2.connect("dbname=tietokannannimi user=käyttäjä host=ip-osoite password=salasana") cur = conn.cursor() cur.execute("SELECT * FROM Reseptit") print(cur.fetchone()) # muista myös tarvittaessa commit() tai rollback() cur.close() conn.close()
- Connections pooling
- Passing parameters to SQL queries
- Psycop2 FAQ
- Advanced topics
PostgreSQL vs SQLite3
SQL-syntaksin eroavaisuuksia SQLite3- ja PostgreSQL-tietokantojen välillä.
SQLite3 | PostgreSQL |
---|---|
AUTOINCREMENT | SERIAL, jolla korvataan INTEGER-tietotyyppi:
edellämainittu on sama kuin seuraava:
Viimeksi käytetyn arvon saa kyselyllä:
tai saman saa myös INSERT-lauseen yhteydessä seuraavasti:
|
DATETIME | TIMESTAMP |
PythonAnywhere ja MySQL
PythonAnywhere-palvelussa voit käyttää suosittua MySQL-tietokantaa. MySQL on joissain tapauksissa ominaisuuksiltaan vajavainen relaatiotietokanta. Jos mahdollista, käytä ennemmin PostgreSQL-tietokantaa, johon palataan myöhemmin tällä kurssilla. Tarvittaessa yös SQLite-tietokanta toimii PythonAnywhere-palvelussa.
- Kopioi resepti_mysql.sql-tiedosto PythonAnywhere-palveluun Files-välilehdellä. MySQL ei kelpuuta aivan samaa SQL-syntaksia kuin SQLite.
- Mene PythonAnywheren ylläpitonäkymässä Databases-välilehdelle. Alusta itsellesi MySQL-tietokanta keksimälle sille sopiva salasana. Älä käytä samaa salasanaa kuin muissa järjestelmissä! Salasana tulee näkymään ohjelmakoodissa.
- Luo seuraavaksi varsinainen tietokanta. Anna tietokannalle nimeksi reseptit
- Siirry luomasi tietokannan konsoliin Start a console on: tunnus$reseptit. Jos saat herjan, että kaikki konsolisi ovat käytössä, käy Consoles-välilehdellä sulkemassa ylimääräiset
- MySQL Monitor käynnistyy. \h kertoo sinulle käytettävissä olevat komennot.
- Luo tietokannan taulut ja sisältö komennolla:
source resepti_mysql.sql
- Sulje konsoli komennolla quit
- Siirry PythonAnywheressa Web-välilehdelle ja etsi sieltä virtualenv-kohta.
Jos sinulla ei vielä ole virtualenviä, luo virtualenv ensin.
Avaa bash tässä virtualenvissä ja asenna seuraavat kirjastot:
pip install mysqlclient pip install mysql.connector
- Muista myös päivittää wsgi-tiedosto oikeanlaiseksi
- Kopioi edellä tekemäsi sqlite3-sovelluksen koodi PythonAnywhere-palveluun.
Muuta sovellus toimimaan mysql-tietokannalla seuraavien ohjeiden avulla:
- jätä sqlite3-kirjasto pois käytöstä ja ota sen sijaan mysql.connector-kirjasto:
Lue connecting to MySQL Using Connector/Python ja Connector/Python Connection Poolingimport mysql.connector import mysql.connector.pooling
Tietokantayhteyden jakaminen aka connection pooling
Muuta tietokantayhteyden avaaminen seuraavanlaiseksi. Luodaan ensin tietokantayhteyksiä varten pooli, jolta sitten pyydetään vapaana oleva tietokantayhteys. Yksi yhteys ei riitä, koska sovellusta saattaa käyttää useampi samaan aikaan.
pool täytyy tässä tapauksessa olla globaali muuttuja, jotta siitä saa oikeasti hyötyä.#tietokantayhteyden asetustiedot kannattaa lukea erillisestä tiedostosta #{ # "database": "tunnus$reseptit", # "user": "tunnus", #tämä on pythonanywheressa käyttämäsi tunnus # "passwd": "salasana", #tämä on mysql-tietokantaa luodessasi antamasi salasana # "host":"tunnus.mysql.eu.pythonanywhere-services.com" #} import json import mysql.connector import mysql.connector.pooling import mysql.connector.errors from mysql.connector import errorcode tiedosto = open("dbconfig.json", encoding="UTF-8") dbconfig = json.load(tiedosto) try: pool=mysql.connector.pooling.MySQLConnectionPool(pool_name="tietokantayhteydet", pool_size=2, #PythonAnywheren ilmaisen tunnuksen maksimi on kolme autocommit=True, #asettaa autocommitin päälle. **dbconfig ) except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Tunnus tai salasana on väärin") elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Tietokantaa ei löydy") else: print(err) def kysely(): global pool try: con = pool.get_connection() try: cur = con.cursor(dictionary=True) except mysql.connector.errors.OperationalError: print("tietokantayhteyttä ei saada" + err) except: #tämä osa ei ole pakollinen ... finally: #tämä suoritetaan aina lopuksi con.close() #vapautetaan tietokantayhteys takaisin pooliin
Tietokantayhteys täytyy muistaa sulkea heti käyttämisen jälkeen. Poolia käytettäessä yhteys ei oikeasti sulkeudu vaan palaa pooliin odottamaan uutta käyttöä.
con.close()
Jos saat virheilmoituksen:
User 'tunnus' has exceeded the 'max_user_connections' resource (current value: 3)
on yhteyksiä liikaa käytössä. Tarkista onko mysql-tietokantaan auki konsoliyhteys tai oletko unohtanut sovelluksessasi sulkea avattuja yhteyksiä. Mysql sulkee automaattisesti kaikki käyttämättä olevat avonaiset yhteydet viiden minuutin jälkeen. Muista sulkea tietokantayhteys kaikissa tilanteissa eli myös sovelluksen kaatuessa ( try...except...finally)
Tietokantayhteyden tunnus ja salasana kannattaa tallentaa johonkin erilliseen tiedostoon, josta ne sitten haetaan. Näin ne eivät paljastu heti, jos jostain syystä lähdekoodi joutuu vääriin käsiin.
- Poista PRAGMA-lause ja row_factory
- Jos käytit sqliten kanssa dict_factorya, niin poista se. Mysql:ssä
saat saman toiminnallisuuden seuraavasti:
cursor = con.cursor(dictionary=True)
- Muuta cursorin luominen seuraavanlaiseksi:
from mysql.connector.cursor import MySQLCursorPrepared cur = con.cursor(cursor_class=MySQLCursorPrepared, buffered=True, dictionary=True)
- preparoidut kyselyt täytyy kirjoittaa uuteen muotoon. mysql ei
ymmärrä nimettyjä parametreja aivan samassa muodossa kuin SQLite vaan on
käytettävä kunkin parametrin kohdalla
%(muuttuja)s
-merkintää. vrt. SQLiten:muuttuja
.sql = '''SELECT nimi, kuvaus, henkilomaara, reseptiid, ruokalajiid FROM resepti WHERE henkilomaara = %(muuttuja)s
- Jinja-templatessa täytyy muuttaa kenttien nimet, jotka ovat mysql:n jäljiltä casesensitive.
- jätä sqlite3-kirjasto pois käytöstä ja ota sen sijaan mysql.connector-kirjasto:
SQLite3:n ja MySQL:n eroavaisuuksia SQL-syntaksissa
-
SQLite3 ja vertailufunktiot
SQLite3 ei osaa tehdä caseinsensitive-vertailua skandinaavisilla merkeillä eli esim. UPPER('BÄÄ') == UPPER('bää') on sen mielestä epätosi. Jotta saa asiat toimimaan myös skandeilla haluamallaan tavalla pitää tehdä oma lajittelufunktio. Esim. seuraavassa käytetään yhtä sqliten sisäänrakennetuista collation (lajittelu) -funktioista ja yritetään hakea kaikki käyttäjät joiden etunimi olisi esim. Källe tai källe, mutta tämä ei toimi eikä auta vaikka käyttäisi upper-funktiota:SELECT * FROM user WHERE etunimi = 'källe' COLLATE NOCASE
tämän NOCASE-funktion voi siis korvata omalla python-funktiolla, jonka jälkeen edellä oleva kysely alkaa toimia.
def vertaa_nocase(string1, string2): if string1.lower() == string2.lower(): return 0 elif string1.lower() < string2.lower(): return 1 else: return -1 #annetaan sqlitelle edellä luotu vertailufunktio con.create_collation("vertaa_nocase", vertaa_nocase) cur.execute("SELECT x FROM user WHERE etunimi = 'källe' COLLATE vertaa_nocase")
SQLitessa on sisäänrakennettuna kolme lajittelufunktiota BINARY, NOCASE ja RTRIM, joista NOCASE ei toimi skandinaavisten merkkien kanssa.
Kts. ICU. - SQLitessa UPPER-funktio ei oletuksena tue skandinaavisia merkkejä. Kts. edellä oleva vertailufunktio. Kts. create_collation. ja ICU.
- BEGIN TRANSACTION vs START TRANSACTION
- INT-tyyppi on SQLitessä paljon suurempi kuin MySQL:ssä. MySQL:ssä pitää käyttää
tyyppinä
BIGINT
, jos haluaa todella suuria kokonaislukuja - AUTOINCREMENT vs AUTO_INCREMENT
- Parametrien välitys kursorin execute-metodille on MySQL:ssä erilainen
kuin SQlitellä
#SQLite sql = """SELECT * FROM User WHERE name = :nimi""" # MySQL sql = """SELECT * FROM User WHERE name = %(nimi)s""" con.execute( {"nimi": "tommi"})
- SQLite kelpuuttaa INSERT-lauseissa taulun nimen ""-merkkien sisään laitettuna, mutta MySql ei kelpuuta
- SQL-kyselyissä taulujen nimet ovat joissakin ympäristöissä case-sensitive. Kts. Identifier Case Sensitify.
Kirjoita kaikissa kyselyissä taulujen nimet pienillä kirjaimillä, niin vältät mahdolliset ongelmat.
Parempi olisi kirjoittaa näin:SELECT t FROM Taulu
SELECT t FROM taulu
- MySQL on oletuksena case insensitive, kun vertaillaan merkkijonoja ja CHAR-, VARCHAR- ja TEXT-tyyppisiä kenttiä. Tämä poikkeaa lähes kaikista muista relaatiotietokannoista.
- Kyselyjen tuloksissa ei saa olla kahta samannimistä kenttää, jos käsittelee kyselyn tuloksia dict-muodossa.
Järkevintä on varmistaa jo kyselyssä, että lopputuloksessa ei voi olla kahta samaa nimeä. Seuraava
kysely hajoaa helposti:
Parempi olisi kirjoittaa näin:SELECT t.id, k.id FROM t, k WHERE t.tunniste = k.id
SELECT t.id AS tid, k.id AS kid FROM t, k WHERE t.tunniste = k.id
- Vanhemmat mysql-versiot eivät tue CHECK-lauseita, vaikka ne kyllä SQL-lauseista
parsivat. PythonAnywheren mysql on vanha v5.7, joka ei osaa toteuttaa CHECK-lauseella tehtyjä
tarkistuksia. Uudet MySQL-tietokannat edellyttävät useampaan kenttään kohdistuvien CHECK-lauseiden
määrittelyjen olevan vasta kenttämäärittelyjen jälkeen. Seuraava ei toimi uudemmilla mysql-tietokannoilla:
Pitää kirjoittaa muotoon:CREATE TABLE kilpailut ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, kisanimi VARCHAR(128) NOT NULL, alkuaika DATETIME NOT NULL CHECK( alkuaika < loppuaika), loppuaika DATETIME NOT NULL CHECK( loppuaika > alkuaika), kesto INTEGER NOT NULL CHECK( kesto > 0 ) );
CREATE TABLE kilpailut ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, kisanimi VARCHAR(128) NOT NULL, alkuaika DATETIME NOT NULL, loppuaika DATETIME NOT NULL, kesto INTEGER NOT NULL CHECK( kesto > 0 ), CONSTRAINT kilpailu_alkuaika CHECK( alkuaika < loppuaika), CONSTRAINT kilpailu_loppuaika CHECK( loppuaika > alkuaika) );
- Viimeksi lisätyn id:n saa mysql-kannassa kyselyllä:
SELECT LAST_INSERT_ID()
-
- Lisätietoa: Python and MySQL Database: A Practical Introduction
- Managing database connections (PythonAnywhere)
- Python Database Connection Pooling with MySQL
- Connector/Python Connection Pooling
Käyttäjien kommentit