Il modulo sql implementa una interfaccia conforme Python DB-API 2.0 a SQLite , un database relazionale con architettura in-process. SQLite è progettato per essere integrato nelle applicazioni, invece di usare un programma server di database separato tipo MySQL, PostgreSQL ed Oracle. E' veloce, rigorosamente testato e flessibile, rendendolo adatto per prototipazione e distribuzione in produzione per alcune applicazioni.

Creare un Database

Un database SQLite viene conservato in un singolo file nel file system. La libreria gestisce l'accesso al file, incluso il lock 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 del database prima di aprirlo con connect() in modo da sapere quando creare lo schema per i nuovi database.

# sqlite3_createdb.py

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: cannot access 'todo.db': No such file or directory

$ python3 sqlite3_createdb.py
Occorre creare lo schema

$ python3 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. I dettagli dello schema del database sono presentati nelle tabelle seguenti:

Tabella progetto:

Tabella compito:

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

-- todo_schema.sql

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

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

-- I compiti sono i passi che possono essere intrapresi 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() di Connection può essere usato per eseguire le istruzioni DDL per creare lo schema.

# sqlite3_create_schema.py

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 3', 'Il modulo Python della Settimana', '2018-08-16')
        """)

        conn.execute("""
        insert into compito (dettagli, stato, scadenza, progetto)
        values ('tradurre select', 'fatto', '2018-05-21', 'pymotw-it 3')
        """)

        conn.execute("""
        insert into compito (dettagli, stato, scadenza, progetto)
        values ('tradurre random', 'in attesa', '2018-06-02', 'pymotw-it 3')
        """)

        conn.execute("""
        insert into compito (dettagli, stato, scadenza, progetto)
        values ('tradurre sqlite3', 'attivo', '2018-10-31', 'pymotw-it 3')
        """)
    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 e i compiti relativi. Il programma da riga di comando sqlite3 può essere usato per esaminare il contenuto del database.

$ python3 sqlite3_create_schema.py

Creazione dello schema
Inserimento dei dati di partenza

$ sqlite3 todo.db 'select * from compito'

1|1|tradurre select|fatto|2018-05-21||pymotw-it 3
2|1|tradurre random|in attesa|2018-06-02||pymotw-it 3
3|1|tradurre sqlite3|attivo|2018-10-31||pymotw-it 3

Recuperare i Dati

Per recuperare i valori salvati nella tabella compito da un programma Python, si crea un cursore Cursor da una connessione di 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.

# sqlite3_select_tasks.py

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 3'
    """)

    for row in cursor.fetchall():
        task_id, priority, details, status, deadline = row
        print('{:2d} [{:d}] {:<25} [{:<10}] ({})'.format(
            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.

$ python3 sqlite3_select_tasks.py

 1 [1] tradurre select           [fatto     ] (2018-05-21)
 2 [1] tradurre random           [in attesa ] (2018-06-02)
 3 [1] tradurre sqlite3          [attivo    ] (2018-10-31)

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

# sqlite3_select_variations.py

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 3'
    """)
    name, description, deadline = cursor.fetchone()

    print('Dettagli del progetto per {} ({})\n scadenza {}'.format(
        description, name, deadline))

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

    print('\nProssimi 5 compiti:')

    for row in cursor.fetchmany(5):
        task_id, priority, details, status, deadline = row
        print('{:2d} [{:d}] {:<25} [{:<10}] ({})'.format(
            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.

$ python3 sqlite3_select_variations.py

Dettagli del progetto per Il modulo Python della Settimana (pymotw-it 3)
 scadenza 2018-08-16

Prossimi 5 compiti:
 1 [1] tradurre select           [fatto     ] (2018-05-21)
 2 [1] tradurre random           [in attesa ] (2018-06-02)
 3 [1] tradurre sqlite3          [attivo    ] (2018-10-31)

Interrogare i Metadati

Le specifiche DB-API 2.0 dicono che dopo la chiamata di execute(), il cursore Cursor dovrebbe impostare il suo 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 e un flag che dice se i valori null debbano essere accettati.

# sqlite3_cursor_description.py

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 3'
    """)

    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.

$ python3 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 e accedere i valori di colonna usando il nome delle colonne stesse. Visto che il numero e l'ordine degli elementi della tupla possono cambiare nel corso del tempo mano a mano che l'interrogazione viene modificata, 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 nell'insieme del risultato dell'interrogazione. sqlite3 comprende anche una classe Row che è intesa per l'uso come "produttrice" di righe. I valori delle colonne possono essere indirizzati tramite istanze di Row usando l'indice o il nome di colonna.

# sqlite3_row_factory.py

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 3'
    """)
    name, description, deadline = cursor.fetchone()

    print('Dettagli del progetto {} ({})\n scadenza {}'.format(
        description, name, deadline))

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

    print('\nProssimi 5 compiti:')

    for row in cursor.fetchmany(5):
        print('{:2d} [{:d}] {:<25} [{:<10}] ({})'.format(
            row['id'], row['priorita'], row['dettagli'],
            row['stato'], row['scadenza'],
        ))

Questa versione dell'esempio sqlite3_select_variations.py è stato riscritta usando istanze di Row in luogo delle tuple. La riga della tabella 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.

$ python3 sqlite3_row_factory.py

Dettagli del progetto Il modulo Python della Settimana (pymotw-it 3)
 scadenza 2018-08-16

Prossimi 5 compiti:
 1 [1] tradurre select           [fatto     ] (2018-05-21)
 2 [1] tradurre random           [in attesa ] (2018-06-02)
 3 [1] tradurre sqlite3          [attivo    ] (2018-10-31)

Usare Variabili con le Interrogazioni

L'uso di interrogazioni definite come stringhe letterali incorporate in un 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à è è la costruzione dell'istruzione SQL con l'interrogazione desiderata combinando i valori in Python. Tuttavia, 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 attacco SQL-injection, che consente di eseguire istruzioni SQL arbitrarie nel database.

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

# sqlite3_argument_positional.py

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}] {:<25} [{:<10}] ({})'.format(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.

$ python3 sqlite3_argument_positional.py "pymotw-it 3"

 1 [1] tradurre select           [fatto     ] (2018-05-21)
 2 [1] tradurre random           [in attesa ] (2018-06-02)
 3 [1] tradurre sqlite3          [attivo    ] (2018-10-31)

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.

# sqlite3_argument_named.py

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}] {:<25} [{:<10}] ({})'.format(
            task_id, priority, details, status, deadline))

Non occorre preoccuparsi di gestire, per i parametri sia posizionali che nominali, sequenze di escape o accorgimenti riguardo apici all'interno delle stringhe in quanto essi ricevono uno speciale trattamento da parte dell'elaboratore dell'interrogazione.

$ python3 sqlite3_argument_named.py "pymotw-it 3"

 1 [1] tradurre select           [fatto     ] (2018-05-21)
 2 [1] tradurre random           [in attesa ] (2018-06-02)
 3 [1] tradurre sqlite3          [attivo    ] (2018-10-31)

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

# sqlite3_argument_update.py

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.

$ python3 sqlite3_argument_update.py 2 fatto

$ python3 sqlite3_argument_named.py "pymotw-it 3"

 1 [1] tradurre select           [fatto     ] (2018-05-21)
 2 [1] tradurre random           [fatto     ] (2018-06-02)
 3 [1] tradurre sqlite3          [attivo    ] (2018-10-31)

Caricamento Massivo

Per applicare la stessa istruzione SQL a un gran numero di dati si usi 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 a 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.

# sqlite3_load_csv.py

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
2018-10-02,pymotw-it 3,2,"finire revisione markup"
2018-08-03,pymotw-it 3,2,"revisione capitolo shlex"
2018-09-21,pymotw-it 3,1,"pulizia cartelle"

L'esecuzione del programma produce:

$ python3 sqlite3_load_csv.py tasks.csv

$ python3 sqlite3_argument_named.py "pymotw-it 3"

 1 [1] tradurre select           [fatto     ] (2018-05-21)
 2 [1] tradurre random           [fatto     ] (2018-06-02)
 5 [2] revisione capitolo shlex  [attivo    ] (2018-08-03)
 6 [1] pulizia cartelle          [attivo    ] (2018-09-21)
 4 [2] finire revisione markup   [attivo    ] (2018-10-02)
 3 [1] tradurre sqlite3          [attivo    ] (2018-10-31)

Definire Nuovi Tipi di Colonna

SQLite ha supporto nativo per colonne di interi, valori a virgola mobile, e di testo. Dati di questi tipi sono convertiti automaticamente da sqlite3 dalla rappresentazione di Python verso 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 str, a meno che non sia stato modificato il valore text_factory in Connection.

Sebbene SQLite supporti solo pochi tipi di dato internamente, sqlite3 comprende dei servizi per definire tipi personalizzati per consentire a una 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 usi PARSE_DECLTYPES se la colonna è stata dichiarata usando il tipo desiderato quando la tabella è stata definita.

# sqlite3_date_types.py

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('  {:<8}  {!r:<26} {}'.format(
            col, row[col], 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.

$ python3 sqlite3_date_types.py

Senza identificazione del tipo:
  id        1                          <class 'int'>
  dettagli  'tradurre select'          <class 'str'>
  scadenza  '2018-05-21'               <class 'str'>

Con identificazione del tipo:
  id        1                          <class 'int'>
  dettagli  'tradurre select'          <class 'str'>
  scadenza  datetime.date(2018, 5, 21) <class 'datetime.date'>

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

# sqlite3_custom_type.py

import pickle
import sqlite3

db_filename = 'todo.db'


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


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


class MyObj:

    def __init__(self, arg):
        self.arg = arg

    def __str__(self):
        return 'MyObj({!r})'.format(self.arg)


# Registra le funzioni 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 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)
        print('    con tipo', 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.

$ python3 sqlite3_custom_type.py

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

adapter_func(MyObj(42))

converter_func(b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x03\x00\x00\x00argq\x03X\x1b\x00\x00\x00questo il valore da salvareq\x04sb.')

converter_func(b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x03\x00\x00\x00argq\x03K*sb.')

Recuperato 1 MyObj('questo il valore da salvare')
    con tipo <class '__main__.MyObj'>

Recuperato 2 MyObj(42)
    con tipo <class '__main__.MyObj'>

Determinare Tipi per 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]".

# sqlite3_custom_type_column.py

import pickle
import sqlite3

db_filename = 'todo.db'


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


def converter_func(data):
    """Converte da valore conservato a rappresentazione in-memoria.
    """
    print('converter_func({!r})\n'.format(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 sequenza direttamente a executemany().
to_save = [
    (MyObj("questo e' il valore da salvare"),),
    (MyObj(42),),
]

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

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

    # Interroga il database richiedendo gli oggetti appena salvati
    # usando uno specificatore di tipo per convertire il testo in oggetti
    cursor.execute('select id, data as "pickle [MyObj]" from obj2')
    for obj_id, obj in cursor.fetchall():
        print('Recuperato', obj_id, obj)
        print('     con tipo', type(obj))
        print()

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

$ python3 sqlite3_custom_type_column.py

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

adapter_func(MyObj(42))

converter_func(b"\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x03\x00\x00\x00argq\x03X\x1e\x00\x00\x00questo e' il valore da salvareq\x04sb.")

converter_func(b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x03\x00\x00\x00argq\x03K*sb.')

converter_func(b"\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x03\x00\x00\x00argq\x03X\x1e\x00\x00\x00questo e' il valore da salvareq\x04sb.")

converter_func(b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x03\x00\x00\x00argq\x03K*sb.')

Recuperato 1 MyObj("questo e' il valore da salvare")
     con tipo <class '__main__.MyObj'>

Recuperato 2 MyObj(42)
     con tipo <class '__main__.MyObj'>

Recuperato 3 MyObj("questo e' il valore da salvare")
     con tipo <class '__main__.MyObj'>

Recuperato 4 MyObj(42)
     con tipo <class '__main__.MyObj'>

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 quando i risultati sono confermati e definitivamente scritti sul database.

Preservare le Modifiche

Le modifiche al database, sia tramite istruzioni di insert che di update devono essere salvate chiamando esplicitamente commit(). Questo requisito fornisce a una applicazione la possibilità di effettuare parecchie modifiche collegate, in modo che siano conservate atomicamente invece che in modo incrementale, evitando situazioni nelle quali aggiornamenti parziali possano essere visti da client diversi in connessione simultanea 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.

# sqlite3_transaction_commit.py

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, i dati modificati vengono rilevati. D'altro canto conn2 non li vede. Dopo la chiamata di commit(), la nuova connessione conn3 vede la riga inserita.

$ python3 sqlite3_transaction_commit.py

Prima delle modifiche:
   pymotw-it 3

Dopo le modifiche in conn1:
   pymotw-it 3
   virtualenvwrapper

Prima di commit:
   pymotw-it 3

Dopo commit:
   pymotw-it 3
   virtualenvwrapper

Scartare le Modifiche

Le modifiche non ancora confermate con commit() 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.

# sqlite3_transaction_rollback.py

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 as 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_projects(conn)

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

$ python3 sqlite3_transaction_rollback.py

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

Dopo la chiamata di rollback:
   pymotw-it 3
   virtualenvwrapper

Livelli di Isolamento

sqlite3 supporta tre modalità di blocco, chiamate livelli di isolamento che controllano la tecnica usata per prevenire modifiche incompatibili tra le connessioni. Il livello di isolamento viene impostato passando una stringa all'argomento 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. Le altre tentano di leggere tutte le righe della tabella compito.

# sqlite3_isolation_levels.py

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():
    with sqlite3.connect(
            db_filename,
            isolation_level=isolation_level) as conn:
        cursor = conn.cursor()
        cursor.execute('update compito set priorita = priorita + 1')
        logging.debug('in attesa per sincronizzare')
        ready.wait()  # sincronizza i thread
        logging.debug('IN PAUSA')
        time.sleep(1)
        conn.commit()
        logging.debug('MODIFICHE CONFERMATE')


def reader():
    with sqlite3.connect(
            db_filename, isolation_level=isolation_level) as conn:
        cursor = conn.cursor()
        logging.debug('in attesa per sincronizzare')
        ready.wait()  # sincronizza i thread
        logging.debug('in attesa')
        cursor.execute('select * from compito')
        logging.debug('SELECT ESEGUITO')
        results = cursor.fetchall()
        logging.debug('risultati recuperati')


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 un oggetto 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.

$ python3 sqlite3_isolation_levels.py DEFERRED

2021-06-03 09:16:08,827 (Lettore 1 ) in attesa per sincronizzare
2021-06-03 09:16:08,827 (Lettore 2 ) in attesa per sincronizzare
2021-06-03 09:16:08,828 (Scrittore 1) in attesa per sincronizzare
2021-06-03 09:16:09,829 (MainThread) impostazioni pronte
2021-06-03 09:16:09,829 (Lettore 2 ) in attesa
2021-06-03 09:16:09,829 (Lettore 1 ) in attesa
2021-06-03 09:16:09,830 (Scrittore 1) IN PAUSA
2021-06-03 09:16:09,830 (Lettore 2 ) SELECT ESEGUITO
2021-06-03 09:16:09,830 (Lettore 2 ) risultati recuperati
2021-06-03 09:16:09,830 (Lettore 1 ) SELECT ESEGUITO
2021-06-03 09:16:09,831 (Lettore 1 ) risultati recuperati
2021-06-03 09:16:10,896 (Scrittore 1) MODIFICHE CONFERMATE
2021-06-03 09:16:10,960 (Scrittore 2) in attesa per sincronizzare
2021-06-03 09:16:10,960 (Scrittore 2) IN PAUSA
2021-06-03 09:16:12,020 (Scrittore 2) MODIFICHE CONFERMATE

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' adatta 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.

$ python3 sqlite3_isolation_levels.py IMMEDIATE

2021-06-03 09:16:12,066 (Lettore 1 ) in attesa per sincronizzare
2021-06-03 09:16:12,067 (Lettore 2 ) in attesa per sincronizzare
2021-06-03 09:16:12,067 (Scrittore 1) in attesa per sincronizzare
2021-06-03 09:16:13,068 (MainThread) impostazioni pronte
2021-06-03 09:16:13,068 (Lettore 1 ) in attesa
2021-06-03 09:16:13,069 (Lettore 2 ) in attesa
2021-06-03 09:16:13,069 (Scrittore 1) IN PAUSA
2021-06-03 09:16:13,069 (Lettore 1 ) SELECT ESEGUITO
2021-06-03 09:16:13,069 (Lettore 2 ) SELECT ESEGUITO
2021-06-03 09:16:13,069 (Lettore 2 ) risultati recuperati
2021-06-03 09:16:13,069 (Lettore 1 ) risultati recuperati
2021-06-03 09:16:14,126 (Scrittore 1) MODIFICHE CONFERMATE
2021-06-03 09:16:14,199 (Scrittore 2) in attesa per sincronizzare
2021-06-03 09:16:14,199 (Scrittore 2) IN PAUSA
2021-06-03 09:16:15,250 (Scrittore 2) MODIFICHE CONFERMATE

Isolamento Esclusivo

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

$ python3 sqlite3_isolation_levels.py EXCLUSIVE

2021-06-03 09:16:15,296 (Lettore 1 ) in attesa per sincronizzare
2021-06-03 09:16:15,296 (Lettore 2 ) in attesa per sincronizzare
2021-06-03 09:16:15,298 (Scrittore 1) in attesa per sincronizzare
2021-06-03 09:16:16,297 (MainThread) impostazioni pronte
2021-06-03 09:16:16,297 (Lettore 2 ) in attesa
2021-06-03 09:16:16,298 (Scrittore 1) IN PAUSA
2021-06-03 09:16:16,298 (Lettore 1 ) in attesa
2021-06-03 09:16:17,356 (Scrittore 1) MODIFICHE CONFERMATE
2021-06-03 09:16:17,528 (Scrittore 2) in attesa per sincronizzare
2021-06-03 09:16:17,528 (Scrittore 2) IN PAUSA
2021-06-03 09:16:18,588 (Scrittore 2) MODIFICHE CONFERMATE
2021-06-03 09:16:18,657 (Lettore 2 ) SELECT ESEGUITO
2021-06-03 09:16:18,658 (Lettore 1 ) SELECT ESEGUITO
2021-06-03 09:16:18,658 (Lettore 1 ) risultati recuperati
2021-06-03 09:16:18,658 (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.

In sqlite3_autocommit.py la chiamata esplicita a commit() è stata rimossa e il livello di isolamento è stato impostato a None, il resto del codice è uguale a sqlite3_isolation_levels.py. Il risultato è diverso, tuttavia, visto che entrambi i thread di scrittura finiscono il proprio lavoro prima che qualunque thread di lettura inizi l'interrogazione.

# sqlite3_autocommit.py

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  # modalità autocommit

def writer():
    with sqlite3.connect(
            db_filename, isolation_level=isolation_level) as conn:
        cursor = conn.cursor()
        cursor.execute('update compito set priorita = priorita + 1')
        logging.debug('in attesa per sincronizzare')
        ready.wait()  # sincronizza i thread
        logging.debug('IN PAUSA')
        time.sleep(1)
    return


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


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.

$ python3 sqlite3_autocommit.py

2021-06-03 09:16:18,714 (Lettore 1 ) in attesa per sincronizzare
2021-06-03 09:16:18,715 (Lettore 2 ) in attesa per sincronizzare
2021-06-03 09:16:18,771 (Scrittore 1) in attesa per sincronizzare
2021-06-03 09:16:18,838 (Scrittore 2) in attesa per sincronizzare
2021-06-03 09:16:19,716 (MainThread) impostazioni pronte
2021-06-03 09:16:19,716 (Scrittore 1) IN PAUSA
2021-06-03 09:16:19,717 (Scrittore 2) IN PAUSA
2021-06-03 09:16:19,717 (Lettore 2 ) in attesa
2021-06-03 09:16:19,717 (Lettore 1 ) in attesa
2021-06-03 09:16:19,717 (Lettore 2 ) SELECT ESEGUITO
2021-06-03 09:16:19,718 (Lettore 2 ) risultati recuperati
2021-06-03 09:16:19,718 (Lettore 1 ) SELECT ESEGUITO
2021-06-03 09:16:19,718 (Lettore 1 ) risultati recuperati

Database In-Memoria

SQLite supporta la gestione di un intero database nella RAM, invece che appoggiarsi a un file su disco. I database in-memoria sono utili per test automatici, dove il database non deve essere preservato tra l'esecuzione di un test e l'altro, oppure per fare esperimenti con uno schema o 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. Ogni connessione :memory: crea una istanza di database separata, quindi le modifiche effettuate da un cursore non hanno effetti sulle altre connessioni.

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.

# sqlite3_iterdump.py

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 3', 'Il modulo Python della Settimana', '2018-10-31')
    """)

    data = [
        ('descrivere select', 'fatto', '2018-05-13', 'pymotw-it 3'),
        ('descrivere random', 'in attesa', '2018-06-01', 'pymotw-it 3'),
        ('descrivere sqlite3', 'attivo', '2017-10-17', 'pymotw-it 3'),
        ]

    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.

