Pysqlite

V tomto článku se seznámíme s knihovnou pro práci s databázemi – 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 verze 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ůsoby, 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'Vojxe1u010dek'), (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'Vojxe1u010dek')
(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 mohly 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"xe4xf6xfc".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 SettingsBlujackerPlochaa.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 parametrů 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()

Výstup je acbd18db4cc2f85cedef654fccc4a4d8.

create_aggregate

Další funkcí pysqlite je vytvoření vlastního agregátu. Děje se tak pomocí metody create_aggregate(jmeno, pocet_parametru, trida). Daná trida musí mít definovanou metodu step, která bude přijímat pocet_parametru parametrů. Dále musí být také definována metoda finalize, která vrátí konečný výsledek.

Jako příklad nám může posloužit například výpočet faktoriálu.

#-*- 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()

Faktoriál čísla 5 je 120.

create_collation

Pysqlite umožňuje vytváření vlastních řadících mechanismů. Děje se to pomocí metody create_collation(jmeno, funkce). Daná funkce musí přijímat dva parametry a měla by vrátit -1, pokud je první parametr řazen níže než druhý, 0 pokud jsou parametry na stejné úrovni a 1 pokud je první řazen výše než druhý.

#-*- 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()

Vytvořili jsme „obrácené“ řazení.

Nastavování práv

Pysqlite dovoluje nastavit, jaké operace jsou dovoleny a jaké zakázány. Slouží k tomu funkce set_authorizer(funkce). Daná funkce musí přijímat 5 parametrů: action, arg1, arg2, dbname, source.

První parametr action určuje, o jaký typ operace se jedná. V následující tabulce naleznete ty nejdůležitější akce + hodnoty parametrů arg1, arg2:

Funkce musí vrátit, zda má uživatel právo provést daný příkaz. Pokud má, návratová hodnota je sqlite.SQLITE_OK, pokud nemá, tak je návratová hodnota rovna sqlite.SQLITE_DENY. Pokud bude vrácena hodnota sqlite.SQLITE_DENY, bude vyvolána výjimka sqlite3.DatabaseError: not authorized a pokud nebude ošetřena, program spadne. Toto se dá obejít pomocí návratové hodnoty sqlite.SQLITE_IGNORE.

#-*- 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()

Ukládání jiných datových typů

Do databáze můžete, pokud se budete trochu snažit, uložit i jiné datové typy, než které jsem uváděl v předešlé tabulce. Správně řečeno, my tam nemůžeme uložit jiné datové typy, ale můžeme naučit pysqlite jak daný objekt převést na některý z podporovaných datových typů. Mohli bychom chtít například uložit následující třídu:

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

Abychom tuto třídu mohli uložit, musíme jí přidat metodu __conform__, která vrátí převedenou hodnotu třídy. V našem případě by to tedy mohlo být například "%f;%f" % (self.x, self.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()

Je zde ještě jedno možné řešení. Řešení, v němž bychom nemuseli vytvářet metodu __conform__. My můžeme zaregistrovat adaptér pomocí metody register_adapter(objekt, funkce):

#-*- 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()

Pozor, objekt (tedy první parametr metody register_adapter) musí vždy dědit z object.

Přeměna SQLite datových typů

V jedné z předchozích tabulek bylo jasně vidět, že SQLite vrací výsledky v následujících datových typech: None, int, long, float, buffer a unicode. My můžeme pysqlite naučit, jak vrátit i jiný datový typ. V předchozím příkladě jsme ukládali třídu Bod do databáze jako řetězec a jako řetězec jsme ji také četli. My ale můžeme pomocí metody register_converter(jmeno, funkce) donutit pysqlite aby vrátila jeho správnou reprezentaci (tedy instanci třídy Bod).

#-*- 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()

Všimněte si, že když se otevírá spojení s databází, tak je přítomen navíc jeden parametr, se kterým jsme se zatím nesetkali. Tento parametr zajišťuje, aby fungovalo převádění proměnných.

Další informace

Další informace o této knihovně naleznete v dokumentaci pysqlite a oficiální dokumentaci pythona.