Python Sqlite3-tietokantakirjasto

Luodaan ensin yhteysolio, joka edustaa tietokantaa. Tässä esimerkissä tietokantojen taulut tallennetaan tiedostoon: 

conn = sqlite3.connect('/tmp/example.sqlite3')

Kun olet luonut yhteysolion, voit luoda sille kursoriolion ja kutsua sen execute-metodia suorittaaksesi SQL-komentoja: 

c = conn.cursor()
# Luo taulu
c.execute('''create table stocks
(date text, trans text, symbol text,
 qty real, price real)''')
# Lisataan uusi rivi tauluun
c.execute("""insert into stocks
      values ('2006-01-05','BUY','RHAT',100,35.14)""")
# Tallenna tietokanta ja sulje kursori
conn.commit()
c.close()

Muuttujat välitetään Pythonista SQL-operaatioille parametrien avulla, yleensä vektorimuodossa execute-metodille. Kysymysmerkki toimii tietokantakyselyn paikkamerkkinä.

Esimerkki. 

symbol = 'IBM'
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)

Laajempi esimerkki. 

for t in (('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
  ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
  ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
):
c.execute('insert into stocks values (?,?,?,?,?)', t)

Tietojen lukemiseksi SELECT-lausekkeelta voit joko käydä lävitse kursoria iteraattorina, kutsua kursorin fetchone-metodia lukeaksesi yhden rivin kerrallaan, tai kutsua fetchall-metodia lukeaksesi listan osumista.

Tämä esimerkki käyttää iteraattoria: 

>>> c = conn.cursor()
>>> c.execute('select * from stocks order by price')
>>> for row in c:
...    print row
...
(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)
(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
(u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
(u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0)
>>>

Kirjaston funktiot ja attribuutit

Funktio sqlite3.connect(database[,timeout, isolation_level, detect_types, factory])

Vakio sqlite3.PARSE_DECLTYPES

Vakio sqlite3.PARSE_COLNAMES

Muuntimen rekisteröinti

Funktio sqlite3.register_converter(typename, callable)

Adapterin rekisteröinti

Funktio sqlite3.register_adapter(type, callable)

int

long

float

str (utf8-koodattuna)

unicode

buffer

Funktio sqlite3. complete_statement(sql)

Funktio sqlite3.enable_callback_tracebacks(flag)

Yhteysoliot

Yhteysolioilla on seuraavat attribuutit ja metodit:

Attribuutti Connection. isolation_level

Funktio Connection.cursor([cursorClass])

Funktio Connection.commit()

Funktio Connection.rollback()

Funktio Connection.close()

Funktiot Connection.execute, Connection.executemany ja Connection.executescript

Oman SQL-funktion luominen

Funktio Connection.create_function(name, num_params, func)

Oman aggregaattifunktion luominen

Funktio Connection.create_aggregate(name, num_params, aggregate_class)

Oman lajittelualgoritmin luominen

Funktio Connection.create_collation(name, callable)

-1

jos ensimmäinen on järjestyksessä toisen jälkeen,

Funktio Connection.interrupt()

Funktio Connection.set_authorizer(authorizer_callback)

Funktio Connection.set_progress_handler(handler, n)

Lisäosien lataaminen

Funktio Connection.enable_load_extension(enabled)

Funktio Connection.load_extension(path)

Rivin luontiolio

Attribuutti Connection.row_factory

Attribuutti Connection.text_factory

Attribuutti Connection.total_changes

Kursorioliot

Kursoriolion ilmentymillä on seuraavat attribuutit ja metodit:

Funktio Cursor.execute(sql[,parameters])

Funktio Cursor.executemany(sql, seq_of_parameters)

Funktio Cursor.executescript(sql_script)

Funktio Cursor.fetchone()

Funktio Cursor.fetchmany([size=cursor.arraysize])

Funktio Cursor.fetchall()

Funktio Cursor.rowcount

Attribuutti Cursor.lastrowid

Pythonin ja Sqliten tyyppien käsittelystä

Sqlite3 tukee oletuksena seuraavia tietotyyppejä:

Seuraavat Pythonin tyypit voi siten lähettää Sqlitelle ilman ongelmia:

Seuraavassa on esitettynä miten sqlite3-tyypit oletusarvoisesti muutetaan Pythonin tyypeiksi:

Sqlite3-kirjaston tyyppijärjestelmä on laajennettavissa kahdella tapaa: Toisaalta voit tallentaa muita Python-tyyppejä sqlite3-tietokantaan käyttämällä olioiden adaptointia, ja toisaalta voit antaa sqlite3-kirjaston muuntaa sqlite3-tyyppejä Python-tyypeiksi käyttäen muuntimia.

Adapterien käyttö muiden Python-tyyppien tallentamiseksi sqlite3-tietokantaan

Kuten aikaisemmin kuvattiin, sqlite3 tukee oletusarvoisesti ainoastaan rajoitettua määrää eri tyyppejä.

Käyttääksesi muita Python-tyyppejä sqlite3-tietokannoissa, sinun tulee adaptoida ne joksikin sqlite3-kirjaston tukemaksi tyypiksi, eli yhdeksi seuraavista:

Olioiden adaptointiin käytettävä protokolla on nimeltään PrepareProtocol.

On kaksi tapaa saada sqlite3-kirjasto adaptoimaan oma Python-tyyppi joksikin tuetuista tyypeistä.

Ensimmäinen tapa: Anna oliosi adaptoida itsensä

Tämä on hyvä keino kun kirjoitat luokkasi itse. Oletetaan esimerkiksi, että luokkasi näyttää tältä: 

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

Päätät nyt tallentaa Point-olion yhtenä Sqliten sarakkeena. Ensin sinun on valittava yksi tuetuista tietotyypeistä esittämään luokkaa Point.

Käytämme tässä merkkijonotyyppiä str ja erotamme koordinaatit toisistaan puolipisteellä. Tämän jälkeen meidän täytyy luoda luokalle metodi conform parametreilla self ja protocol, jonka tehtävä on palauttaa muunnettu arvo. Protokolla-parametri saa arvon PrepareProtocol. 

import sqlite3
class Point(object):
  def __init__(self, x, y):
    self.x, self.y = x, y
  def __conform__(self, protocol):
    if protocol is sqlite3.PrepareProtocol:
      return "%f;%f" % (self.x, self.y)
con = sqlite3.connect(":memory:")
cur = con.cursor()
p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print cur.fetchone()[0]

Toinen tapa: Kutsufunktion rekisteröinti adapteriksi

Toinen tapa saada sqlite3-kirjasto adaptoimaan oma Python-tyyppi joksikin tuetuista tyypeistä, on luoda funktio, joka muuntaa tyypin merkkijonoesitykseksi. Kyseinen funktio rekisteröidään metodilla register_adapter.

Adaptoitavan tyypin tai luokan tulee periytyä jotain reittiä object-luokasta. 

import sqlite3
class Point(object):
  def __init__(self, x, y):
    self.x, self.y = x, y
def adapt_point(point):
  return "%f;%f" % (point.x, point.y)
sqlite3.register_adapter(Point, adapt_point)
con = sqlite3.connect(":memory:")
cur = con.cursor()
p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print cur.fetchone()[0]

Sqlite3-kirjastolla on kaksi oletusadapteria Pythonin vakiokirjaston olioiden datetime.date ja datetime.datetime tyyppejä varten. Oletetaan nyt, että haluamme tallentaa datetime.datetime olion, ei tällä kertaa ISO-standardin esityksenä, vaan Unixin aikaleimana. 

import sqlite3import datetime, time
def adapt_datetime(ts):
  return time.mktime(ts.timetuple())
sqlite3.register_adapter(datetime.datetime, adapt_datetime)
con = sqlite3.connect(":memory:")
cur = con.cursor()
now = datetime.datetime.now()
cur.execute("select ?", (now,))
print cur.fetchone()[0]

Sqlite3-muuttujien muuntaminen omiksi Python-tyypeiksi

Adapterin luominen mahdollistaa omien Python-tyyppien lähettämisen Sqlitelle. Kuitenkin ollakseen käyttökelpoinen, menetelmä vaatii myös paluureitin Pythonista Sqliten kautta takaisin Pythonille.

Tällaiseen tehtävään käytetään muuntimia.

Palataan takaisin Point-luokan pariin. Tallensimme (x,y)-koordinaatit erotettuina puolipisteellä merkkijonoksi sqlite3-tietokantaan.

Määritellään ensin muunninfunktio, joka saa parametrinaan merkkijonon ja rakentaa siitä Point-luokan olion.

Huomaa, että muunninfunktioita kutsutaan aina merkkijonon avulla, olipa Sqlitelle lähetetty muuttuja minkä tietotyypin alaisuudessa hyvänsä. 

def convert_point(s):
  x, y = map(float, s.split(";"))
  return Point(x, y)

Nyt on saatava sqlite3-kirjasto ymmärtämään, että valintamme tietokannasta onkin itse asiassa tyyppiä Point koordinaatteineen. Tämän toteuttamiseen on kaksi tapaa:

Molemmat tavat on kuvattuna kappaleessa Kirjaston funktiot ja attribuutit, vakioiden PARSE_DECLTYPES ja PARSE_COLNAMES määrittelyn yhteydessä.

Seuraava esimerkki valottaa molempia toteutustapoja: 

import sqlite3
class Point(object):
  def __init__(self, x, y):
    self.x, self.y = x, y
  def __repr__(self):
    return "(%f;%f)" % (self.x, self.y)
def adapt_point(point):
  return "%f;%f" % (point.x, point.y)
def convert_point(s):
  x, y = map(float, s.split(";"))
  return Point(x, y)
# Rekisteroi adapteri
sqlite3.register_adapter(Point, adapt_point)
# Registeroi muunnin
sqlite3.register_converter("point", convert_point)
p = Point(4.0, -3.2)
###################################
# 1) Maariteltyja tyyppeja kayttaen
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test(p point)")
cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
print "with declared types:", cur.fetchone()[0]
cur.close()
con.close()
#############################
# 2) Sarakkeen nimia kayttaen
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(p)")
cur.execute("insert into test(p) values (?)", (p,))
cur.execute('select p as "p [point]" from test')
print "with column names:", cur.fetchone()[0]
cur.close()
con.close()

Oletusadapterit ja -muuntimet

Datetime-kirjaston tyypeille date ja datetime on olemassa oletusadapterit. Ne näkyvät ISO-standardin mukaisina päivämäärä- ja aikaleimoina sqlite3-tietokannalle.

Oletusarvoiset muuntimet rekisteröidään nimellä date olioille datetide.date ja nimellä timestamp olioille datetime.datetime.

Tällä tavoin voit käyttää päivämäärä- ja aikaleimoja Pythonista ongelmitta monissa tapauksissa. Adapterien muoto on yhteensopiva Sqliten päivä- ja aikafunktioiden kanssa. Seuraavassa esimerkki tästä: 

import sqlite3
import datetime
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COCOLNAMES)
cur = con.cursor()
cur.execute("create table test(d date, ts timestamp)")
today = datetime.date.today()
now = datetime.datetime.now()
cur.execute("insert into test(d, ts) values (?, ?)", (today, now))
cur.execute("select d, ts from test")
row = cur.fetchone()
print today, "=>", row[0], type(row[0])
print now, "=>", row[1], type(row[1])
cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')
row = cur.fetchone()
print "Paivamaara nyt on", row[0], type(row[0])
print "Aikaleima nyt on", row[1], type(row[1])

Tiedonsiirtojen hallinta

Oletuksena sqlite3-kirjasto avaa yhteyden itseisarvoisesti ennen tiedonmäärityskielen lauseita, kuten INSERT, UPDATE, DELETE, REPLACE, ja toteuttaa muutokset itseisarvoisesti ennen muita kuin tiedonmäärityskielen lauseita ja hakuun liittymättömiä lauseita, eli mitä tahansa muita kuin SELECT, INSERT, UPDATE, DELETE, REPLACE.

Joten jos olet käsittelemässä avattua yhteyttä, ja suoritat komennon kuten CREATE TABLE, VACUUM, PRAGMA, sqlite3-kirjasto toteuttaa muutoksesi itseisarvoisesti ennenkuin suorittaa antamasi komennon. Tähän on kaksi syytä. Ensimmäinen on, että osa näistä komennoista ei toimi yhteyden aikana. Toinen syy on, että Sqliten on pidettävä kirjaa yhteyden tilasta, eli onko yhteys aktiivinen vaiko ei.

Voit hallita minkä tyyppisen BEGIN-lauseen Pysqlite oletusarvoisesti suorittaa, tai suorittaako mitään, käyttämällä eristystaso-parametria yhteyden connect-kutsulle, tai yhteyden eristystaso-ominaisuutta.

Jos haluat käyttöön automaattisesti suoritettavat toimenpiteet, käytä eristystasolle isolation_level arvoa None. Muussa tapauksessa jätä valinta oletusarvoonsa, jolloin tuloksena on puhdas BEGIN-lause, tai aseta se joksikin Sqliten tukemaksi eristystason arvoksi seuraavista: DEFERRED, IMMEDIATE tai EXCLUSIVE.

Lisätietoa: Sarakkeiden käsittely niiden nimien avulla järjestysnumeron käytön sijasta

Yksi sqlite3-kirjaston käyttökelpoinen ominaisuus on row-luokka, joka toimii rivin luontioliona.

Ne rivit, joita käytetään hyväksi tämän luokan avulla ovat kaikki saavutettavissa niin järjestysnumeron avulla kuten vektorit samoin kuin ei-merkkikokoherkkien nimien avulla. 

import sqlite3
con = sqlite3.connect("mydb")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("select name_last, age from people")
for row in cur:
  # Totea, etta...
  assert row[0] == row["name_last"]
  assert row["name_last"] == row["nAmE_lAsT"]
  assert row[1] == row["age"]
  assert row[1] == row["AgE"]

Toimenpiteen automaattinen loppuunsaattaminen

Toimenpide voidaan määrittää automaattisesti loppuunsaatettavaksi. Poikkeuksen sattuessa toimenpide perutaan, muutoin se suoritetaan. 

from __future__ import with_statement
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("create table person (id integer primary key, firstname varchar unique)")
# Toimenpide onnistui, con.commit suoritetaan automaattisesti taman jalkeen
with con:
  con.execute("insert into person(firstname) values (?)", ("Joe",))
# Con.rollback-metodia kutsutaan kun with-osion suoritus paattyy poikkeukseen.
# Poikkeus nostetaan silti, ja se taytyy kasitella.
try:
  with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
  print "Ei voitu lisata Joea kahdesti"

pysqlite3 (last edited 2011-09-20 05:28:38 by localhost)