$ python3 sqlite3_iterdump.py

Creazione dello schema
Inserimento dei dati di partenza
Scaricamento:
BEGIN TRANSACTION;
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','2018-05-13',NULL,'pymotw-it 3');
INSERT INTO "compito" VALUES(2,1,'descrivere random','in attesa','2018-06-01',NULL,'pymotw-it 3');
INSERT INTO "compito" VALUES(3,1,'descrivere sqlite3','attivo','2017-10-17',NULL,'pymotw-it 3');
CREATE TABLE progetto (
    nome        text primary key,
    descrizione text,
    scadenza    date
);
INSERT INTO "progetto" VALUES('pymotw-it 3','Il modulo Python della Settimana','2018-10-31');
DELETE FROM "sqlite_sequence";
INSERT INTO "sqlite_sequence" VALUES('compito',3);
COMMIT;

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 riutilizzare il codice dell'applicazione.

# sqlite3_create_function.py

import codecs
import sqlite3

db_filename = 'todo.db'


def encrypt(s):
    print('Codifica {!r}'.format(s))
    return codecs.encode(s, 'rot-13')


def decrypt(s):
    print('Decodifica {!r}'.format(s))
    return codecs.encode(s, 'rot-13')


with sqlite3.connect(db_filename) as conn:

    conn.create_function('encrypt', 1, encrypt)
    conn.create_function('decrypt', 1, decrypt)
    cursor = conn.cursor()

    # Valori grezzi
    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)

    print('\nDecodifica...')
    query = "update compito set dettagli = decrypt(dettagli)"
    cursor.execute(query)

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 argomenti che riceve la funzione e la funzione Python da esporre.

