Il modulo sqlite3 fornisce una interfaccia compatibile DB-API 2.0 al database relazionale SQLite . SQLite è un database in-process concepito per essere incorporato in applicazioni, invece di usare un programma server di database separato tipo MySQL, PostgreSQL oppure Oracle. SQLite è veloce, rigorosamente testato, flessibile, rendendolo candidato per la prototipizzazione e lo sviluppo di produzione per certe applicazioni.

Creare un Database

Un database SQLite viene conservato in un singolo file nel filesystem. La libreria gestisce l'accesso al file, incluso il bloccaggio per prevenire corruzione di dati quando viene usato in scrittura da utenti multipli. Il database viene creato la prima volta che si accede al file, ma l'applicazione è responsabile per la gestione delle definizioni della tabella, note come schema . all'interno del database.

In questo esempio si cerca il file database prima di aprirlo con connect() in modo da sapere quando creare lo schema per i nuovi database.

import os
import sqlite3

db_filename = 'todo.db'

db_is_new = not os.path.exists(db_filename)

conn = sqlite3.connect(db_filename)

if db_is_new:
    print 'Occorre creare lo schema'
else:
    print 'Il database esiste, si suppone che esista anche lo schema.'

conn.close()

Quando si esegue lo script per due volte si dimostra che esso crea il file vuoto, se non esiste.

$ ls *.db
ls: impossibile accedere a *.db: File o directory non esistente

$ python sqlite3_createdb.py
Occorre creare lo schema

$ ls *.db
todo.db

$ python sqlite3_createdb.py
Il database esiste, si suppone che esista anche lo schema.

Il passo successivo dopo la creazione del nuovo file di database, è quello di creare lo schema per definire le tabelle all'interno del database. Gli esempi che seguono in questa sezione usano tutti lo stesso schema di database con delle tabelle per la gestione di compiti.
Le tabelle sono:

progetto

compito

Le istruzioni in data definition language ( DDL ) per creare le tabelle sono:

-- Schema per gli esempi dell'applicazione to-do.

-- I progetti sono le attivita' di alto livello composte da compiti
create table progetto (
    nome        text primary key,
    descrizione text,
    scadenza    date
);

-- I compiti sono le attivita' che possono essere svolte per completare un progetto

create table compito (
    id            integer primary key autoincrement not null,
    priorita      integer default 1,
    dettagli      text,
    stato         text,
    scadenza      date,
    completato_il date,
    progetto      text not null references project(name)
);

Il metodo executescript() della connessione può essere usato per eseguire le istruzioni DDL per creare lo schema.

import os
import sqlite3

db_filename = 'todo.db'
schema_filename = 'todo_schema.sql'

db_is_new = not os.path.exists(db_filename)

with sqlite3.connect(db_filename) as conn:
    if db_is_new:
        print 'Creazione dello schema'
        with open(schema_filename, 'rt') as f:
            schema = f.read()
        conn.executescript(schema)

        print 'Inserimento dei dati di partenza'

        conn.execute("""
        insert into progetto (nome, descrizione, scadenza)
        values ('pymotw-it', 'Il modulo Python della Settimana', '2010-11-01')
        """)

        conn.execute("""
        insert into compito (dettagli, stato, scadenza, progetto)
        values ('descrivere select', 'fatto', '2010-10-03', 'pymotw-it')
        """)

        conn.execute("""
        insert into compito (dettagli, stato, scadenza, progetto)
        values ('descrivere random', 'in attesa', '2010-10-10', 'pymotw-it')
        """)

        conn.execute("""
        insert into compito (dettagli, stato, scadenza, progetto)
        values ('descrivere sqlite3', 'attivo', '2010-10-17', 'pymotw-it')
        """)
    else:
        print 'Il database esiste, si suppone che esista anche lo schema.'

Dopo che sono state create le tabelle, alcune istruzioni insert creano un progetto di esempio ed i compiti relativi. Il programma da riga di comando sqlite3 può essere usato per esaminare il contenuto del database.

$ python sqlite3_create_schema.py

Creazione dello schema
Inserimento dei dati di partenza

$ sqlite3 todo.db 'select * from compito'

1|1|descrivere select|fatto|2010-10-03||pymotw-it
2|1|descrivere random|in attesa|2010-10-10||pymotw-it
3|1|descrivere sqlite3|attivo|2010-10-17||pymotw-it

Recuperare i Dati

Per recuperare i valori salvati nella tabella compito all'interno di un programma Python, si crea un cursore Cursor da una connessione del database usando il metodo cursor() . Un cursore fornisce una vista consistente dei dati, e costituisce il mezzo principale per interagire con un sistema di database transazionale come SQLite.

import sqlite3

db_filename = 'todo.db'

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()

    cursor.execute("""
    select id, priorita, dettagli, stato, scadenza from compito where progetto = 'pymotw-it'
    """)

    for row in cursor.fetchall():
        task_id, priority, details, status, deadline = row
        print '%2d {%d} %-20s [%-8s] (%s)' % (task_id, priority, details, status, deadline)

L'interrogazione si svolge in due passi. Per prima cosa si esegue l'interrogazione con il metodo del cursore execute() per informare il motore del database di quali dati deve raccogliere. Quindi si usa fetchall() per recuperare i risultati. Il valore restituito è una sequenza di tuple che contengono i valori per le colonne indicate nella clausola select dell'interrogazione.

$ python sqlite3_select_tasks.py

 1 {1} descrivere select    [fatto    ] (2010-10-03)
 2 {1} descrivere random    [in attesa] (2010-10-10)
 3 {1} descrivere sqlite3   [attivo   ] (2010-10-17)

I risultati possono essere recuperati uno alla volta con fetchone() , oppure in blocchi di dimensione finita con fetchmany() .

import sqlite3

db_filename = 'todo.db'

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()

    cursor.execute("""
    select nome, descrizione, scadenza from progetto where nome = 'pymotw-it'
    """)
    name, description, deadline = cursor.fetchone()

    print 'Dettagli del progetto per %s (%s) scadenza %s' % (description, name, deadline)

    cursor.execute("""
    select id, priorita, dettagli, stato, scadenza from compito
    where progetto = 'pymotw-it' order by scadenza
    """)

    print '\nProssimi 5 compiti:'

    for row in cursor.fetchmany(5):
        task_id, priority, details, status, deadline = row
        print '%2d {%d} %-25s [%-8s] (%s)' % (task_id, priority, details, status, deadline)

Il valore passato a fetchmany() è il numero massimo di elementi da restituire. Se gli elementi disponibili sono meno, la sequenza restituita sarà più piccola rispetto al valore massimo passato.

$ python sqlite3_select_variations.py

Dettagli del progetto per Il modulo Python della Settimana (pymotw-it) scadenza 2010-11-01

Prossimi 5 compiti:
 1 {1} descrivere select         [fatto    ] (2010-10-03)
 2 {1} descrivere random         [in attesa] (2010-10-10)
 3 {1} descrivere sqlite3        [attivo   ] (2010-10-17)

Interrogare i Metadati

Le specifiche DB-API 2.0 dicono che dopo la chiamata di execute() , il cursore Cursor dovrebbe impostare l'attributo description in modo che contenga delle informazioni circa i dati che verranno restituiti dai metodi di recupero. Le specifiche API dicono che il valore di description deve essere una sequenza di tuple che contengono il nome della colonna, il tipo, la dimensione di visualizzazione, la dimensione interna, la precisione, la scala ed un flag che dice se i valori null debbano essere accettati.

import sqlite3

db_filename = 'todo.db'

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()

    cursor.execute("""
    select * from compito where progetto = 'pymotw-it'
    """)

    print 'La tabella Compito ha queste colonne:'
    for colinfo in cursor.description:
        print colinfo

