L’autore ha selezionato il fondo di soccorso COVID-19 per ricevere una donazione come parte del programma Write for DOnations.
Introduzione
SQLite è un database SQL autonomo basato su file. SQLite viene fornito in bundle con Python e può essere utilizzato in qualsiasi applicazione Python senza dover installare alcun software aggiuntivo.
In questo tutorial, andremo attraverso il sqlite3 modulo in Python 3., Creeremo una connessione a un database SQLite, aggiungeremo una tabella a quel database, inseriremo i dati in quella tabella e leggeremo e modificheremo i dati in quella tabella.
Per questo tutorial, lavoreremo principalmente con un inventario di pesci che dobbiamo modificare quando i pesci vengono aggiunti o rimossi da un acquario immaginario.
Prerequisiti
Per ottenere il massimo da questo tutorial, si consiglia di avere una certa familiarità con la programmazione in Python e qualche background di base con SQL.,
È possibile rivedere questi tutorial per le informazioni di base necessarie:
- Come Codice in Python3
- Introduzione alle Query in MySQL
Fase 1 — Creazione di una Connessione a un Database SQLite
Quando ti connetti ad un database SQLite, siamo l’accesso ai dati che risieda in un file sul nostro computer. I database SQLite sono motori SQL completamente descritti che possono essere utilizzati per molti scopi. Per ora, prenderemo in considerazione un database che traccia l’inventario dei pesci in un acquario immaginario.,
Possiamo connetterci a un database SQLite usando il modulo Python sqlite3:
import sqlite3connection = sqlite3.connect("aquarium.db")import sqlite3 dà al nostro programma Python l’accesso al modulo sqlite3. La funzionesqlite3.connect() restituisce un oggettoConnection che useremo per interagire con il database SQLite contenuto nel fileaquarium.db. Il fileaquarium.db viene creato automaticamente dasqlite3.connect() seaquarium.db non esiste già sul nostro computer.,
Siamo in grado di verificare che abbiamo creato il nostro connection oggetto l’esecuzione di:
print(connection.total_changes)Se si esegue questo codice Python, vedremo output del tipo:
Output0connection.total_changes è il numero totale di righe di database che sono stati modificati dall’ connection. Poiché non abbiamo ancora eseguito alcun comando SQL, 0 total_changes è corretto.
Se, in qualsiasi momento, scopriamo di voler ricominciare questo tutorial, possiamo eliminare il file aquarium.db dal nostro computer.,
Nota: È anche possibile connettersi a un database SQLite che risiede strettamente in memoria (e non in un file) passando la stringa speciale ":memory:" in sqlite3.connect(). Ad esempio,sqlite3.connect(":memory:"). Un":memory:" database SQLite scomparirà non appena il programma Python esce. Questo potrebbe essere conveniente se si desidera che una sandbox temporanea provi qualcosa in SQLite e non è necessario mantenere alcun dato dopo l’uscita del programma.,
Passo 2 — Aggiunta di dati al database SQLite
Ora che abbiamo collegato alaquarium.db database SQLite, possiamo iniziare l’inserimento e la lettura dei dati da esso.
In un database SQL, i dati vengono memorizzati in tabelle. Le tabelle definiscono un insieme di colonne e contengono 0 o più righe con dati per ciascuna delle colonne definite., una tabella denominata fish che rileva i seguenti dati:
| nome | specie | tank_number |
|---|---|---|
| Sammy | squalo | 1 |
| Jamie | seppie | 7 |
fish tabella traccia un valore per name species e tank_number per ogni pesce in acquario., Two example fish rows are listed: one row for a shark named Sammy, and one row for a cuttlefish named Jamie.
We can create this fish table in SQLite using the connection we made in Step 1:
cursor = connection.cursor()cursor.execute("CREATE TABLE fish (name TEXT, species TEXT, tank_number INTEGER)")connection.cursor() returns a Cursor object. Cursor objects allow us to send SQL statements to a SQLite database using cursor.execute()., "CREATE TABLE fish ..." string è una istruzione SQL che crea una tabella denominata fish con tre colonne descritto in precedenza: name tipo TEXT, specie di tipo TEXT e tank_number tipo INTEGER.,
Ora che abbiamo creato una tabella, possiamo inserire righe di dati:
cursor.execute("INSERT INTO fish VALUES ('Sammy', 'shark', 1)")cursor.execute("INSERT INTO fish VALUES ('Jamie', 'cuttlefish', 7)")Si chiama cursor.execute() due volte: una volta per inserire una riga per lo squalo Sammy serbatoio 1 e una volta per inserire una riga per le seppie Jamie serbatoio 7. "INSERT INTO fish VALUES ..." è un’istruzione SQL che ci consente di aggiungere righe a una tabella.,
Nella sezione successiva, useremo un’istruzione SQL SELECTper ispezionare le righe appena inserite nella nostra tabella fish.
Passo 3 — Lettura dei dati dal database SQLite
Nel passo 2, abbiamo aggiunto due righe a una tabella SQLite denominatafish., Siamo in grado di recuperare le righe utilizzando un SELECT istruzione SQL:
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()print(rows)Se si esegue questo codice, verrà visualizzato un output simile al seguente:
Outputcursor.execute() funzione SELECT dichiarazione di recuperare i valori per il name species e tank_number colonne fish tabella. fetchall() recupera tutti i risultati dell’istruzioneSELECT., Quando print(rows) vediamo un elenco di due tuple. Ogni tupla ha tre voci; una voce per ogni colonna che abbiamo selezionato dalla tabellafish. Le due tuple hanno i dati che abbiamo inserito nel passaggio 2: una tupla perSammy lashark, e una tupla perJamie lacuttlefish.,
Se volessimo recuperare le righe nel fish tabella che corrispondono a una serie specifica di criteri, siamo in grado di utilizzare un WHERE clausola:
Se si esegue questa operazione, si otterrà un output simile al seguente:
OutputCome con l’esempio precedente, cursor.execute(<SQL statement>).fetchall() ci permette di recuperare tutti i risultati di un SELECT istruzione. La clausolaWHERE nell’istruzioneSELECT filtra per le righe in cui il valore diname ètarget_fish_name., Si noti che usiamo ?per sostituire la nostra variabiletarget_fish_namenell’istruzioneSELECT. Ci aspettiamo di abbinare solo una riga, e in effetti vediamo solo la riga per Jamie il cuttlefish restituito.
Attenzione: non utilizzare mai le operazioni di stringa Python per creare dinamicamente una stringa di istruzioni SQL. L’utilizzo di operazioni stringa Python per assemblare una stringa di istruzioni SQL ti rende vulnerabile agli attacchi SQL injection. Gli attacchi SQL injection possono essere utilizzati per rubare, alterare o modificare in altro modo i dati memorizzati nel database., Utilizzare sempre il segnaposto? nelle istruzioni SQL per sostituire dinamicamente i valori dal programma Python. Passare una tupla di valori come secondo argomento a Cursor.execute() per associare i valori all’istruzione SQL. Questo modello di sostituzione è dimostrato qui e in altre parti di questo tutorial pure.
Passo 4 — Modifica dei dati nel database SQLite
Le righe in un database SQLite possono essere modificate utilizzandoUPDATE eDELETE Istruzioni SQL.,
Diciamo, ad esempio, che Sammy lo squalo è stato spostato nel serbatoio numero 2. Possiamo cambiare Sammy riga fish tabella per riflettere questo cambiamento:
un UPDATE statement SQL per modificare il tank_number di Sammy per il suo nuovo valore di 2. La clausolaWHERE nell’istruzioneUPDATE garantisce che cambiamo il valore ditank_number solo se una riga haname = "Sammy".,
Se si esegue il seguente SELECT istruzione, possiamo confermare il nostro aggiornamento è stata effettuata correttamente:
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()print(rows)Se si esegue questa operazione, si otterrà un output simile al seguente:
Outputsi Noti che la riga Sammy ora ha il valore di 2 per il tank_number colonna.
Diciamo che Sammy lo squalo è stato rilasciato in natura e non è più tenuto dall’acquario., Poiché Sammy non vive più nell’acquario, avrebbe senso rimuovere la riga Sammy dalla tabella fish.
Rilascio di un DELETE istruzione SQL per rimuovere una riga:
released_fish_name = "Sammy"cursor.execute( "DELETE FROM fish WHERE name = ?", (released_fish_name,))il problema di un DELETE istruzione SQL per rimuovere la riga Sammy shark. La clausolaWHERE nell’istruzioneDELETE garantisce l’eliminazione di una riga solo se tale riga haname = "Sammy".,
Se si esegue il seguente SELECT istruzione, possiamo confermare la nostra eliminazione è stata fatta correttamente:
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()print(rows)Se si esegue questo codice, verrà visualizzato un output simile al seguente:
Outputsi Noti che la riga Sammy shark è ormai andato, e solo Jamie cuttlefish rimane.,
Passo 5 — Uso con le Istruzioni Per la rimozione Automatica
In questo tutorial abbiamo utilizzato due oggetti principali per interagire con il "aquarium.db" database SQLite: un Connection oggetto denominato connection e un Cursor oggetto denominato cursor.
Allo stesso modo in cui i file Python dovrebbero essere chiusi quando abbiamo finito di lavorare con loro, Connectione Cursor gli oggetti dovrebbero essere chiusi anche quando non sono più necessari.,
Si può usare un with istruzione per aiutarci a chiudere automaticamente Connection e Cursor oggetto:
closing è una funzione comfort forniti da contextlib modulo. Quando un’istruzionewith esce,closing assicura checlose() venga chiamato su qualsiasi oggetto gli venga passato. La funzioneclosing viene utilizzata due volte in questo esempio., Una volta per assicurarsi che l’oggettoConnection restituito dasqlite3.connect() venga chiuso automaticamente e una seconda volta per garantire che l’oggettoCursor restituito daconnection.cursor() venga chiuso automaticamente.,
Se si esegue questo codice, verrà visualizzato un output simile al seguente:
OutputDal "SELECT 1" è un’istruzione SQL che restituisce sempre una singola riga con una sola colonna con un valore di 1 ha senso vedere una singola tupla con 1 come unico valore restituito dal nostro codice.
Conclusione
Il modulo sqlite3 è una parte potente della libreria standard Python; ci consente di lavorare con un database SQL su disco completo senza installare alcun software aggiuntivo.,
In questo tutorial, abbiamo imparato come utilizzare il modulo sqlite3 per connettersi a un database SQLite, aggiungere dati a quel database, nonché leggere e modificare i dati in quel database. Lungo la strada, abbiamo anche imparato a conoscere i rischi di attacchi SQL injection e come utilizzarecontextlib.closing per chiamare automaticamenteclose() su oggetti Python inwith istruzioni.
Da qui possiamo saperne di più sui database SQL in SQLite vs MySQL vs PostgreSQL: un confronto tra i sistemi di gestione di database relazionali.