Python, tietokannat ja sqlite

Tällä luennolla käsitellään tietokantoja Pythonin näkökulmasta.

Luentovideon lyhyt esimerkki

Vanhempi sqlite-luento, vanhemman luentovideon lyhyt esimerkki

Uusin pitempi esimerkki: videorekisteri (lähdekoodi, template1, template2), video.sql

Vanha esimerkki (suorita suoraan python-tulkilla): db.py (lähdekoodi)

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.).

SQLite-tietokanta

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

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.

users.jyu.fi:ssä olevassa pythonissa on valmiina mukana sqlite3-kirjasto

Muut tietokannat vaativat omat kirjastonsa:

Tietokantayhteys

Mallitietokanta

Tietokanta voi käsitellä komentoriviltä sqlite3-komennolla, joka avaa suoran komentorivikäyttöliittymän sqlite3-tietokantaan. Yleensä aina kannattaa asettaa tietokantaan heti WAL (Write-Ahead Logging) päälle: 

.read resepti.sql
PRAGMA journal_mode = wal;

Tietokantayhteys sqlite-tietokantaan avataan seuraavasti:

import sqlite3
#asetetaan samalla autocommit-moodi päälle (isolation_level=none)
con = sqlite3.connect( '/absoluuttinen/polku/tietokantaan/mallidb', isolation_level=None)

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!

try:
    # sqlite haluaa absoluuttisen polun
    # os.path.abspath muuntaa suhteellisen polun absoluuttiseksi. Vaatii myös import os -rivin
    con = sqlite3.connect(os.path.abspath('../../hidden/resepti'))
    con.row_factory = sqlite3.Row
    # kirjoittaa rivin lokitiedostoon
    logging.debug("onnistui!")
except Exception as e:
    logging.debug("Kanta ei aukea")
    logging.debug(str(e))
finally:
    #yhteys täytyy _aina_ sulkea
    con.close()

sqlite-tietokannoissa kannattaa asettaa yhteydelle heti seuraavat asetukset:

con.row_factory = sqlite3.Row

row_factory mahdollistaa kursorin kenttiin viittaamisen kenttien nimillä.

Jos tietokantayhteyttä ei enää tarvita niin se on syytä sulkea:

con.close()

Asetetaan tietokantayhteydessä viite-eheydet (foreign keys) käyttöön: 

con.execute("PRAGMA foreign_keys = ON;")

Kyselyt

Tietokantayhteyden avaamisen jälkeen kantaan voidaan tehdä kyselyjä.

cur = con.cursor()

try:
        cur.execute("""
        SELECT *
        FROM resepti
        """)
except sqlite3.Error:
        print("nyt tuli virhe: %s" % sys.exc_info()[0])

for row in cur.fetchall():
    print(row)

Kursori palauttaa python-listan, jonka sisällä on tupleja. Yksi tuple vastaa aina yhtä tietuetta:

[(u'Ananas Con Carne', u'Tulinen', 4, 1, 2), (u'Tiramisu', u'Hieno herkku', 6, 2, 7), (u'Banaanicurry', u'Mainio kasvisruoka', 2, 3, 2), (u'Porkkanapiirakka', u'Suuri herkku', 8, 4, 7), (u'Rommikakku', u'Hyytel\xf6kakku, ei tarvi paistaa', 6, 5, 7), (u'Kauralastut', u'Helppo ja nopea', 4, 6, 7)]

Tuples, Lists and Dictionaries

Yleensä tietokantakyselyissä käytetään ns. valmisteltuja kyselyjä, mutta pythonin sqlitekirjaston kanssa tämä ei ole mahdollista.

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

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

  1. Kysely valmistellaan - valmisteltu kysely suoritetaan seuraavilla suorituskerroilla tehokkaammin (prepare).
  2. Kysely suoritetaan (execute).
  3. Kyselyn tulos käydään läpi kursorin avulla for-silmukassa (voi käyttää myös: fetchone, fetchmany tai fetchall).

Transaktiot

SQL As Understood By SQLite

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.

Sqlite-tietokannassa transaktiot eivät ole oletuksena päällä, mutta Pythonin sqlite-kirjastossa tranksaktiot taas ovat oletuksena päällä.

Monen muun kirjaston kohdalla transaktiot saattaa joutua erikseen käynnistämään. Tietokantaan kohdistuvat muutokset täytyy aina hyväksyä commit-metodilla. Sqliteen saa autocommit moodin päälle, kun asettaa tietokantayhteyden avaamisen yhteydessä isolation_level=None.

Jos autocommit ei ole päällä (oletus), on transaktiot hyväksyttävä seuraavasti: 

con.commit()

Jos autocommit on päällä, voi kuitenkin aloittaa transaktion BEGIN-komennolla. 

cur.execute("BEGIN")

Tämän jälkeen transaktion voi joko hyväksyä: 

con.commit()

tai perua:

con.rollback()

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("BEGIN") #aloitetaan transaktio
    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 sqlite3.Error:
        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 sqlite3.Error:
        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 sqlite3.Error:
        # 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).

Transaktio hyväksytään komennolla con.commit() tai perutaan komennolla con.rollback()

Controlling Transactions in SQLite

SQLite

Ohjeita SQLite-tietokannan käyttöön SQLite-pääteohjauksesta.

Esimerkki

Esimerkkikoodi 
#!/polku/omaan/virtuaaliymparistoon/venv/bin/python
# -*- coding: utf-8 -*-

import sqlite3
import sys

# transaktiot ovat oletuksena käytössä
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 sqlite3.Error:
        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 sqlite3.Error:
        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 sqlite3.Error:
        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 sqlite3.Error:
        # 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:

SQLAlchemy

SQL Relay

What is the best way to make persistent connections to a database?

Esimerkkejä

Uusi esimerkki: videorekisteri (lähdekoodi, template1, template2)

db.py (lähdekoodi)

Tarvitsetko oikeasti tietokantaa?

Mieti aina tarkkaan onko tietokannasta sinulle varmasti hyötyä vai riittääkö esim. pelkkä JSON-tiedosto? Onko tarpeen tehdä kyselyjä? Onko tietoja päivitettävä usein?

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

Lisätietoa

Käyttäjien kommentit

Kommentoi Lisää kommentti