Pysqlite

Databáze jsou pro větší i menší programy velkým přínosem. Umožňují rychlejší přístup k datům a rychlejší zapisování. Od pythonu 2.5 je součástí standardní distribuce modul sqlite3 (pokud používáte nižší verzi pythona, stáhněte si knihovnu na oficiálních stránkách), který toto umožňuje.

Tento modul byl napsán Gerhardem Häringen a poskytuje SQL rozhraní DB-API 2.0.

Vytvoření spojení

K vytvoření spojení se používá funkce connect.

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
db.commit()
db.close()

Funkce connect nejprve projde aktuální pracovní adresář a za předpokladu, že nalezne soubor db.txt, připojí se k němu. Pokud tento soubor neexistuje, bude vytvořen. Pomocí metody commit() se změny provedené na databázi uloží. Spojení se uzavírá pomocí metody close.

Databáze se pochopitelně může jmenovat jakkoliv, ale je zde jedno speciální pojmenování:

db = sqlite.connect(":memory:")

Předchozí příklad vytvoří databázi v RAMce?.

Vytvoření tabulky

Jakmile otevřeme spojení s databází, můžeme na ní vykonávat standardní SQL příkazy pomocí metody execute:

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
db.execute("create table osoby(jmeno, prijmeni)") 
db.commit()
db.close()

Zapisování do databáze

Zapisování do tabulky se také pochopitelně provádí pomocí metody execute:

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
db.execute("create table osoby(jmeno, prijmeni)") 
db.execute("insert into osoby(jmeno, prijmeni) values('Jakub', 'Vojáček')")
db.commit()
db.close()

Při zapisování jsme jako hodnoty použili 'Jakub', 'Vojáček'. V opravdovém programu ale budeme většinou chtít zapisovat hodnotu nějakých proměnných. Dalo by se to vyřešit takto:

jmeno = 'Jakub'
prijmeni = 'Vojáček'
db.execute("insert into osoby(jmeno, prijmeni) values('%s', '%s')" % (jmeno, prijmeni))

Předchozí způsob sice funguje, ale není to doporučený způsob, protože je zranitelný vůči SQL injekci. Pysqlite poskytuje dva možné způsob, jak vložit proměnné do sql příkazu:

jmeno = 'Jakub'
prijmeni = 'Vojáček'
db.execute("insert into osoby(jmeno, prijmeni) values(?, ?)", (jmeno, prijmeni))

Další způsob je:

jmeno = 'Jakub'
prijmeni = 'Vojáček'
db.execute("insert into osoby(jmeno, prijmeni) values(:jmeno, :prijmeni)", {"jmeno" : jmeno, "prijmeni" : prijmeni})

Čtení dat z databáze

Pro přečtení dat z databáze se opět používá metoda execute:

#-*- coding: utf-8 -*-
#Předpokládá se, že v tabulce osoby máte uloženy nějaké údaje
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
vystup = db.execute("select * from osoby")
print vystup
db.commit()
db.close()

Místo očekávaného výstupu na nás vybaflo <sqlite3.Cursor object at 0x00A43560>. To pro nás ale pochopitelně není žádná překážka. Nyní je více možností, jak získat data. Buď můžeme použít metodu fetchall, nebo využít toho, že vystup je iterovatelný.

vystup = db.execute("select * from osoby")
print vystup.fetchall()
#Výstup:
[(u'Jakub', u'Voj\xe1\u010dek'), (u'Pavel', u'Kosina')]

Výstup je nyní daleko srozumitelnější. Ještě si ukážeme druhý způsob, jak přečíst data z databáze:

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
vystup = db.execute("select * from osoby")
for zaznam in vystup:
    print zaznam
vystup = db.execute("select * from osoby")
print 
for jmeno, prijmeni in vystup:
    print jmeno, prijmeni
db.commit()
db.close()

V tomto případě je výstup:

(u'Jakub', u'Voj\xe1\u010dek')
(u'Pavel', u'Kosina')

Jakub Vojáček
Pavel Kosina

Další funkce

Nyní se dá říci, že už znáte všechny základní funkce této knihovny. Nicméně tento modul umí několik dalších funkcí, které by vám mohli ušetřit práci.

executescript(sql_skript)

Pomocí tohoto příkazu se dá spustit více SQL příkazů najednou.

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
db.executescript('''
create table osoby(jmeno, prijmeni);
insert into osoby values('jakub', 'vojacek');
create table mesta(jmeno);
insert into mesta values('Praha');
insert into mesta values('Brno');
''')
db.commit()
db.close()

executemany(sql, sekvence)