$ python3 sqlite3_create_function.py

Valori originali:
(1, 'tradurre select')
(2, 'tradurre random')
(3, 'tradurre sqlite3')
(4, 'finire revisione markup')
(5, 'revisione capitolo shlex')
(6, 'pulizia cartelle')

Codifica...
Codifica 'tradurre select'
Codifica 'tradurre random'
Codifica 'tradurre sqlite3'
Codifica 'finire revisione markup'
Codifica 'revisione capitolo shlex'
Codifica 'pulizia cartelle'

Valori codificati grezzi:
(1, 'genqheer fryrpg')
(2, 'genqheer enaqbz')
(3, 'genqheer fdyvgr3')
(4, 'svaver erivfvbar znexhc')
(5, 'erivfvbar pncvgbyb fuyrk')
(6, 'chyvmvn pnegryyr')

Decodifica nell'interrogazione ...
Decodifica 'genqheer fryrpg'
Decodifica 'genqheer enaqbz'
Decodifica 'genqheer fdyvgr3'
Decodifica 'svaver erivfvbar znexhc'
Decodifica 'erivfvbar pncvgbyb fuyrk'
Decodifica 'chyvmvn pnegryyr'
(1, 'tradurre select')
(2, 'tradurre random')
(3, 'tradurre sqlite3')
(4, 'finire revisione markup')
(5, 'revisione capitolo shlex')
(6, 'pulizia cartelle')

