Come mostrare le tabelle in SQLite

Introduzione

SQLite è un motore di database molto diffuso, noto per la sua semplicità ed efficienza nella gestione dei database relazionali. È un motore di database SQL embedded, il che significa che spesso viene usato direttamente all'interno delle applicazioni, offrendo una soluzione leggera senza bisogno di un processo server separato. In questo tutorial vedremo come visualizzare l'elenco delle tabelle di un database SQLite, un'operazione comune quando prendi confidenza con la struttura di un database esistente o quando fai debug.

Prerequisiti

Ti serve SQLite installato e accessibile. Puoi interagire con i database SQLite in vari modi, ad esempio dalla riga di comando, da uno script Python o con strumenti GUI come Beekeeper Studio. In questa guida useremo lo strumento a riga di comando di SQLite, perché è disponibile ovunque.

Accedere al database SQLite

Per iniziare, apri il terminale o il prompt dei comandi e accedi al tuo database SQLite con lo strumento a riga di comando di SQLite. Il comando generale per iniziare a interagire con il database è:

sqlite3 your_database_file.db

Sostituisci ‘your_database_file.db’ con il percorso del tuo file di database SQLite.

Elencare le tabelle con il comando .tables

SQLite offre il comando .tables come metodo semplice per elencare tutte le tabelle disponibili nel database attivo.

Dalla shell a riga di comando di SQLite, digita:

.tables

Eseguendo questo comando, SQLite restituirà l'elenco dei nomi di tutte le tabelle del database corrente.

Output previsto: (usando il database di esempio ‘sakila.db’)

actor                   experiments             payment
address                 film                    rental
category                film_actor              sales_by_film_category
city                    film_category           sales_by_store
country                 film_list               staff
customer                film_text               staff_list
customer_list           inventory               store
events                  language

Come funziona il comando .tables?

SQLite chiede al database di restituire i nomi di tutte le tabelle e le viste nel database principale e in quelli collegati. Per farlo, dietro le quinte esegue un comando SELECT sulla tabella sqlite_master.

Interrogare la tabella sqlite_master

Per avere più controllo, puoi interrogare direttamente la tabella ‘sqlite_master’, che contiene i metadati del database, comprese le definizioni delle tabelle.

Ad esempio:

SELECT name FROM sqlite_master WHERE type='table';

Questo comando SQL seleziona tutti i record della colonna name della tabella sqlite_master in cui type è ‘table’.

Output previsto: (usando il database di esempio ‘sakila.db’)

---------------
actor
sqlite_sequence
country
city
address
language
category
customer
film
film_actor
film_category
film_text
inventory
staff
store
payment
rental
experiments
events

Mostrare lo schema di una tabella con il comando .schema

Per esaminare la struttura o ‘schema’ della tua tabella (cioè le colonne e i rispettivi tipi di dato), SQLite mette a disposizione il comando .schema:

.schema your_table_name

Output previsto: (usando la tabella ‘actor’ del database di esempio ‘sakila.db’)

CREATE TABLE actor (
  actor_id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
  , "email_address" VARCHAR(255) NULL);
CREATE INDEX idx_actor_last_name ON actor(last_name)
;
CREATE TRIGGER actor_trigger_ai AFTER INSERT ON actor
 BEGIN
  UPDATE actor SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
 END;
CREATE TRIGGER actor_trigger_au AFTER UPDATE ON actor
 BEGIN
  UPDATE actor SET last_update = DATETIME('NOW')  WHERE rowid = new.rowid;
 END;

Sostituisci ‘your_table_name’ con il nome della tua tabella.

Questo comando restituirà lo statement CREATE TABLE usato per creare la tabella nel tuo database.

Usare uno statement SQL SELECT

In alternativa, puoi ottenere lo schema con uno statement SELECT sulla tabella sqlite_master:

SELECT sql FROM sqlite_master WHERE name = 'your_table_name';

Questo comando restituirà lo stesso statement CREATE TABLE del comando .schema, ma senza gli statement ‘INDEX’ e ‘TRIGGER’.

Usare il comando PRAGMA per ottenere informazioni sulla tabella

Un altro approccio in SQLite è usare il comando PRAGMA, che fornisce diverse caratteristiche del database SQLite. Un comando PRAGMA utile è ‘table_info’, con cui puoi ottenere informazioni sulle colonne di una tabella.

Anche se ‘table_info’ non elenca tutte le tabelle, puoi usarlo insieme ai comandi che le elencano per capire subito la struttura delle tabelle dopo averne trovato i nomi.

Esempio:

PRAGMA table_info('actor');

Output previsto: (usando la tabella ‘actor’ del database di esempio ‘sakila.db’)

cid  name           type          notnull  dflt_value         pk
---  -------------  ------------  -------  -----------------  --
0    actor_id       INTEGER       1                           1
1    first_name     VARCHAR(45)   1                           0
2    last_name      VARCHAR(45)   1                           0
3    last_update    TIMESTAMP     1        CURRENT_TIMESTAMP  0
4    email_address  VARCHAR(255)  0                           0

Ogni riga rappresenta una colonna della tabella ‘actor’, e i campi mostrano rispettivamente ‘cid’, ‘name’, ‘type’, lo stato ‘not null’, il ‘default value’ e le informazioni sulla ‘primary key’ della colonna.

Conclusione

Elencare le tabelle in SQLite è una competenza fondamentale per gestire e ispezionare un database. Che tu usi il comando .tables per un elenco rapido, interroghi la tabella sqlite_master per un approccio più programmatico o usi gli statement PRAGMA per raccogliere informazioni dettagliate sulla struttura delle tabelle, SQLite offre diversi strumenti per aiutarti a capire e usare al meglio il tuo database.

Capire e scrivere comandi SQL per visualizzare le tabelle SQLite può dare soddisfazione, ma può anche essere lungo e complesso, soprattutto con database molto grandi.

Beekeeper Studio offre un'interfaccia intuitiva per le tabelle del tuo database, rendendo più comodo muoversi tra tantissime tabelle.

Inoltre, per vedere lo schema di qualsiasi tabella basta cliccare sul suo nome e lo schema apparirà in un formato ben strutturato.