Provede sql příkaz pro všechny části dané sekvence.

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite

class Cisla:
    def __init__(self):
        self.cislo = 0
    def __iter__(self):
        return self
    def next(self):
        if self.cislo == 100:
            raise StopIteration
        self.cislo = self.cislo + 1
        return (self.cislo, )

db = sqlite.connect("db")
db.execute("create table cisla(cislo)")
cisla = Cisla()
db.executemany("insert into cisla values(?)", cisla)
for cislo in db.execute("select * from cisla"):
    print cislo
db.commit()
db.close()

fetchone()

Pokud víte, že SELECT vrátil pouze jeden řádek tabulky, můžete použít metodu fetchone místo fetchall(). Jediný rozdíl je v tom, že fetchone vrací n-tici, zatímco fetchall vrací seznam n-tic:

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
db.execute("create table cisla(cislo)")
db.executescript('''
insert into cisla values(1);
insert into cisla values(2);
''')
vystup = db.execute("select * from cisla where cislo = 1")
print vystup.fetchone()
vystup = db.execute("select * from cisla where cislo = 1")
print vystup.fetchall()
db.commit()
db.close()

Datové typy

Do databáze pochopitelně nemůžete uložit instance Tkinteru apod. V následující tabulce naleznete seznam všech povolených datových typů + jak je reprezentuje SQLite?.

V následující tabulce naleznete, jak se datové typy SQLite? reprezentují v Pythonu.

text_factory

V předchozí tabulce jsem se zmínil o text_factory. Pomocí tohoto parametru lze určit, jaký datový typ bude vrácen po SELECTu? na SQLite? typ TEXT.

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
text = u"ěščřžýáíé"
print u"Původní typ: ", type(text)
vystup = db.execute("select ?", (text, ))
print u"Vrácený typ: ", type(vystup.fetchone()[0])
db.commit()
db.close()

Standardně se jedná o unicode, ale je možné, že budete chtít výstup změnit například na str:

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
db.text_factory = str
text = u"ěščřžýáíé"
print u"Původní typ: ", type(text)
vystup = db.execute("select ?", (text, ))
print u"Vrácený typ: ", type(vystup.fetchone()[0])
db.commit()
db.close()

Parametru text_factory můžeme pochopitelně přiřadit i jinou hodnotu než str, unicode. Může to být například vámi definovaná funkce:

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
db.text_factory = lambda x: unicode(x, "utf-8", "ignore")
text = u"\xe4\xf6\xfc".encode("latin1")
print u"Původní typ: ", type(text)
vystup = db.execute("select ?", (text, ))
print u"Vrácený typ: ", type(vystup.fetchone()[0])
db.commit()
db.close()

Pokud bychom nepoužili lambda x: unicode(x, "utf-8", "ignore"), ale pouze unicode, dostali bychom chybu:

Traceback (most recent call last):
  File "C:\Documents and Settings\Blujacker\Plocha\a.py", line 6, in <module>
    vystup = db.execute("select ?", (text, ))
OperationalError: Could not decode to UTF-8 column '?' with text 'äöü'

Tím, že jsme jako třetí parametr funkce unicode nastavili "ignore", jsme pythonu řekli, aby znaky, které se mu nepodařilo dekódovat ignoroval.

Tento parametr může nabývat jedné speciální hodnoty: OptimizedUnicode. Pokud je text_factory rovna právě této hodnotě, tak python vrátí unicode pouze v případě, že se jedná o non-ASCI data. V ostatních případech vrátí str:

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
from sqlite3 import OptimizedUnicode
db = sqlite.connect("db")
db.text_factory = OptimizedUnicode
text = u"str"
print u"Původní typ: ", type(text)
vystup = db.execute("select ?", (text, ))
print u"Vrácený typ: ", type(vystup.fetchone()[0])
db.commit()
db.close()

V předchozí ukázce nebylo třeba, aby vrácená data byla unicode.

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
from sqlite3 import OptimizedUnicode
db = sqlite.connect("db")
db.text_factory = OptimizedUnicode
text = u"ěšč"
print u"Původní typ: ", type(text)
vystup = db.execute("select ?", (text, ))
print u"Vrácený typ: ", type(vystup.fetchone()[0])
db.commit()
db.close()

Datový typ výstupu je unicode.

create_function

Modul pysqlite podporuje vytváření vlastních funkcích, které pak můžete použít v SQL příkazu.