Decodifica...
Decodifica 'genqheer fryrpg'
Decodifica 'genqheer enaqbz'
Decodifica 'genqheer fdyvgr3'
Decodifica 'svaver erivfvbar znexhc'
Decodifica 'erivfvbar pncvgbyb fuyrk'
Decodifica 'chyvmvn pnegryyr'

Interrogazioni con Espressioni Regolari

SQLite supporta parecchie speciali funzioni utente che sono associate con la sintassi SQL. Ad esempio una funzione regexp potrebbe essere usata in una query per verificare se il valore stringa di una colonna corrisponde a una espressione regolare usando la sintassi seguente.

SELECT * FROM tabella
WHERE colonna REGEXP '.*modello.*'

Questo esempio associa una funzione con regexp() per verificare i valori usando il modulo Python re.

# sqlite3_regex.py

import re
import sqlite3

db_filename = 'todo.db'


def regexp(pattern, input):
    return bool(re.match(pattern, input))


with sqlite3.connect(db_filename) as conn:
    conn.row_factory = sqlite3.Row
    conn.create_function('regexp', 2, regexp)
    cursor = conn.cursor()

    pattern = '.*[tT]radurre .*'

    cursor.execute(
        """
        select id, priorita, dettagli, stato, scadenza from compito
        where dettagli regexp :pattern
        order by scadenza, priorita
        """,
        {'pattern': pattern},
    )

    for row in cursor.fetchall():
        task_id, priority, details, status, deadline = row
        print('{:2d} [{:d}] {:<25} [{:<10}] ({})'.format(
              task_id, priority, details, status, deadline))