Visto che sqlite3 non impone costrizioni di tipo o dimensione sui dati inseriti nel database, viene inserito solo il valore del nome della colonna.

$ python sqlite3_cursor_description.py

La tabella Compito ha queste colonne:
('id', None, None, None, None, None, None)
('priorita', None, None, None, None, None, None)
('dettagli', None, None, None, None, None, None)
('stato', None, None, None, None, None, None)
('scadenza', None, None, None, None, None, None)
('completato_il', None, None, None, None, None, None)
('progetto', None, None, None, None, None, None)

Oggetti Riga

Nella modalità predefinita, i valori restituiti dai metodi di recupero sotto forma di "righe" dal database sono tuple. Il chiamante è responsabile della conoscenza dell'ordine delle colonne nell'interrogazione e dell'estrazione dei valori individuali dalla tupla. Quando il numero di valori in una interrogazione cresce, oppure il codice che lavora con i dati è sparso in una libreria, in genere è più facile lavorare con un oggetto ed accedere i valori di colonna usando il nome delle colonne stesse, visto che in questo modo il numero e l'ordine degli elementi della tupla possono cambiare nel corso del tempo mano a mano che l'interrogazione viene modificata, ed il codice che dipende dai risultati dell'interrogazione è meno prono agli errori.

Gli oggetti Connection hanno una proprietà row_factory che consente al codice chiamante di controllare il tipo di oggetto creato per rappresentare ogni riga nel risultato dell'interrogazione impostata. sqlite3 comprende anche una classe Row che è intesa per l'uso come row factory. Le istanze di Row possono essere indirizzate tramite l'indice di colonna ed il nome.

import sqlite3

db_filename = 'todo.db'

with sqlite3.connect(db_filename) as conn:
    # Modifica row factory per usare Row
    conn.row_factory = sqlite3.Row

    cursor = conn.cursor()

    cursor.execute("""
    select nome, descrizione, scadenza from progetto where nome = 'pymotw-it'
    """)
    name, description, deadline = cursor.fetchone()

    print 'Dettagli del progetto %s (%s) scadenza %s' % (description, name, deadline)

    cursor.execute("""
    select id, priorita, stato, scadenza, dettagli from compito
    where progetto = 'pymotw-it' order by scadenza
    """)

    print '\nProssimi 5 compiti:'

    for row in cursor.fetchmany(5):
        print '%2d {%d} %-25s [%-8s] (%s)' % (
            row['id'], row['priorita'], row['dettagli'], row['stato'], row['scadenza'],
            )

Questa versione dell'esempio sqlite3_select_variations.py è stato riscritto usando istanze di Row in luogo delle tuple. La riga progetto viene comunque stampata indirizzando i valori di colonna in base alla posizione, ma l'istruzione print per i compiti usa invece la ricerca tramite parola chiave, in modo che non importi se l'ordine delle colonne nell'interrogazione è stato cambiato.

$ python sqlite3_row_factory.py

Dettagli del progetto Il modulo Python della Settimana (pymotw-it) scadenza 2010-11-01

Prossimi 5 compiti:
 1 {1} descrivere select         [fatto    ] (2010-10-03)
 2 {1} descrivere random         [in attesa] (2010-10-10)
 3 {1} descrivere sqlite3        [attivo   ] (2010-10-17)

Usare Variabili con le Interrogazioni

L'uso di interrogazioni definite come stringhe letterali incorporate nel programma non consente mutamenti. Ad esempio, quando viene aggiunto un altro progetto al database l'interrogazione per mostrare i primi cinque compiti dovrebbe essere aggiornata in modo che possa funzionare per entrambi i progetti. Un modo per aggiungere maggiore flessibilità è di costruire l'istruzione SQL con l'interrogazione desiderata combinando i valori in Python. Comunque, costruire in questo modo una stringa di interrogazione è pericoloso, e dovrebbe essere evitato. Sbagliare la costruzione di una sequenza di escape per i caratteri speciali nelle parti variabili dell'interrogazione può risultare in un errore di analisi in SQL, o peggio in una classe di vulnerabilità alla sicurezza nota come attacchi SQL-injection .

Il modo giusto per usare valori dinamici con le interrogazioni è tramite variabili ospiti passate ad execute() assieme all'istruzione SQL. Un valore segnaposto in SQL viene sostiuito dal valore nella variabile ospite quando viene eseguita l'istruzione. L'uso di variabili ospite al posto dell'inserimento di valori arbitrari nell'istruzione SQL prima che sia analizzata evita gli attacchi SQL-injection perchè non esiste possibilità che valori non affidabili possano influenzare il modo in cui l'istruzione SQL viene analizzata. SQLite supporta due forme di interrogazioni con segnaposto, posizionali e nominali.

Parametri Posizionali

Un punto interrogativo (?) denota un parametro posizionale, passato ad execute() come membro di una tupla.

import sqlite3
import sys

db_filename = 'todo.db'
project_name = sys.argv[1]

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()

    query = "select id, priorita, dettagli, stato, scadenza from compito where progetto = ?"

    cursor.execute(query, (project_name,))

    for row in cursor.fetchall():
        task_id, priority, details, status, deadline = row
        print '%2d {%d} %-20s [%-8s] (%s)' % (task_id, priority, details, status, deadline)

Il parametro da riga di comando viene passato in sicurezza all'interrogazione come parametro posizionale, quindi non vi è possibilità che dati malformati possano intaccare il database.

$ python sqlite3_argument_positional.py pymotw-it

 1 {1} descrivere select    [fatto    ] (2010-10-03)
 2 {1} descrivere random    [in attesa] (2010-10-10)
 3 {1} descrivere sqlite3   [attivo   ] (2010-10-17)

Parametri Nominali

Si usano i parametri nominali quando l'interrogazione è più complessa, ed è composta da molti parametri, oppure dove alcuni parametri sono ripetuti diverse volte all'interno dell'interrogazione. I parametri nominali sono prefissati dal simbolo dei due punti (:), tipo :nome_parametro .

import sqlite3
import sys

db_filename = 'todo.db'
project_name = sys.argv[1]

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()

    query = """select id, priorita, dettagli, stato, scadenza from compito
            where progetto = :nome_progetto
            order by scadenza, priorita
            """

    cursor.execute(query, {'nome_progetto':project_name})

    for row in cursor.fetchall():
        task_id, priority, details, status, deadline = row
        print '%2d {%d} %-25s [%-8s] (%s)' % (task_id, priority, details, status, deadline)

Non occorre che i parametri posizionali o nominali vengano racchiusi tra virgolette o fatti precedere da sequenze di escape, visto che godono di un trattamento speciale da parte dell'analizzatore dell'interrogazione.

$ python sqlite3_argument_nominal.py pymotw-it

 1 {1} descrivere select         [fatto    ] (2010-10-03)
 2 {1} descrivere random         [in attesa] (2010-10-10)
 3 {1} descrivere sqlite3        [attivo   ] (2010-10-17)

I parametri di interrogazione possono essere usati con le istruzioni select , insert ed update . Essi possono comparire in qualsiasi parte dell'interrogazione fintanto che il valore letterale è consentito.

import sqlite3
import sys

db_filename = 'todo.db'
id = int(sys.argv[1])
status = sys.argv[2]

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()
    query = "update compito set stato = :stato where id = :id"
    cursor.execute(query, {'stato':status, 'id':id})

Questa istruzione update usa due parametri nominali. Il valore id viene usato per cercare la giusta riga da modificare, mentre il valore stato viene scritto nella tabella.

$ python sqlite3_argument_update.py 2 done
$ python sqlite3_argument_named.py pymotw-it

 1 {1} descrivere select         [fatto   ] (2010-10-03)
 2 {1} descrivere random         [fatto   ] (2010-10-10)
 3 {1} descrivere sqlite3        [attivo  ] (2010-10-17)