Funkce se vytvářejí pomocí metody create_function(jmeno, pocet_parametru, funkce). Pomocí parametru jmeno můžete později zavolat danou funkci z SQL příkazu. Proměnná pocet_parametru určuje, kolik parametru přijímá daná funkce.

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
import md5
db = sqlite.connect("db")
def md5sum(t):
    return md5.md5(t).hexdigest()
db.create_function("md5", 1, md5sum)
db.execute("create table hesla(heslo)")
db.execute("insert into hesla values(md5(?))", ("foo", ))
vystup = db.execute("select * from hesla")
print vystup.fetchone()[0]
db.commit()
db.close()

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
class Faktorial:
    def __init__(self):
        self.cislo = 1

    def step(self, hodnota):
        self.cislo = self.cislo * hodnota

    def finalize(self):
        return self.cislo

db.create_aggregate("faktorial", 1, Faktorial)
db.execute("create table cisla(cislo)")
db.executescript('''
insert into cisla values(1);
insert into cisla values(2);
insert into cisla values(3);
insert into cisla values(4);
insert into cisla values(5);
''')
vystup = db.execute("select faktorial(cislo) from cisla")
print vystup.fetchone()[0]
db.commit()
db.close()

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
def obrazene_razeni(parametr1, parametr2):
    return -cmp(parametr1, parametr2)
    
db.create_collation("obracene", obrazene_razeni)

db.execute("create table pismena(pismeno)")
db.executescript('''
insert into pismena values('a');
insert into pismena values('b');
insert into pismena values('c');
''')
vystup = db.execute("select pismeno from pismena order by pismeno collate obracene")
for pismeno in vystup:
    print pismeno
db.commit()
db.close()

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
def authorizer_callback(action, arg1, arg2, dbname, source):
    if action == sqlite.SQLITE_DELETE and arg1 == "sql_master":
        print u"Nemáte právo mazat tabulky!"
        return sqlite.SQLITE_DENY
    elif action == sqlite.SQLITE_DELETE:
        print u"Nemáte právo mazat položky z tabulky!"
        return sqlite.SQLITE_DENY
    elif action == sqlite.SQLITE_INSERT and arg1 == "soukroma_tabulka":
        print u"Nemáte právo zapisovat do tabulky 'soukroma_tabulka'"
        return sqlite.SQLITE_IGNORE
    return sqlite.SQLITE_OK
    
db.set_authorizer(authorizer_callback)
db.execute("create table tabulka(polozka)")
db.execute("create table soukroma_tabulka(polozka)")
db.execute("insert into tabulka values('zkouska')")
db.execute("insert into soukroma_tabulka values('zkouska')")#Příkaz se neprovede, ale program běží dál
db.execute("delete from tabulka where polozka = 'zkouska'")#Příkaz se neprovede, ale program běží dál
db.execute("drop table tabulka")
print "Sem se program nikdy nedostane..."
db.commit()
db.close()

class Bod:
    def __init__(self, x, y):
        self.x, self.y = x, y

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite
db = sqlite.connect("db")
class Bod:
    def __init__(self, x, y):
        self.x, self.y = x, y
    def __conform__(self, protocol):
        if protocol == sqlite.PrepareProtocol:
            return "%f;%f" % (self.x, self.y)
bod = Bod(4.0, -3.2)
vysledek = db.execute("select ?", (bod,))
print vysledek.fetchone()[0]        
db.commit()
db.close()

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite

class Bod(object):
    def __init__(self, x, y):
        self.x, self.y = x, y

def uprav_bod(bod):
    return "%f;%f" % (bod.x, bod.y)
sqlite.register_adapter(Bod, uprav_bod)

db = sqlite.connect("db")
bod = Bod(4.0, -3.2)
vysledek = db.execute("select ?", (bod,))
print vysledek.fetchone()[0]        
db.commit()
db.close()

#-*- coding: utf-8 -*-
from sqlite3 import dbapi2 as sqlite

class Bod(object):
    def __init__(self, x, y):
        self.x, self.y = x, y
    def __repr__(self):
        return u"Bod(%f;%f)" % (self.x, self.y)
def uprav_bod(bod):
    return "%f;%f" % (bod.x, bod.y)
def premen_bod(s):
    x, y = map(float, s.split(";"))
    return Bod(x, y)
sqlite.register_adapter(Bod, uprav_bod)
sqlite.register_converter("bod", premen_bod)

db = sqlite.connect("db", detect_types=sqlite.PARSE_DECLTYPES)
bod = Bod(4.0, -3.2)
print bod
db.execute('create table test(bod bod)')
db.execute("insert into test values(?)", (bod, ))
vysledek = db.execute("select bod from test")
print vysledek.fetchone()[0]
db.commit()
db.close()