Un tutorial SQLite con Python

Introduzione

Questo tutorial riguarderà l’utilizzo di SQLite in combinazione con l’interfaccia sqlite3 di Python. SQLite è un database relazionale a file singolo in bundle con la maggior parte delle installazioni Python standard. SQLite è spesso la tecnologia di scelta per le piccole applicazioni, in particolare quelli di sistemi embedded e dispositivi come telefoni e tablet, elettrodomestici intelligenti, e strumenti. Tuttavia, non è raro sentire che viene utilizzato per le piccole e medie applicazioni web e desktop.,

Creare un database e creare una connessione

Creare un nuovo database SQLite è semplice come creare una connessione utilizzando il modulo sqlite3 nella libreria standard Python. Per stabilire una connessione, tutto ciò che devi fare è passare un percorso di file al metodo connect(...) nel modulo sqlite3, e se il database rappresentato dal file non esiste, ne verrà creato uno in quel percorso.,

import sqlite3con = sqlite3.connect('/path/to/file/db.sqlite3')

Scoprirai che nella programmazione quotidiana del database creerai costantemente connessioni al tuo database, quindi è una buona idea avvolgere questa semplice istruzione di connessione in una funzione generalizzata riutilizzabile.

Creazione di tabelle

Per creare tabelle di database è necessario avere un’idea della struttura dei dati che si desidera memorizzare. Ci sono molte considerazioni di progettazione che vanno a definire le tabelle di un database relazionale, di cui sono stati scritti interi libri., Non entrerò nei dettagli di questa pratica e lascerò invece al lettore di indagare ulteriormente.

Tuttavia, per aiutare nella nostra discussione sulla programmazione del database SQLite con Python, lavorerò sulla premessa che è necessario creare un database per un negozio di libri fittizio che abbia i seguenti dati già raccolti sulle vendite di libri.,

customer	date	product	price
Alan Turing	2/22/1944	Introduction to Combinatorics	7.99
Donald Knuth	7/3/1967	A Guide to Writing Short Stories	17.99
Donald Knuth	7/3/1967	Data Structures and Algorithms	11.99
Edgar Codd	1/12/1969	Advanced Set Theory	16.,99

Ispezionando questi dati è evidente che contengono informazioni su clienti, prodotti e ordini. Un modello comune nella progettazione di database per i sistemi transazionali di questo tipo consiste nel suddividere gli ordini in due tabelle aggiuntive, ordini e elementi pubblicitari (a volte indicati come dettagli dell’ordine) per ottenere una maggiore normalizzazione.

In un interprete Python, nella stessa directory dei db_utils.,modulo py definito in precedenza, immettere l’SQL per la creazione delle tabelle clienti e prodotti segue:

Il codice precedente crea un oggetto di connessione quindi lo utilizza per istanziare un oggetto cursore. L’oggetto cursore viene utilizzato per eseguire istruzioni SQL sul database SQLite.

Con il cursore creato ho quindi scritto l’SQL per creare la tabella dei clienti, dandogli una chiave primaria insieme a un campo di testo nome e cognome e assegnandolo a una variabile chiamatacustomers_sql., Quindi chiamo il metodoexecute(...) dell’oggetto cursore passandogli la variabilecustomers_sql. Quindi creo una tabella prodotti in modo simile.

È possibile interrogare la tabella sqlite_master, una tabella di metadati SQLite integrata, per verificare che i comandi precedenti abbiano avuto successo.

Per vedere tutte le tabelle nel database attualmente connesso interrogare la colonna name della tabella sqlite_master dove type è uguale a “table”.,

Per dare un’occhiata allo schema delle tabelle interrogare la colonna sql della stessa tabella dove type è ancora “table” e name è uguale a “customers” e / o “products”.

La tabella successiva da definire sarà la tabella ordini che associa i clienti agli ordini tramite una chiave esterna e la data del loro acquisto. Poiché SQLite non supporta un tipo di dati data/ora effettivo (o una classe di dati per essere coerente con il vernacolo SQLite), tutte le date saranno rappresentate come valori di testo.,

La tabella finale da definire sarà la tabella delle voci che fornisce una contabilità dettagliata dei prodotti in ciascun ordine.

Caricamento dei dati

In questa sezione dimostrerò come INSERIRE i nostri dati di esempio nelle tabelle appena create. Un punto di partenza naturale sarebbe quello di popolare prima la tabella dei prodotti perché senza prodotti non possiamo avere una vendita e quindi non avremmo le chiavi esterne per relazionarsi con le voci e gli ordini., Guardando i dati di esempio vedo che ci sono quattro prodotti:

Il flusso di lavoro per l’esecuzione di istruzioni INSERT è semplicemente:

1. la Connessione al database
2. Creare un oggetto cursore
3. Scrivere un parametri di inserimento istruzione SQL e memorizzare una variabile
4. Chiamare il metodo execute dell’oggetto cursore passando la variabile sql e i valori, come una tupla, per essere inseriti nella tabella

Dato questo quadro generale cerchiamo di scrivere codice più.,

Il codice sopra probabilmente sembra abbastanza ovvio, ma lasciami discutere un po ‘ perché ci sono alcune cose importanti in corso qui. L’istruzione insert segue la sintassi SQL standard ad eccezione del bit?. Il ?‘s sono in realtà segnaposto in ciò che è noto come una “query parametrizzata”.

