Tietokannat ja Python
- Luentotaltiointi
- Yleistä tietokannoista
- Python Database API Specification v2.0 (PEP249)
- Tietokantayhteys
- Kyselyt
- Transaktiot
- Esimerkki
- Tietokantayhteyden jakaminen
- Lisätietoa
Tällä luennolla käsitellään tietokantoja Pythonin näkökulmasta.
Jotkin asiat ovat muuttuneet luennon jälkeen. Tarkista pääteohjaustehtävästä viimeisimmät.
Luentotaltiointi
- sovellus09.wmv 37M
- sovellus09.mp3 29M
- youtube
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 ao. kurssien materiaaleilla.
Käsiteltäessä tietokantoja on aina hyvä pitää mielessä se perussääntö, että annetaan tietokannan tehdä eri operaatiot mahdollisimman pitkälle. Siispä esimerkiksi viite-eheydet, rajoitteet ja lajittelut kannattaa aina tehdä suoraan kannassa, jos mahdollista. Myös esimerkiksi haut kannattaa rajata mahdollisimman tarkkaan jo SQL-lausetta muodostaessa. Kannassa operoiminen on merkittävästi tehokkaampaa kuin tehdä vastaavat asiat vasta ohjelmakoodin puolella. Tehokkuuden lisäksi operaatioiden tekeminen ohjelmakoodissa on paljon virhealttiimpaa kuin että tietokanta tekisi vastaavat operaatiot itse.
Lisäksi kannattaa pitää mielessä se, että jokainen tietokantakutsu syö nopeutta sovellukselta (merkittävää siinä vaiheessa, kun tehdään isoja sovelluksia). Tietokantakutsut kannattaa siis miettiä tarkkaan siten, ettei kantaan tehdä turhia kutsuja (ei samoja kutsuja useaan kertaan samalla sivulla; ei päivitystä, jos ei ole päivitettävää jne.).
Python Database API Specification v2.0 (PEP249)
PEP249 määrittelee rajapinnan jolla käsitellään tietokantoja.
On syytä käyttää vain PEP249-rajapinnan toteuttavia tietokantakirjastoa jolloin varmistaa parhaiten ohjelmansa siirrettävyyden.
sqlite3-kirjastolla voidaan käsitellä sqlite3-tietokantoja.
Asenna tarvittaessa virtuaaliympäristöösi sqlite3:
pip install sqlite3
Muut tietokannat vaativat omat kirjastonsa:
Tietokantayhteys
Tietokantayhteys sqlite-tietokantaan avataan seuraavasti:
import sqlite3
con = sqlite3.connect('/polku/tietokantaan/mallidb')
Muihin tietokantoihin yhteys avataan samaan tapaan.
Tietokannasta riippuen voidaan tarvita lisäparametreja mm. tietokantapalvelimen ip-osoite, tunnus, salasana jne.
Yhteyden avaaminen kannattaa upottaa try...except-lohkoon koska yhteyden avaaminen ei aina onnistu!
sqlite-tietokannoissa kannattaa asettaa yhteydelle heti seuraavat asetukset:
con.row_factory = sqlite3.Row
text_factory mahdollistaa muiden kuin UTF8-merkkijonojen käyttämisen. Ei tarvitse käyttää, koska käytämme UTF-8-merkkijonoja kaikkialla
row_factory mahdollistaa kursorin kenttiin viittaamisen kenttien nimillä.
Jos tietokantayhteyttä ei enää tarvita niin se on syytä sulkea:
con.close()
Kyselyt
Tietokantayhteyden avaamisen jälkeen kantaan voidaan tehdä kyselyjä.
cur = con.cursor()
try:
cur.execute("""
SELECT *
FROM resepti
""")
except:
print "nyt tuli virhe: %s" % sys.exc_info()[0]
for row in cur.fetchall():
print row
Yleensä tietokantakyselyissä käytetään ns. valmisteltuja kyselyjä mutta pythonin sqlitekirjaston kanssa tämä ei ole mahdollista. Esim. Postgresql tukee prepare-komentoa
Dynaamiset kyselyt sidotuilla muuttujilla
Dynaamisissa kyselyissä kyselyyn sidotaan mukaan muuttujia.
Muuttujia ei koskaan pidä sijoittaa suoraan SQL-lauseeseen. vrt. merkkijonot
Älä koskaan tee näin:
cur.execute("""
SELECT *
FROM resepti
WHERE nimi = %s
""" % muuttuja
Merkkijono-operaatioiden käyttäminen altistaa ohjelman SQL-injection-hyökkäyksille. SQL-lauseen muodostaminen on myös hankalampaa, koska joutuu itse käsittelemään erikseen merkkijonot ja numerot. Sidotuissa kyselyissä tietokanta hoitaa eri tyypit.
Esimerkkejä
resepti1 = "Porkkanapiirakka"
cur.execute("""
SELECT *
FROM resepti
WHERE nimi = ?
""", (resepti1,))
print "Haetaan yksi resepti"
for (nimi, kuvaus, hlomaara, id, tyyppi) in cur:
print 'nimi:\t%s' % (nimi)
print 'kuvaus:\t%s' % (kuvaus)
print 'hlomaara:\t%s' % (hlomaara)
print 'id:\t%s' % (id)
print 'tyyppi:\t%s\n' % (tyyppi)
resepti2 = "Rommikakku"
reseptit = (resepti1, resepti2)
sql = """
SELECT *
FROM resepti
WHERE nimi = ?
OR nimi = ?
"""
cur.execute(sql, reseptit)
print "Haetaan kaksi reseptiä"
for rivi in cur:
print ('nimi:\t%s' % (rivi[0]) )
print ('kuvaus:\t%s' % (rivi[1]) )
print ('hlomaara:\t%s' % (rivi[2]) )
print ('id:\t%s' % (rivi[3]) )
print ('tyyppi:\t%s\n' % (rivi[4]) )
print "\nSama kuin edellä, mutta erilaisella syntaksilla"
reseptit = {"rese1":resepti1, "rese2":resepti2}
cur.execute("""
SELECT *
FROM resepti
WHERE nimi = :rese1
OR nimi = :rese2
""", reseptit)
# Läpikäynti onnistuu seuraavilla tavoilla jos on asetettuna
# con.row_factory = sqlite3.Row
for rivi in cur:
for i in rivi.keys():
print ('%s:\t%s' % (i, rivi[i]) )
print "\nKursorin voi käydä läpi vain kerran. Sama kysely on tehtävä uudelleen"
cur.execute("""
SELECT *
FROM resepti
WHERE nimi = :rese1
OR nimi = :rese2
""", reseptit)
for rivi in cur:
print ('Nimi:\t%s' % (rivi['Nimi']) )
print ('Kuvaus:\t%s' % (rivi['Kuvaus']) )
print ('Henkilomaara:\t%s' % (rivi['Henkilomaara']) )
print ('ReseptiID:\t%s' % (rivi['ReseptiID']) )
print ('RuokalajiID:\t%s' % (rivi['RuokalajiID']) )Kyselyn suorittamisen vaiheet
- Kysely valmistellaan - valmisteltu kysely suoritetaan seuraavilla suorituskerroilla tehokkaammin (prepare).
- Kysely suoritetaan (execute).
- Kyselyn tulos käydään läpi kursorin avulla for-silmukassa (voi käyttää myös: fetchone, fetchmany tai fetchall).
Transaktiot
Transaktioiden avulla pystytään hallitsemaan tietokantaoperaatiokokonaisuuksia. Esimerkiksi, jos tietokantaan pitää syöttää useita tietueita peräkkäin ja jos yksikin lisäys epäonnistuu, niin kaikki lisäykset pitää pystyä peruuttamaan (vrt. verkkopankki). Transaktio varmistaa, etteivät keskeneräisen transaktion vaikutukset tietokantaan heijastu muille tietokannan käyttäjille kuin vasta transaktion hyväksymisen jälkeen.
Pythonim sqlite-kirjastossa transaktiot ovat oletuksena käytössä. Monen muun kirjaston kohdalla ne saattaa joutua erikseen käynnistämään. Tietokantaan kohdistuvat muutokset täytyy aina hyväksyä commit-metodilla
con.commit()
Myös tietojen lisääminen, poistaminen ja muuttaminen onnistuu samalla idealla kuin kyselyjen tekeminen. Seuraavassa esimerkkejä tietojen lisäämisestä, muuttamisesta ja poistamisesta sekä transaktioista:
def reseptit():
cur.execute("""
SELECT *
FROM resepti
""")
for rivi in cur:
print ('Nimi:\t%s\tReseptiID:\t%s\tRuokalajiID:\t%s' % (rivi['Nimi'],rivi['ReseptiID'],rivi['RuokalajiID']) )
print "\nTilanne ennen Talkkunahyveen lisäämistä"
reseptit()
try:
cur.execute("""
INSERT INTO Resepti (Nimi, Kuvaus, Henkilomaara, RuokalajiID)
VALUES (:nimi, :kuvaus, :henkilomaara, :ruokalajiID )
""", {"nimi":u"Talkkunahyve", "kuvaus":u"perinteinen jälkkäri",
"henkilomaara":4,"ruokalajiID":7})
except:
print("nyt tuli virhe: %s" % sys.exc_info()[0] + "\n")
print("Lisääminen epäonnistui\n")
con.rollback()
#palauttaa viimeisimmän lisätyn tietueen perusavaimen
reseptiID = cur.lastrowid
print "\nTilanne Talkkunahyveen lisäämisen jälkeen"
reseptit()
try:
cur.execute("""
UPDATE Resepti
SET Nimi = 'Puolukkahyve'
WHERE ReseptiID = ?
""", (reseptiID,))
except:
print("nyt tuli virhe: %s" % sys.exc_info()[0] + "\n")
print("Päivittäminen epäonnistui\n")
con.rollback()
# try:
# cur.execute("""
# DELETE From Resepti
# WHERE ReseptiID = :id
# """, {"id":reseptiID} )
# except:
# print("nyt tuli virhe: %s" % sys.exc_info()[0] + "\n")
# print("Poistaminen epäonnistui\n")
# con.rollback()
#ilman seuraavaa riviä muutokset eivät jää voimaan!
con.commit()
print "\nTilanne lopussa"
reseptit()cur.lastrowid palauttaa viimeisen tietokantaan lisätyn avaimen (primary key).
- Saman saa selville SQL-kysellä: SELECT last_insert_rowid()
- Kätevä keino saada selville lisätty ID silloin, kun tietokannassa on autoincrement-määritys ID:lle (primary keylle).
- Tietokantariippuvainen toiminto
- ROWIDs and the INTEGER PRIMARY KEY
Transaktio hyväksytään komennolla con.commit() tai perutaan komennolla con.rollback()
Controlling Transactions in SQLite
Esimerkki
Esimerkkikoodi
#!/polku/omaan/virtuaaliymparistoon/venv/bin/python
# -*- coding: utf-8 -*-
import sqlite3
import sys
con = sqlite3.connect( '/wwwhome/home/tjlahton/public_html/hidden/resepti.db')
con.row_factory = sqlite3.Row
cur = con.cursor()
try:
cur.execute("""
SELECT *
FROM resepti
""")
except:
print "nyt tuli virhe: %s" % sys.exc_info()[0]
for row in cur.fetchall():
print row
resepti1 = "Porkkanapiirakka"
cur.execute("""
SELECT *
FROM resepti
WHERE nimi = ?
""", (resepti1,))
print "Haetaan yksi resepti"
for (nimi, kuvaus, hlomaara, id, tyyppi) in cur:
print 'nimi:\t%s' % (nimi)
print 'kuvaus:\t%s' % (kuvaus)
print 'hlomaara:\t%s' % (hlomaara)
print 'id:\t%s' % (id)
print 'tyyppi:\t%s\n' % (tyyppi)
resepti2 = "Rommikakku"
reseptit = (resepti1, resepti2)
sql = """
SELECT *
FROM resepti
WHERE nimi = ?
OR nimi = ?
"""
cur.execute(sql, reseptit)
print "Haetaan kaksi reseptiä"
for rivi in cur:
print ('nimi:\t%s' % (rivi[0]) )
print ('kuvaus:\t%s' % (rivi[1]) )
print ('hlomaara:\t%s' % (rivi[2]) )
print ('id:\t%s' % (rivi[3]) )
print ('tyyppi:\t%s\n' % (rivi[4]) )
print "\nSama kuin edellä, mutta erilaisella syntaksilla"
reseptit = {"rese1":resepti1, "rese2":resepti2}
cur.execute("""
SELECT *
FROM resepti
WHERE nimi = :rese1
OR nimi = :rese2
""", reseptit)
# Läpikäynti onnistuu seuraavilla tavoilla jos on asetettuna
# con.row_factory = sqlite3.Row
for rivi in cur:
for i in rivi.keys():
print ('%s:\t%s' % (i, rivi[i]) )
print "\nKursorin voi käydä läpi vain kerran. Sama kysely on tehtävä uudelleen"
cur.execute("""
SELECT *
FROM resepti
WHERE nimi = :rese1
OR nimi = :rese2
""", reseptit)
for rivi in cur:
print ('Nimi:\t%s' % (rivi['Nimi']) )
print ('Kuvaus:\t%s' % (rivi['Kuvaus']) )
print ('Henkilomaara:\t%s' % (rivi['Henkilomaara']) )
print ('ReseptiID:\t%s' % (rivi['ReseptiID']) )
print ('RuokalajiID:\t%s' % (rivi['RuokalajiID']) )
def reseptit():
cur.execute("""
SELECT *
FROM resepti
""")
for rivi in cur:
print ('Nimi:\t%s\tReseptiID:\t%s\tRuokalajiID:\t%s' % (rivi['Nimi'],rivi['ReseptiID'],rivi['RuokalajiID']) )
print "\nTilanne ennen Talkkunahyveen lisäämistä"
reseptit()
try:
cur.execute("""
INSERT INTO Resepti (Nimi, Kuvaus, Henkilomaara, RuokalajiID)
VALUES (:nimi, :kuvaus, :henkilomaara, :ruokalajiID )
""", {"nimi":u"Talkkunahyve", "kuvaus":u"perinteinen jälkkäri",
"henkilomaara":4,"ruokalajiID":7})
except:
print("nyt tuli virhe: %s" % sys.exc_info()[0] + "\n")
print("Lisääminen epäonnistui\n")
con.rollback()
#palauttaa viimeisimmän lisätyn tietueen perusavaimen
reseptiID = cur.lastrowid
print "\nTilanne Talkkunahyveen lisäämisen jälkeen"
reseptit()
try:
cur.execute("""
UPDATE Resepti
SET Nimi = 'Puolukkahyve'
WHERE ReseptiID = ?
""", (reseptiID,))
except:
print("nyt tuli virhe: %s" % sys.exc_info()[0] + "\n")
print("Päivittäminen epäonnistui\n")
con.rollback()
# try:
# cur.execute("""
# DELETE From Resepti
# WHERE ReseptiID = :id
# """, {"id":reseptiID} )
# except:
# print("nyt tuli virhe: %s" % sys.exc_info()[0] + "\n")
# print("Poistaminen epäonnistui\n")
# con.rollback()
#ilman seuraavaa riviä muutokset eivät jää voimaan!
con.commit()
print "\nTilanne lopussa"
reseptit()
Tietokantayhteyden jakaminen
Raskaissa WWW-sovelluksissa tietokantayhteyttä ei avata ja suljeta yhtenään vaan samaa yhteyttä pidetään auki pitempään ja jaetaan eri prosessien kesken. Tietokantayhteyksiä avataan kerralla useita ja niitä säilytetään ja jaetaan tarpeen mukaan. kts. Connection pool.
Työkaluja tietokantayhteyden tehostamiseen:
What is the best way to make persistent connections to a database?
Käyttäjien kommentit