Caricamento Massivo

Per applicare la stessa istruzione SQL ad un gran numero di dati si usa executemany() . Questo metodo è utile per caricare i dati, visto che si evita di iterare attraverso gli input in Python e si lascia che sia la libreria sottostante ad occuparsi dell'ottimizzazione delle iterazioni. Questo programma di esempio legge una lista di compiti da un file in formato .csv (valori separati da virgola) usando il modulo csv e li carica nel database.

import csv
import sqlite3
import sys

db_filename = 'todo.db'
data_filename = sys.argv[1]

SQL = """insert into compito (dettagli, priorita, stato, scadenza, progetto)
         values (:dettagli, :priorita, 'attivo', :scadenza, :progetto)
      """

with open(data_filename, 'rt') as csv_file:
    csv_reader = csv.DictReader(csv_file)

    with sqlite3.connect(db_filename) as conn:
        cursor = conn.cursor()
        cursor.executemany(SQL, csv_reader)

I dati di esempio contenuti nel file tasks.csv sono:

$ cat tasks.csv

scadenza,progetto,priorita,dettagli
2010-10-02,pymotw-it,2,"finire la revisione di markup"
2010-10-03,pymotw-it,2,"revisione del capitolo intros"
2010-10-03,pymotw-it,1,"subtitle"

L'esecuzione del programma produce:

$ python sqlite3_load_csv.py tasks.csv
$ python sqlite3_argument_nominal.py pymotw-it

 4 {2} finire la revisione di markup [attivo   ] (2010-10-02)
 1 {1} descrivere select             [fatto    ] (2010-10-03)
 6 {1} subtitle                      [attivo   ] (2010-10-03)
 5 {2} revisione del capitolo intros [attivo   ] (2010-10-03)
 2 {1} descrivere random             [in attesa] (2010-10-10)
 3 {1} descrivere sqlite3            [attivo   ] (2010-10-17)

Tipi di Colonna

SQLite ha supporto nativo per colonne di interi, valori a virgola mobile, e colonne di testo. Dati di questi tipi sono convertiti automaticamente da sqlite3 dalla rappresentazione di Python in un valore che possa essere conservato nel database, e viceversa, quando necessario. I valori interi sono caricati dal database in variabili int oppure long , a seconda della dimensione del valore. Il testo viene salvato e recuperato come unicode , a meno che non sia stato modificato il valore text_factory della connessione .

Sebbene SQLite supporti solo pochi tipi di dato internamente, sqlite3 comprende delle facilitazioni per definire tipi personalizzati per consentire all'applicazione Python di conservare in una colonna un qualsiasi tipo di dato. La conversione tra tipi oltre a quelli supportati in modo predefinito sono abilitati nella connessione al database usando il flag detect_types . Si usa PARSE_DECLTYPES se la colonna è stata dichiarata usando il tipo desiderato quando la tabella è stata definita.

import sqlite3
import sys

db_filename = 'todo.db'

sql = "select id, dettagli, scadenza from compito"

def show_deadline(conn):
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute(sql)
    row = cursor.fetchone()
    for col in ['id', 'dettagli', 'scadenza']:
        print '  colunna:', col
        print '    valore :', row[col]
        print '    tipo   :', type(row[col])
    return

print 'Senza identificazione del tipo:'

with sqlite3.connect(db_filename) as conn:
    show_deadline(conn)

print '\nCon identificazione del tipo:'