Le query parametrizzate sono una caratteristica importante di essenzialmente tutte le interfacce di database per i moderni linguaggi di programmazione di alto livello come il modulo sqlite3 in Python., Questo tipo di query serve a migliorare l’efficienza delle query che vengono ripetute più volte. Forse più importante, disinfettano anche gli input che prendono il posto dei segnaposto ? che vengono passati durante la chiamata al metodo execute dell’oggetto cursore per evitare input nefasti che portano all’iniezione SQL. Quello che segue è un fumetto dal popolare xkcd.com blog che descrive i pericoli di SQL injection.

Per popolare le tabelle rimanenti seguiremo uno schema leggermente diverso per cambiare un po ‘ le cose., Flusso di lavoro per ogni ordine, identificato da una combinazione di cliente nome e cognome e la data di acquisto, sarà:

1. Inserire il nuovo cliente nella tabella clienti e recuperare la sua chiave primaria id
2. Creare un ordine di ingresso in base all’id del cliente e la data di acquisto quindi recuperare la sua chiave primaria id
3. Per ogni prodotto in ordine di determinare la sua chiave primaria id e la creazione di una linea voce associando l’ordine e il prodotto

Per rendere le cose più semplici di noi stessi facciamo un rapido sguardo di tutti i nostri prodotti., Per ora non preoccuparti troppo della meccanica dell’istruzione SELECT SQL in quanto dedicheremo una sezione a breve.

Il primo ordine fu effettuato il 22 febbraio 1944 da Alan Turing che acquistò Introduction to Combinatorics per $7.99.

Inizia creando un nuovo record cliente per Mr. Turing, quindi determina il suo ID chiave primaria accedendo al campo lastrowid dell’oggetto cursore.

Ora possiamo creare una voce di ordine, raccogliere il nuovo valore id ordine e associarlo a una voce di voce insieme al prodotto ordinato da Mr. Turing.,

I record rimanenti vengono caricati esattamente allo stesso modo tranne che per l’ordine fatto a Donald Knuth, che riceverà due voci di voce. Tuttavia, la natura ripetitiva di tale attività sta gridando la necessità di avvolgere queste funzionalità in funzioni riutilizzabili. Nel db_utils.py modulo aggiungere il seguente codice:

Awh, ora possiamo lavorare con una certa efficienza!

Dovraiexit() il tuo interprete Python e ricaricarlo per rendere accessibili le tue nuove funzioni nell’interprete.,

Mi sento in dovere di dare un ulteriore consiglio come studente di software artigianale. Quando ti ritrovi a fare più manipolazioni del database (inserti in questo caso) al fine di realizzare ciò che è in realtà un’attività cumulativa (ad esempio, la creazione di un ordine) è meglio avvolgere le attività secondarie (creazione di cliente, ordine, quindi elementi pubblicitari) in una singola transazione di database in modo da poter eseguire il commit in caso di successo o rollback,

Questo sarebbe simile a questo:

Voglio finire questa sezione con una rapida dimostrazione di come AGGIORNARE un record esistente nel database. Aggiorniamo la Guida per scrivere il prezzo dei racconti a 10.99 (in vendita).

>>> update_sql = "UPDATE products SET price = ? WHERE id = ?">>> cur.execute(update_sql, (10.99, 2))

Interrogare il database

Generalmente l’azione più comune eseguita su un database è il recupero di alcuni dei dati memorizzati in esso tramite un’istruzione SELECT. Per questa sezione dimostrerò come utilizzare l’interfaccia sqlite3 per eseguire semplici query di selezione.,

Per eseguire una query multirow di base della tabella customers si passa un’istruzione SELECT al metodo execute(...) dell’oggetto cursore. Successivamente è possibile iterare i risultati della query chiamando il metodo fetchall() dello stesso oggetto cursore.

Diciamo che vorresti invece recuperare solo un record dal database. Puoi farlo scrivendo una query più specifica, ad esempio per l’id di Donald Knuth di 2, e in seguito chiamando fetchone() metodo dell’oggetto cursore.,

>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")>>> result = cur.fetchone()>>> print(result)(2, 'Donald', 'Knuth')

Vedi come la singola riga di ciascun risultato è sotto forma di una tupla? Bene, mentre le tuple sono una struttura dati Pythonic molto utile per alcuni casi d’uso della programmazione, molte persone le trovano un po ‘ ostacolanti quando si tratta del compito di recupero dei dati. Si dà il caso che ci sia un modo per rappresentare i dati in un modo che è forse più flessibile per alcuni. Tutto quello che devi fare è impostare il metodo row_factory dell’oggetto connection su qualcosa di più adatto come sqlite3.Row., Questo ti darà la possibilità di accedere ai singoli elementi di una riga in base alla posizione o al valore della parola chiave.

Conclusione

In questo articolo ho dato una breve dimostrazione di ciò che ritengo siano le caratteristiche e le funzionalità più importanti dell’interfaccia Python sqlite3 al database SQLite a file singolo leggero che viene fornito pre-in bundle con la maggior parte delle installazioni Python., Ho anche cercato di dare alcuni consigli sulle migliori pratiche quando si tratta di programmazione di database, ma avverto il nuovo arrivato che la complessità della programmazione di database è generalmente una delle più inclini a buchi di sicurezza a livello aziendale e ulteriori conoscenze sono necessarie prima di tale impresa.