Il risultato è tutti i compiti dove la colonna dettagli corrisponde al modello.

$ python3 sqlite3_regex.py

 1 [9] tradurre select           [fatto     ] (2018-05-21)
 2 [9] tradurre random           [fatto     ] (2018-06-02)
 3 [9] tradurre sqlite3          [attivo    ] (2018-10-31)

Aggregazione Personalizzata

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

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

# sqlite3_create_aggregate.py

import sqlite3
import collections


db_filename = 'todo.db'

class Mode(object):
    def __init__(self):
        self.counter = collections.Counter()

    def step(self, value):
        print('step({!r})'.format(value))
        self.counter[value] += 1

    def finalize(self):
        result, count = self.counter.most_common(1)[0]
        print('finalize() -> {!r} ({} volte)'.format(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 3'
    """)
    row = cursor.fetchone()
    print("La moda di scadenza è:", row[0])

La classe aggregatore viene registrata con il metodo create_aggregate() dell'oggetto Connection. 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.

$ python3 sqlite3_create_aggregate.py

step('2018-05-21')
step('2018-06-02')
step('2018-10-31')
step('2018-10-02')
step('2018-08-03')
step('2018-09-21')
finalize() -> '2018-05-21' (1 volte)
La moda di scadenza è: 2018-05-21

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.

# sqlite3_threading.py

import sqlite3
import sys
import threading
import time

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


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


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.

$ python3 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 140472337532736 and this is thread id 140472327485184.

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 a un qualsivoglia criterio. La funzione di autorizzazione viene chiamata durante l'elaborazione delle istruzioni SQL e riceve cinque argomenti. Il primo è un codice di azione che indica che tipo di operazione debba essere eseguita (lettura, scrittura, cancellazione ecc.). I restanti argomenti dipendono dal codice di azione. Per operazioni SQLITE_READ (lettura) gli argomenti sono il nome della tabella, quello della colonna, il punto nel SQL dove l'accesso deve avvenire (interrogazione principale, trigger ecc.), e None.

# sqlite3_set_authorizer.py

import sqlite3

db_filename = 'todo.db'


def authorizer_func(action, table, column, sql_location, ignore):
    print('\nauthorizer_func({}, {}, {}, {}, {})'.format(
        action, table, column, sql_location, ignore))

    response = sqlite3.SQLITE_OK  # siamo permissivi per default

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

    elif action == sqlite3.SQLITE_READ:
        print('richiesta accesso a colonna {}.{} da {}'.format(
            table, column, sql_location))
        if column == 'dettagli':
            print('  colonna dettagli ignorata')
            response = sqlite3.SQLITE_IGNORE
        elif column == 'priorita':
            print('  negato 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('Utilizzo di SQLITE_IGNORE per nascondere un valore di colonna:')
    cursor = conn.cursor()
    cursor.execute("""
    select id, dettagli from compito where progetto = 'pymotw-it 3'
    """)
    for row in cursor.fetchall():
        print(row['id'], row['dettagli'])

    print('\nUtilizzo di SQLITE_DENY per negare accssso ad una colonna:')
    cursor.execute("""
    select id, priorita from compito where progetto = 'pymotw-it 3'
    """)
    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.

$ python3 sqlite3_set_authorizer.py

Utilizzo di 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 accesso a colonna compito.id da main

authorizer_func(20, compito, dettagli, main, None)
richiesta accesso a colonna compito.dettagli da main
  colonna dettagli ignorata

authorizer_func(20, compito, progetto, main, None)
richiesta accesso a colonna compito.progetto da main
1 None
2 None
3 None
4 None
5 None
6 None

Utilizzo di SQLITE_DENY per negare accssso ad una colonna:

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

authorizer_func(20, compito, id, main, None)
richiesta accesso a colonna compito.id da main

authorizer_func(20, compito, priorita, main, None)
richiesta accesso a colonna compito.priorita da main
  negato accesso alla colonna priorita
Traceback (most recent call last):
  File "sqlite3_set_authorizer.py", line 44, in <module>
    cursor.execute("""
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.