with sqlite3.connect(db_filename, detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    show_deadline(conn)

sqlite3 fornisce del convertitori per colonne data e timestamp, usando date e datetime dal modulo datetime per rappresentare i valori in Python. Entrambi i convertitori relativi alle date sono abilitati automaticamente quando viene attivata l'identificazione del tipo.

$ python sqlite3_date_types.py
Senza identificazione del tipo:
  colunna: id
    valore : 1
    tipo   : 
  colunna: dettagli
    valore : descrivere select
    tipo   : 
  colunna: scadenza
    valore : 2010-10-03
    tipo   : 

Con identificazione del tipo:
  colunna: id
    valore : 1
    tipo   : 
  colunna: dettagli
    valore : descrivere select
    tipo   : 
  colunna: scadenza
    valore : 2010-10-03
    tipo   : 

Tipi Personalizzati

Occorre registrare due funzioni per definire un nuovo tipo. adapter riceve l'oggetto Python come input e lo restituisce come stringa di byte che può essere conservata nel database. converter riceve la stringa dal database e restituisce un oggetto Python. Si usa register_adapter() per definire una funzione adapter e register_converter() per la funzione converter .

import sqlite3
try:
    import cPickle as pickle
except:
    import pickle

db_filename = 'todo.db'

def adapter_func(obj):
    """Converte da in-memoria alla rappresentazione del valore da conservare
    """
    print 'adapter_func(%s)\n' % obj
    return pickle.dumps(obj)

def converter_func(data):
    """Converte da valore conservato a rappresentazione in-memoria.
    """
    print 'converter_func(%r)\n' % data
    return pickle.loads(data)


class MyObj(object):
    def __init__(self, arg):
        self.arg = arg
    def __str__(self):
        return 'MyObj(%r)' % self.arg

# Registra le funczioni per la manipolazione del tipo.
sqlite3.register_adapter(MyObj, adapter_func)
sqlite3.register_converter("MyObj", converter_func)

# Crea alcuni oggetti da salvare.  Usa una lista di tuple in modo da poter
# passare questa sequanza direttamente a executemany().
to_save = [ (MyObj("questo e' il valore da salvare"),),
            (MyObj(42),),
            ]

with sqlite3.connect(db_filename, detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    # Crea una tabella con una colonna di tipo MyObj"
    conn.execute("""
    create table if not exists obj (
        id    integer primary key autoincrement not null,
        data  MyObj
    )
    """)
    cursor = conn.cursor()

    # Inserisce gli oggetti nel  database
    cursor.executemany("insert into obj (data) values (?)", to_save)

    # Interroga il database richiedendo gli oggetti appena salvati
    cursor.execute("select id, data from obj")
    for obj_id, obj in cursor.fetchall():
        print 'Recuperato', obj_id, obj, type(obj)
        print

Questo esempio usa pickle per salvare un oggetto verso una stringa che possa essere conservata nel database. Questa tecnica è utile per conservare oggetti arbitrari, ma non consente una interrogazione basata sugli attributi dell'oggetto. Un vero mappatore relazionale di oggetti tipo SQLAlchemy che conserva i valori di attributo nelle sue proprie colonne sarebbe molto più utile per grandi quantità di dati.

$ python sqlite3_custom_type.py

adapter_func(MyObj("questo e' il valore da salvare"))

adapter_func(MyObj(42))

converter_func('ccopy_reg\n_reconstructor\np1\n(c__main__\nMyObj\np2\nc__builtin__\nobject\np3\nNtRp4\n(dp5\nS\'arg\'\np6\nS"questo e\' il valore da salvare"\np7\nsb.')

converter_func("ccopy_reg\n_reconstructor\np1\n(c__main__\nMyObj\np2\nc__builtin__\nobject\np3\nNtRp4\n(dp5\nS'arg'\np6\nI42\nsb.")

Recuperato 1 MyObj("questo e' il valore da salvare") 

Recuperato 2 MyObj(42) 

Derivare i Tipi dai Nomi delle Colonne

Ci sono due fonti per le informazioni sul tipo per quanto riguarda i dati per una interrogazione. La dichiarazione originale della tabella può essere usata per identificare il tipo di una colonna reale, come mostrato qui sopra. Uno specificatore di tipo può essere anche incluso nella clausola select dell'interrogazione usando la forma " nome [tipo] ".

import sqlite3
try:
    import cPickle as pickle
except:
    import pickle

db_filename = 'todo.db'

def adapter_func(obj):
    """Converte da in-memoria alla rappresentazione del valore da conservare
    """
    print 'adapter_func(%s)\n' % obj
    return pickle.dumps(obj)

def converter_func(data):
    """Converte da valore conservato a rappresentazione in-memoria.
    """
    print 'converter_func(%r)\n' % data
    return pickle.loads(data)


class MyObj(object):
    def __init__(self, arg):
        self.arg = arg
    def __str__(self):
        return 'MyObj(%r)' % self.arg

# Registra le funczioni per la manipolazione del tipo.
sqlite3.register_adapter(MyObj, adapter_func)
sqlite3.register_converter("MyObj", converter_func)

# Crea alcuni oggetti da salvare.  Usa una lista di tuple in modo da poter
# passare questa sequanza direttamente a executemany().
to_save = [ (MyObj("questo e' il valore da salvare"),),
            (MyObj(42),),
            ]

with sqlite3.connect(db_filename, detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    # Crea una tabella con una colonna di tipo MyObj"
    conn.execute("""
    create table if not exists obj (
        id    integer primary key autoincrement not null,
        data  MyObj
    )
    """)
    cursor = conn.cursor()

    # Inserisce gli oggetti nel  database
    cursor.executemany("insert into obj (data) values (?)", to_save)

    # Interroga il database richiedendo gli oggetti appena salvati
    cursor.execute("select id, data from obj")
    for obj_id, obj in cursor.fetchall():
        print 'Recuperato', obj_id, obj, type(obj)
        print

Si usa il flag detect_types con il valore PARSE_COLNAMES quando il tipo è parte dell'interrogazione invece che della definizione originale della tabella.

$ python sqlite3_custom_type_column.py

adapter_func(MyObj("questo e' il valore da salvare"))

adapter_func(MyObj(42))

converter_func('ccopy_reg\n_reconstructor\np1\n(c__main__\nMyObj\np2\nc__builtin__\nobject\np3\nNtRp4\n(dp5\nS\'arg\'\np6\nS"questo e\' il valore da salvare"\np7\nsb.')

converter_func("ccopy_reg\n_reconstructor\np1\n(c__main__\nMyObj\np2\nc__builtin__\nobject\np3\nNtRp4\n(dp5\nS'arg'\np6\nI42\nsb.")

Recuperato 1 MyObj("questo e' il valore da salvare") 

Recuperato 2 MyObj(42) 

Transazioni

Una delle funzionalità chiave dei database relazionali è l'uso delle transazioni per mantenere uno stato interno consistente. Con le transazioni abilitate, si possono effettuare parecchie modifiche tramite una connessione senza che qualsiasi altro utente ne sia coinvolto, fino a che i risultati sono confermati e definitivamente scritti sul database reale.

Preservare le Modifiche

Le modifiche al database, sia tramite istruzioni di insert o update devono essere salvate chiamando esplicitamente commit() . Questo requisito fornisce ad una applicazione la possibilità di effettuare parecchie modifiche insieme, per poi conservarle atomicamente invece che in modo incrementale, evitando situazioni nelle quali aggiornamenti parziali possano essere visti da client diversi in connessione al database.

L'effetto della chiamata di commit() può essere visto con un programma che usa parecchie connessioni al database. Una nuova riga viene inserita con la prima connessione, quindi vengono effettuati due tentativi di leggere la riga inserita usando connessioni separate.

import sqlite3

db_filename = 'todo.db'

def show_projects(conn):
    cursor = conn.cursor()
    cursor.execute('select nome, descrizione from progetto')
    for name, desc in cursor.fetchall():
        print '  ', name
    return

with sqlite3.connect(db_filename) as conn1:

    print 'Prima delle modifiche:'
    show_projects(conn1)

    # Istruzione Insert in un cursore
    cursor1 = conn1.cursor()
    cursor1.execute("""
    insert into progetto (nome, descrizione, scadenza)
    values ('virtualenvwrapper', 'Estensioni Virtualenv', '2011-01-01')
    """)

    print '\nDopo le modifiche in conn1:'
    show_projects(conn1)

    # Istruzione Select da un'altra connessione, senza prima chiamare commit
    print '\nPrima di commit:'
    with sqlite3.connect(db_filename) as conn2:
        show_projects(conn2)

    # Chiamata di Commit quindi select da un'altra connessione
    conn1.commit()
    print '\nDopo commit:'
    with sqlite3.connect(db_filename) as conn3:
        show_projects(conn3)

Quando viene chiamato show_projects() prima della chiamata di commit da conn1 , i risultati dipendono da quale connessione viene usata. Visto che le modifiche sono state fatte tramite conn1 , essa vede i dati modificati. D'altro canto conn2 non li vede. Dopo la chiamata di commit, la nuova connessione conn3 vede la riga inserita.

$ python sqlite3_transaction_commit.py
Prima delle modifiches:
   pymotw-it

Dopo le modifiche in conn1:
   pymotw-it
   virtualenvwrapper

Prima di commit:
   pymotw-it

Dopo commit:
   pymotw-it
   virtualenvwrapper

Scartare le Modifiche

Le modifiche non ancora confermate possono essere scartate interamente usando rollback() . I metodi commit() e rollback() sono in genere chiamati da parti diverse dello stesso blocco tyr:except , con gli errori che fanno scattare lo scarto delle modifiche.

import sqlite3

db_filenome = 'todo.db'

def show_projects(conn):
    cursor = conn.cursor()
    cursor.execute('select nome, descrizione from progetto')
    for nome, desc in cursor.fetchall():
        print '  ', nome
    return

with sqlite3.connect(db_filenome) as conn:

    print 'Prima delle modifiche:'
    show_projects(conn)

    try:

        # Istruzione di cancellazione
        cursor = conn.cursor()
        cursor.execute("delete from progetto where nome = 'virtualenvwrapper'")

        # Mostra i risultati
        print '\Dopo la cancellazione:'
        show_projects(conn)

        # Finge che l'esecuzione abbia causato un errore
        raise RuntimeError('errore simulato')

    except Exception, err:
        # Scarta le  modifiche
        print 'ERRORE:', err
        conn.rollback()

    else:
        # Salva le modifiche
        conn.commit()

    # Mostra i risultati
    print '\nDopo la chiamata di rollback:'
    show_progettos(conn)

Dopo la chiamata di rollback() le modifiche al database non esistono più.

$ python sqlite3_transaction_rollback.py

Prima delle modifiche:
   pymotw-it
   virtualenvwrapper
\Dopo la cancellazione:
   pymotw-it
ERRORE: errore simulato

Dopo la chiamata di rollback:
   pymotw-it
   virtualenvwrapper

Livelli di Isolamento

sqlite3 supporta tre modalità di bloccaggio, chiamate livelli di isolamento che controllano i bloccaggi usati per prevenire modifiche incompatibili tra le connessioni. Il livello di isolamento viene impostato passando una stringa al parametro isolation_level quando viene aperta una connessione, in modo che diverse connessioni possano usare diversi valori.

Questo programma dimostra l'effetto di diversi livelli di isolamento sull'ordine di eventi nei thread usando connessioni separate allo stesso database. Vengono creati quattro thread. Due scrivono le modifiche al database aggiornando righe esistenti. Gli altri tentano di leggere tutte le righe della tabella compito .

import logging
import sqlite3
import sys
import threading
import time

logging.basicConfig(level=logging.DEBUG,
                    format='%(asctime)s (%(threadName)-10s) %(message)s',
                    )

db_filename = 'todo.db'
isolation_level = sys.argv[1]

def writer():
    my_name = threading.currentThread().name
    logging.debug('in connessione')
    with sqlite3.connect(db_filename, isolation_level=isolation_level) as conn:
        cursor = conn.cursor()
        logging.debug('connesso')
        cursor.execute('update compito set priorita = priorita + 1')
        logging.debug('modifiche effettuate')
        logging.debug('in attesa di sincronizzare')
        ready.wait() # sincronizza
        logging.debug('IN PAUSA')
        time.sleep(1)
        conn.commit()
        logging.debug('MODIFICHE EFFETTUATE')
    return

def reader():
    my_name = threading.currentThread().name
    with sqlite3.connect(db_filename, isolation_level=isolation_level) as conn:
        cursor = conn.cursor()
        logging.debug('in attesa di sincronizzare')
        ready.wait() # sincronizza
        logging.debug('attendo oltre')
        cursor.execute('select * from compito')
        logging.debug('SELECT ESEGUITO')
        results = cursor.fetchall()
        logging.debug('risultati recuperati')
    return

if __name__ == '__main__':
    ready = threading.Event()

    threads = [
        threading.Thread(name='Lettore 1', target=reader),
        threading.Thread(name='Lettore 2', target=reader),
        threading.Thread(name='Scrittore 1', target=writer),
        threading.Thread(name='Scrittore 2', target=writer),
        ]

    [ t.start() for t in threads ]

    time.sleep(1)
    logging.debug('impostazioni pronte')
    ready.set()

    [ t.join() for t in threads ]

I thread sono sincronizzati usando Event dal modulo threading. La funzione writer() connette ed effettua le modifiche al database, ma non chiama commit prima che scatti l'evento. La funzione reader() si connette, quindi attende prima di interrogare il database fino a quando l'evento di sincronizzazione non si verifica.

Isolamento Differito

Il livello di isolamento predefinito è DEFERRED (differito). L'uso di questa modalità blocca il database, ma solo dopo che una modifica è iniziata. Tutti gli esempi precedenti usano la modalità differita.

$ python sqlite3_isolation_levels.py DEFERRED

2011-02-13 09:56:18,943 (Lettore 1 ) in attesa di sincronizzare
2011-02-13 09:56:18,943 (Lettore 2 ) in attesa di sincronizzare
2011-02-13 09:56:18,943 (Scrittore 1) in connessione
2011-02-13 09:56:18,943 (Scrittore 2) in connessione
2011-02-13 09:56:18,944 (Scrittore 1) connesso
2011-02-13 09:56:18,944 (Scrittore 2) connesso
2011-02-13 09:56:18,966 (Scrittore 2) modifiche effettuate
2011-02-13 09:56:18,966 (Scrittore 2) in attesa di sincronizzare
2011-02-13 09:56:19,944 (MainThread) impostazioni pronte
2011-02-13 09:56:19,945 (Scrittore 2) IN PAUSA
2011-02-13 09:56:19,945 (Lettore 1 ) attendo oltre
2011-02-13 09:56:19,945 (Lettore 2 ) attendo oltre
2011-02-13 09:56:19,946 (Lettore 2 ) SELECT ESEGUITO
2011-02-13 09:56:19,946 (Lettore 2 ) risultati recuperati
2011-02-13 09:56:19,947 (Lettore 1 ) SELECT ESEGUITO
2011-02-13 09:56:19,947 (Lettore 1 ) risultati recuperati
2011-02-13 09:56:20,999 (Scrittore 2) MODIFICHE EFFETTUATE
2011-02-13 09:56:21,090 (Scrittore 1) modifiche effettuate
2011-02-13 09:56:21,090 (Scrittore 1) in attesa di sincronizzare
2011-02-13 09:56:21,090 (Scrittore 1) IN PAUSA
2011-02-13 09:56:22,111 (Scrittore 1) MODIFICHE EFFETTUATE

Isolamento Immediato

La modalità di isolamento immediato blocca il database non appena viene iniziata una modifica e previene gli altri cursori dall'effettuare modifiche fino a che viene chiamato commit sulla transazione. E' adatto per un database che debba effettuare scritture complesse ma con più accessi in lettura che in scrittura, visto che gli accessi in lettura non sono bloccati mentre la transazione è in esecuzione.

$ python sqlite3_isolation_levels.py IMMEDIATE

2011-02-13 10:09:17,984 (Lettore 1 ) in attesa di sincronizzare
2011-02-13 10:09:17,984 (Scrittore 1) in connessione
2011-02-13 10:09:17,984 (Scrittore 1) connesso
2011-02-13 10:09:17,985 (Scrittore 2) in connessione
2011-02-13 10:09:17,985 (Scrittore 2) connesso
2011-02-13 10:09:17,986 (Lettore 2 ) in attesa di sincronizzare
2011-02-13 10:09:17,988 (Scrittore 1) modifiche effettuate
2011-02-13 10:09:17,988 (Scrittore 1) in attesa di sincronizzare
2011-02-13 10:09:18,986 (MainThread) impostazioni pronte
2011-02-13 10:09:18,986 (Lettore 1 ) attendo oltre
2011-02-13 10:09:18,987 (Lettore 1 ) SELECT ESEGUITO
2011-02-13 10:09:18,987 (Lettore 1 ) risultati recuperati
2011-02-13 10:09:18,987 (Scrittore 1) IN PAUSA
2011-02-13 10:09:18,988 (Lettore 2 ) attendo oltre
2011-02-13 10:09:18,988 (Lettore 2 ) SELECT ESEGUITO
2011-02-13 10:09:18,988 (Lettore 2 ) risultati recuperati
2011-02-13 10:09:20,007 (Scrittore 1) MODIFICHE EFFETTUATE
2011-02-13 10:09:20,019 (Scrittore 2) modifiche effettuate
2011-02-13 10:09:20,019 (Scrittore 2) in attesa di sincronizzare
2011-02-13 10:09:20,019 (Scrittore 2) IN PAUSA
2011-02-13 10:09:21,606 (Scrittore 2) MODIFICHE EFFETTUATE

Isolamento Esclusivo

La modalità di isolamento esclusiva blocca il database a tutti gli accessi in lettura e scrittura. Il suo uso è limitato a situazioni dove le prestazioni sul database siano importanti, visto che ogni connessione esclusiva blocca tutti gli altri utenti.

$ python sqlite3_isolation_levels.py EXCLUSIVE

2011-02-13 10:11:48,784 (Lettore 2 ) in attesa di sincronizzare
2011-02-13 10:11:48,784 (Lettore 1 ) in attesa di sincronizzare
2011-02-13 10:11:48,784 (Scrittore 1) in connessione
2011-02-13 10:11:48,785 (Scrittore 1) connesso
2011-02-13 10:11:48,786 (Scrittore 1) modifiche effettuate
2011-02-13 10:11:48,786 (Scrittore 1) in attesa di sincronizzare
2011-02-13 10:11:48,787 (Scrittore 2) in connessione
2011-02-13 10:11:48,787 (Scrittore 2) connesso
2011-02-13 10:11:49,788 (MainThread) impostazioni pronte
2011-02-13 10:11:49,788 (Lettore 1 ) attendo oltre
2011-02-13 10:11:49,788 (Scrittore 1) IN PAUSA
2011-02-13 10:11:49,788 (Lettore 2 ) attendo oltre
2011-02-13 10:11:50,807 (Scrittore 1) MODIFICHE EFFETTUATE
2011-02-13 10:11:50,818 (Scrittore 2) modifiche effettuate
2011-02-13 10:11:50,818 (Scrittore 2) in attesa di sincronizzare
2011-02-13 10:11:50,818 (Scrittore 2) IN PAUSA
2011-02-13 10:11:51,839 (Scrittore 2) MODIFICHE EFFETTUATE
2011-02-13 10:11:51,919 (Lettore 1 ) SELECT ESEGUITO
2011-02-13 10:11:51,919 (Lettore 2 ) SELECT ESEGUITO
2011-02-13 10:11:51,920 (Lettore 1 ) risultati recuperati
2011-02-13 10:11:51,920 (Lettore 2 ) risultati recuperati

Visto che la prima connessione in scrittura ha iniziato a compiere modifiche, le connessioni in lettura e la seconda connessione in scrittura sono bloccate fino alla chiamata di commit della prima. La chiamata di sleep() induce un ritardo artificiale nel thread di scrittura per evidenziare il fatto che le altre connessioni sono bloccate.

Modalità Autocommit

Il parametro isolation_level per una connessione puà anche essere impostato a None per abilitare la modalità di conferma automatica (autocommit). Con autocommit abilitato, ogni chiamata di execute() viene confermata immediatamente dopo che l'istruzione finisce. La modalità autocommit è adatta per brevi transazioni, tipo l'inserimento di una piccola mole di dati in una singola tabella. Il database viene bloccato per il minor tempo possibile, in modo che le possibilità di conflitto tra i thread siano minori.

import logging
import sqlite3
import sys
import threading
import time

logging.basicConfig(level=logging.DEBUG,
                    format='%(asctime)s (%(threadName)-10s) %(message)s',
                    )

db_filename = 'todo.db'
isolation_level = None # autocommit mode

def writer():
    my_name = threading.currentThread().name
    logging.debug('in connessione')
    with sqlite3.connect(db_filename, isolation_level=isolation_level) as conn:
        cursor = conn.cursor()
        logging.debug('connesso')
        cursor.execute('update compito set priorita = priorita + 1')
        logging.debug('modifiche effettuate')
        logging.debug('in attesa di sincronizzare')
        ready.wait() # sincronizza
        logging.debug('IN PAUSA')
        logging.debug('MODIFICHE EFFETTUATE')
        time.sleep(1)
    return

def reader():
    my_name = threading.currentThread().name
    with sqlite3.connect(db_filename, isolation_level=isolation_level) as conn:
        cursor = conn.cursor()
        logging.debug('in attesa di sincronizzare')
        ready.wait() # sincronizza
        logging.debug('attendo oltre')
        cursor.execute('select * from compito')
        logging.debug('SELECT ESEGUITO')
        results = cursor.fetchall()
        logging.debug('risultati recuperati')
    return

if __name__ == '__main__':
    ready = threading.Event()

    threads = [
        threading.Thread(name='Lettore 1', target=reader),
        threading.Thread(name='Lettore 2', target=reader),
        threading.Thread(name='Scrittore 1', target=writer),
        threading.Thread(name='Scrittore 2', target=writer),
        ]

    [ t.start() for t in threads ]

    time.sleep(1)
    logging.debug('impostazioni pronte')
    ready.set()

    [ t.join() for t in threads ]

La chiamata esplicita a commit() è stata rimossa, ma per tutto il resto sqlite3_autocommit.py è uguale a sqlite3_isolation_levels.py . L'output è diverso, comunque, visto che entrambi i thread in scrittura finiscono il proprio lavoro prima che i thread in lettura inizino le interrogazioni.

$ python sqlite3_autocommit.py

2011-02-13 13:15:31,097 (Scrittore 1) in connessione
2011-02-13 13:15:31,097 (Scrittore 2) in connessione
2011-02-13 13:15:31,120 (Lettore 2 ) in attesa di sincronizzare
2011-02-13 13:15:31,120 (Scrittore 1) connesso
2011-02-13 13:15:31,127 (Scrittore 2) connesso
2011-02-13 13:15:31,127 (Lettore 1 ) in attesa di sincronizzare
2011-02-13 13:15:31,247 (Scrittore 1) modifiche effettuate
2011-02-13 13:15:31,248 (Scrittore 1) in attesa di sincronizzare
2011-02-13 13:15:31,343 (Scrittore 2) modifiche effettuate
2011-02-13 13:15:31,343 (Scrittore 2) in attesa di sincronizzare
2011-02-13 13:15:32,098 (MainThread) impostazioni pronte
2011-02-13 13:15:32,099 (Scrittore 1) IN PAUSA
2011-02-13 13:15:32,099 (Scrittore 1) MODIFICHE EFFETTUATE
2011-02-13 13:15:32,099 (Scrittore 2) IN PAUSA
2011-02-13 13:15:32,099 (Scrittore 2) MODIFICHE EFFETTUATE
2011-02-13 13:15:32,099 (Lettore 1 ) attendo oltre
2011-02-13 13:15:32,100 (Lettore 2 ) attendo oltre
2011-02-13 13:15:32,101 (Lettore 2 ) SELECT ESEGUITO
2011-02-13 13:15:32,101 (Lettore 1 ) SELECT ESEGUITO
2011-02-13 13:15:32,101 (Lettore 1 ) risultati recuperati
2011-02-13 13:15:32,101 (Lettore 2 ) risultati recuperati

Comportamenti Definiti dall'Utente

sqlite3 supporta diversi meccanismi di estensione, con supporto per estendere la funzionalità del database con funzioni e classi implementate in Python.

Usare Funzioni Python in SQL

La sintassi SQL supporta la chiamata a funzioni durante le interrogazioni, sia nell'elenco di colonne che nella clausola where dell'istruzione select . Questa funzionalità rende possibile l'elaborazione dei dati prima di essere restituiti dall'interrogazione, e può essere usata per convertire tra diversi formati, eseguire calcoli che sarebbero mal costruiti in puro linguaggio SQL, e riusare il codice dell'applicazione.

import sqlite3

db_filename = 'todo.db'

def encrypt(s):
    print 'Codifica %r' % s
    return s.encode('rot-13')

def decrypt(s):
    print 'Decodifica %r' % s
    return s.encode('rot-13')


with sqlite3.connect(db_filename) as conn:
    conn.create_function('encrypt', 1, encrypt)
    conn.create_function('decrypt', 1, decrypt)
    cursor = conn.cursor()

    # Raw values
    print 'Valori originali:'
    query = "select id, dettagli from compito"
    cursor.execute(query)
    for row in cursor.fetchall():
        print row

    print '\nCodifica...'
    query = "update compito set dettagli = encrypt(dettagli)"
    cursor.execute(query)

    print '\nValori codificati grezzi:'
    query = "select id, dettagli from compito"
    cursor.execute(query)
    for row in cursor.fetchall():
        print row

    print "\nDecodifica nell'interrogazione ..."
    query = "select id, decrypt(dettagli) from compito"
    cursor.execute(query)
    for row in cursor.fetchall():
        print row

Le funzioni sono esposte usando il metodo create_function() della Connessione . I parametri sono il nome della funzione (come dovrebbe essere usata dall'interno di SQL), il numero di parametri che riceve la funzione e la funzione Python da esporre.

$ python sqlite3_create_function.py

Valori originali:
(1, u'descrivere select')
(2, u'descrivere random')
(3, u'descrivere sqlite3')
(4, u'finire la revisione di markup')
(5, u'revisione del capitolo intros')
(6, u'subtitle')

Codifica...
Codifica u'descrivere select'
Codifica u'descrivere random'
Codifica u'descrivere sqlite3'
Codifica u'finire la revisione di markup'
Codifica u'revisione del capitolo intros'
Codifica u'subtitle'

Valori codificati grezzi:
(1, u'qrfpevirer fryrpg')
(2, u'qrfpevirer enaqbz')
(3, u'qrfpevirer fdyvgr3')
(4, u'svaver yn erivfvbar qv znexhc')
(5, u'erivfvbar qry pncvgbyb vagebf')
(6, u'fhogvgyr')

Decodifica nell'interrogazione ...
Decodifica u'qrfpevirer fryrpg'
Decodifica u'qrfpevirer enaqbz'
Decodifica u'qrfpevirer fdyvgr3'
Decodifica u'svaver yn erivfvbar qv znexhc'
Decodifica u'erivfvbar qry pncvgbyb vagebf'
Decodifica u'fhogvgyr'
(1, u'descrivere select')
(2, u'descrivere random')
(3, u'descrivere sqlite3')
(4, u'finire la revisione di markup')
(5, u'revisione del capitolo intros')
(6, u'subtitle')

Aggregazione Personalizzata

Una funzione di aggregazione raccoglie molte parti di dati individuali e li somma in un qualche modo. Esempi di funzioni di aggregazione built-in sono avg() (media), min() , max() e count() .

L'API per gli aggregatori usati da sqlite3 è definita nei termini di una classe con due metodi. Il metodo step() viene chiamato una volta per ogni valore di dati mentre l'interrogazione viene elaborata. Il metodo finalize() viene chiamato una volta alla fine dell'interogazione e dovrebbre restituire il valore aggregato. Questo esempio implementa un aggregatore che ottiene il valore che appare più frequentemente nell'input.

import sqlite3
import collections

db_filename = 'todo.db'

class Mode(object):
    def __init__(self):
        self.counter = collections.Counter()  # Counter è disponibile dalla versione 2.7
    def step(self, value):
        print 'step(%r)' % value
        self.counter[value] += 1
    def finalize(self):
        result, count = self.counter.most_common(1)[0]
        print 'finalize() -> %r (%d volte)' % (result, count)
        return result

with sqlite3.connect(db_filename) as conn:

    conn.create_aggregate('mode', 1, Mode)

    cursor = conn.cursor()
    cursor.execute("select mode(scadenza) from compito where progetto = 'pymotw-it'")
    row = cursor.fetchone()
    print "Il mode(scadenza) è:", row[0]

La classe aggregatore viene registrata con il metodo create_aggregate() della Connessione . I parametri sono il nome della funzione (come dovrebbe essere usata all'interno di SQL), il numero di parametri che riceve il metodo step() e la classe da usare.

$ python sqlite3_create_aggregate.py

step(u'2010-10-03')
step(u'2010-10-10')
step(u'2010-10-17')
step(u'2010-10-02')
step(u'2010-10-03')
step(u'2010-10-03')
finalize() -> u'2010-10-03' (3 volte)
il mode(scadenza) è: 2010-10-03

Ordinamento Personalizzato

Si definisce collation una funzione di confronto usata nella sezione order by di una interrogazione SQL. Le funzioni di confronto personalizzate possono essere usate per comparare tipi di dato che altrimenti non potrebbero essere ordinati internamente da SQLite. Ad esempio una collation personalizzata potebbe essere necessaria per ordinare gli oggetti pickle salvati con lo script sqlite3_custom_type.py qui sopra.

import sqlite3
try:
    import cPickle as pickle
except:
    import pickle

db_filename = 'todo.db'

def adapter_func(obj):
    return pickle.dumps(obj)

def converter_func(data):
    return pickle.loads(data)

class MyObj(object):
    def __init__(self, arg):
        self.arg = arg
    def __str__(self):
        return 'MyObj(%r)' % self.arg
    def __cmp__(self, other):
        return cmp(self.arg, other.arg)

# Registra le funzioni per manipoloare il tipo
sqlite3.register_adapter(MyObj, adapter_func)
sqlite3.register_converter("MyObj", converter_func)

def collation_func(a, b):
    a_obj = converter_func(a)
    b_obj = converter_func(b)
    print 'collation_func(%s, %s)' % (a_obj, b_obj)
    return cmp(a_obj, b_obj)

with sqlite3.connect(db_filename, detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    # Definisce la collation
    conn.create_collation('unpickle', collation_func)

    # Pulisce la tabella ed inserisce i nuovi valori
    conn.execute('delete from obj')
    conn.executemany('insert into obj (data) values (?)',
                     [(MyObj(x),) for x in xrange(5, 0, -1)],
                     )

    # Interroga il database per ottenere gli oggetti appena salvati
    print '\nInterrogazione in corso:'
    cursor = conn.cursor()
    cursor.execute("select id, data from obj order by data collate unpickle")
    for obj_id, obj in cursor.fetchall():
        print obj_id, obj
        print

I parametri della funzione collation sono stringhe di byte, in modo che possano essere estratti dal pickle e convertiti in istanze di MyObj prima che il confronto sia eseguito.

$ python sqlite3_create_collation.py

Interrogazione in corso:
collation_func(MyObj(5), MyObj(4))
collation_func(MyObj(4), MyObj(3))
collation_func(MyObj(4), MyObj(2))
collation_func(MyObj(3), MyObj(2))
collation_func(MyObj(3), MyObj(1))
collation_func(MyObj(2), MyObj(1))
7 MyObj(1)

6 MyObj(2)

5 MyObj(3)

4 MyObj(4)

3 MyObj(5)

Limitare l'accesso ai Dati

Sebbene SQLite non abbia i controlli per l'accesso utente presenti in altri, più grandi database relazionali, ha comunque un meccanismo per limitare l'accesso alle colonne. Ogni connessione può installare una funzione di autorizzazione per consentire o negare l'accesso a colonne in fase di esecuzione in base ad un qualsivoglia criterio. La funzione di autorizzazione viene chiamata durante l'elaborazione delle istruzioni SQL e riceve cinque parametri. Il primo è un codice di azione che indica che tipo di operazione debba essere eseguita (leggere, scrivere, cancellare ecc.). I restanti parametri dipendono dal codice di azione. Per operazioni SQLITE_READ (lettura) i parametri sono il nome della tabella, quello della colonna, il punto nel SQL dove l'accesso deve avvenire (interrogazione principale, trigger ecc.), e None .

import sqlite3

db_filename = 'todo.db'

def authorizer_func(action_code, table, column, sql_location, ignore):
    print '\nauthorizer_func(%s, %s, %s, %s, %s)' % \
        (action_code, table, column, sql_location, ignore)

    response = sqlite3.SQLITE_OK # permissiva per default

    if action_code == sqlite3.SQLITE_SELECT:
        print 'richiesta permessi per eseguire una istruzione select'
        response = sqlite3.SQLITE_OK

    elif action_code == sqlite3.SQLITE_READ:
        print 'richiesta permessi per alla colonna %s.%s da %s' % \
            (table, column, sql_location)
        if column == 'dettagli':
            print '  si ignorano la colonna dettagli'
            response = sqlite3.SQLITE_IGNORE
        elif column == 'priorita':
            print "  si nega l'accesso alla colonna priorita"
            response = sqlite3.SQLITE_DENY

    return response

with sqlite3.connect(db_filename) as conn:
    conn.row_factory = sqlite3.Row
    conn.set_authorizer(authorizer_func)

    print 'Si usa SQLITE_IGNORE per nascondere un valore di colonna:'
    cursor = conn.cursor()
    cursor.execute("select id, dettagli from compito where progetto = 'pymotw-it'")
    for row in cursor.fetchall():
        print row['id'], row['dettagli']

    print "\nSi usa SQLITE_DENY per negare l'accesso alla colonna:"
    cursor.execute("select id, priorita from compito where progetto = 'pymotw-it'")
    for row in cursor.fetchall():
        print row['id'], row['dettagli']

Questo esempio usa SQLITE_IGNORE per fare sì che le stringhe dalla colonna compito.dettagli vengano sostituite con valori null nel risultato dell'interrogazione. Vengono inoltre negati tutti gli accessi alla colonna compito.priorita restituendo SQLITE_DENY , che di conseguenza provoca il sollevamento di una eccezione di SQLite.

$ python sqlite3_set_authorizer.py

Si usa SQLITE_IGNORE per nascondere un valore di colonna:

authorizer_func(21, None, None, None, None)
richiesta permessi per eseguire una istruzione select

authorizer_func(20, compito, id, main, None)
richiesta permessi per alla colonna compito.id da main

authorizer_func(20, compito, dettagli, main, None)
richiesta permessi per alla colonna compito.dettagli da main
  si ignorano la colonna dettagli

authorizer_func(20, compito, progetto, main, None)
richiesta permessi per alla colonna compito.progetto da main
1 None
2 None
3 None

Si usa SQLITE_DENY per negare l'accesso alla colonna:

authorizer_func(21, None, None, None, None)
richiesta permessi per eseguire una istruzione select

authorizer_func(20, compito, id, main, None)
richiesta permessi per alla colonna compito.id da main

authorizer_func(20, compito, priorita, main, None)
richiesta permessi per alla colonna compito.priorita da main
  si nega l'accesso alla colonna priorita
Traceback (most recent call last):
  File "sqlite3_set_authorizer.py", line 41, in 
    cursor.execute("select id, priorita from compito where progetto = 'pymotw-it'")
sqlite3.DatabaseError: access to compito.priorita is prohibited

I possibili codici di azione come costanti sono disponibili in sqlite3 , i nomi sono prefissati da SQLITE_ . Ciascun tipo di istruzione SQL può essere contrassegnata, così come può essere controllato anche l'accesso alle singole colonne.

Database In-Memoria

SQLite supporta la gestione di interi database nella RAM, invece che appoggiarsi ad un file su disco. I database in-memoria sono utili per test automatici, dove il database non deve essere preservato fra un test e l'altro, oppure per fare esperimenti con uno schema od altre funzionalità del database. Per aprire un database in-memoria, si usa la stringa ':memory:' invece che un nome di file mentre si crea la Connessione .

import os
import sqlite3


schema_filename = 'todo_schema.sql'

with sqlite3.connect(':memory:') as conn:
    conn.row_factory = sqlite3.Row

    print 'Creazione dello schema'
    with open(schema_filename, 'rt') as f:
        schema = f.read()
    conn.executescript(schema)

    print 'Inserimento dei dati di partenza'

    conn.execute("""
    insert into progetto (nome, descrizione, scadenza)
    values ('pymotw-it', 'Il modulo Python della Settimana', '2010-11-01')
    """)

    data = [
        ('descrivere select', 'fatto', '2010-10-03', 'pymotw-it'),
        ('descrivere random', 'in attesa', '2010-10-10', 'pymotw-it'),
        ('descrivere sqlite3', 'attivo', '2010-10-17', 'pymotw-it'),
        ]

    conn.executemany("""
            insert into compito (dettagli, stato, scadenza, progetto)
            values (?, ?, ?, ?)
            """, data)

    print 'Cerco i compiti ...'
    cursor = conn.cursor()
    cursor.execute("""
    select id, priorita, stato, scadenza, dettagli from compito
    where progetto = 'pymotw-it' order by scadenza
    """)
    for row in cursor.fetchall():
        print '%2d {%d} %-25s [%-8s] (%s)' % (
            row['id'], row['priorita'], row['dettagli'], row['stato'], row['scadenza'],
            )

with sqlite3.connect(':memory:') as conn2:
    print '\nCerco compiti nella seconda connessione...'
    cursor = conn2.cursor()
    cursor.execute("""
    select id, priorita, stato, scadenza, dettagli from compito
    where progetto = 'pymotw-it' order by scadenza
    """)
    for row in cursor.fetchall():
        print '%2d {%d} %-25s [%-8s] (%s)' % (
            row['id'], row['priorita'], row['dettagli'], row['stato'], row['scadenza'],
            )

Il secondo tentativo di interrogazione in questo esempio fallisce con un errore visto che la tabella non esiste. Ogni connessione crea un database separato, quindi le modifiche fatte da un cursore di una connessione non si ripercuotono sulle altre.

$ python sqlite3_memory.py

Creazione dello schema
Inserimento dei dati di partenza
Cerco i compiti ...
 1 {1} descrivere select         [fatto   ] (2010-10-03)
 2 {1} descrivere random         [in attesa] (2010-10-10)
 3 {1} descrivere sqlite3        [attivo  ] (2010-10-17)

Cerco compiti nella seconda connessione...
Traceback (most recent call last):
  File "sqlite3_memory.py", line 53, in 
    """)
sqlite3.OperationalError: no such table: compito

Esportare il Contenuto di un Database

Il contenuto di un database in-memoria può essere salvato usando il metodo iterdump() della Connessione . L'iteratore restituito da iterdump() produce una serie di stringhe che assieme costruiscono istruzioni SQL per ricreare lo stato del database.

import os
import sqlite3


schema_filename = 'todo_schema.sql'

with sqlite3.connect(':memory:') as conn:
    conn.row_factory = sqlite3.Row

    print 'Creazione dello schema'
    with open(schema_filename, 'rt') as f:
        schema = f.read()
    conn.executescript(schema)

    print 'Inserimento dei dati di partenza'

    conn.execute("""
    insert into progetto (nome, descrizione, scadenza)
    values ('pymotw-it', 'Il modulo Python della Settimana', '2010-11-01')
    """)

    data = [
        ('descrivere select', 'fatto', '2010-10-03', 'pymotw-it'),
        ('descrivere random', 'in attesa', '2010-10-10', 'pymotw-it'),
        ('descrivere sqlite3', 'attivo', '2010-10-17', 'pymotw-it'),
        ]

    conn.executemany("""
            insert into compito (dettagli, stato, scadenza, progetto)
            values (?, ?, ?, ?)
            """, data)

    print 'Scaricamento:'
    for text in conn.iterdump():
        print text

iterdump() può anche essere usato con database salvati su file, ma è più utile per preservare un database che altrimenti non potrebbe essere salvato.

$ python sqlite3_iterdump.py
Creazione dello schema
Inserimento dei dati di partenza
Scaricamento:
BEGIN TRANSACTION;
CREATE TABLE progetto (
    nome        text primary key,
    descrizione text,
    scadenza    date
);
INSERT INTO "progetto" VALUES('pymotw-it','Il modulo Python della Settimana','2010-11-01');
CREATE TABLE compito (
    id            integer primary key autoincrement not null,
    priorita      integer default 1,
    dettagli      text,
    stato         text,
    scadenza      date,
    completato_il date,
    progetto      text not null references project(name)
);
INSERT INTO "compito" VALUES(1,1,'descrivere select','fatto','2010-10-03',NULL,'pymotw-it');
INSERT INTO "compito" VALUES(2,1,'descrivere random','in attesa','2010-10-10',NULL,'pymotw-it');
INSERT INTO "compito" VALUES(3,1,'descrivere sqlite3','attivo','2010-10-17',NULL,'pymotw-it');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('compito',3);
COMMIT;

Threading e Condivisione della Connessione

Per ragioni storiche che hanno a che fare con vecchie versioni di SQLite, gli oggetti Connection non possono essere condivisi fra thread. Ogni thread deve creare la sua propria connessione al database.

import sqlite3
import sys
import threading
import time

db_filename = 'todo.db'
isolation_level = None # modalità autocommit

def reader(conn):
    my_name = threading.currentThread().name
    print 'Partenza del thread'
    try:
        cursor = conn.cursor()
        cursor.execute('select * from compiti')
        results = cursor.fetchall()
        print 'risultati recuperati'
    except Exception, err:
        print 'ERRORE:', err
    return

if __name__ == '__main__':

    with sqlite3.connect(db_filename, isolation_level=isolation_level) as conn:
        t = threading.Thread(name='Reader 1', target=reader, args=(conn,))
        t.start()
        t.join()

I tentativi di condividere una connessione fra thread generano una eccezione.

$ python sqlite3_threading.py
Partenza del thread
ERRORE: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id -1216571712 and this is thread id -1219478672