Guida per principianti a PostgreSQL

Saper interrogare i sistemi di database relazionali è una competenza fondamentale per un data scientist. SQL, ovvero Structured Query Language, ti permette di farlo in modo molto efficiente. SQL non solo ti consente di porre domande significative ai dati, ma ti permette anche di manipolarli in molti modi diversi. Senza database, praticamente nessuna applicazione reale è possibile. Quindi, la conoscenza dei database e la capacità di gestirli sono parti cruciali della cassetta degli attrezzi di un data scientist.

Curiosità: SQL si pronuncia anche SE-QU-EL. C’è una ragione storica: il nome iniziale di SQL era Simple English Query Language.

In generale, i database relazionali si presentano così:

Le relazioni sono anche chiamate tabelle. Esistono diversi modi per rappresentare i database. Questo è solo uno, ed è il più diffuso.

Questo tutorial introduce le quattro operazioni più comuni eseguite con SQL: Create, Read, Update e Delete. Insieme, queste quattro operazioni sono spesso indicate come CRUD. In qualsiasi applicazione che prevede l’interazione dell’utente, queste quattro operazioni sono sempre presenti.

Utilizzerai PostgreSQL come sistema di gestione di database relazionali. PostgreSQL è molto leggero ed è anche gratuito. In questo tutorial, farai quanto segue:

• Installare e avviare PostgreSQL
• Connetterti a un database PostgreSQL
• Creare, leggere, aggiornare ed eliminare tabelle in quel database
• Eseguire SQL in Jupyter Notebook
• Eseguire SQL in Python

Cominciamo.

Installare e avviare PostgreSQL

PostgreSQL è un RDBMS leggero e open source. È estremamente apprezzato dall’industria. Puoi saperne di più su PostgreSQL dal suo sito ufficiale.

Per poter iniziare a scrivere ed eseguire query in PostgreSQL, devi installarlo sul tuo computer. L’installazione è molto semplice. I due brevi video seguenti mostrano come scaricare e installare PostgreSQL su una macchina Windows 7 a 32 bit:

• Scarica PostgreSQL
• Installa PostgreSQL

Nota: durante l’installazione di PostgreSQL prendi nota della password e del numero di porta che inserisci.

Una volta installato correttamente PostgreSQL sulla tua macchina, apri pgAdmin. pgAdmin è una comoda utility che viene fornita con l’installazione di PostgreSQL e ti permette di svolgere le normali attività sui database tramite una piacevole interfaccia grafica. L’interfaccia di pgAdmin appare così:

Quando apri pgAdmin, vedrai un server chiamato "PostgreSQL 9.4 (localhost:5432)" elencato nell’interfaccia:

Nota: la tua versione potrebbe essere diversa, e quindi anche il numero di porta (5432).

Connettiti al server inserendo la password che hai fornito durante l’installazione. Per riferimento: https://www.loom.com/share/71708f6a99ee483a977991e9e8a25808.

Dopo esserti connesso correttamente al server di database locale, otterrai un’interfaccia simile alla seguente:

Il tuo primo compito sarà creare un database: puoi farlo facendo clic con il tasto destro sulla scheda Databases e selezionando New Database dal menu a discesa. Creiamo un database chiamato DataCamp_Courses. Una volta creato il database, puoi proseguire con le sezioni successive del tutorial.

Operazioni CRUD in PostgreSQL

Creare una tabella in base a una specifica data -

Per poter operare su un database, ti serve una tabella. Quindi andiamo a creare una semplice tabella (chiamata anche relazione) denominata datacamp_courses con la seguente specifica (schema):

La specifica ci fornisce diverse informazioni sulle colonne della tabella:

• La chiave primaria della tabella deve essere course_id (nota che solo questa è in grassetto) e il suo tipo di dato deve essere un intero. Una chiave primaria è un vincolo che impone valori non nulli e univoci nella colonna. Ti permette di identificare in modo univoco una specifica istanza o un insieme di istanze presenti nella tabella.
• Il resto delle informazioni nella specifica ora dovrebbe essere facile da interpretare.

Per creare una tabella, fai clic con il tasto destro sul database appena creato DataCamp_Courses e seleziona CREATE Script dalle opzioni. Dovresti ottenere qualcosa di simile al seguente:

Esegui ora la seguente query:

CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);

Per eseguire la query ti basta selezionarla e fare clic sul pulsante di esecuzione nella barra dei menu:

L’output dovrebbe essere:

La struttura generale di una query di creazione tabella in PostgreSQL è:

CREATE TABLE table_name (
 column_name TYPE column_constraint,
 table_constraint table_constraint
)

Non abbiamo specificato alcun table_constraint durante la creazione della tabella. Possiamo evitarlo per ora. Tutto il resto è abbastanza leggibile, tranne la parola chiave SERIAL. In PostgreSQL, SERIAL consente di creare una colonna auto-incrementante. Per impostazione predefinita, crea valori di tipo intero. SERIAL ci solleva dall’onere di ricordare l’ultimo valore di chiave primaria inserito/aggiornato in una tabella, ed è una buona pratica usare auto-increment per le chiavi primarie. Puoi saperne di più su SERIAL qui.

Inserire alcuni record nella tabella appena creata

In questo passaggio inserirai alcuni record nella tabella. I tuoi record dovrebbero contenere:

• Il nome del corso
• Il nome del docente del corso
• L’argomento del corso

I valori per la colonna course_id saranno gestiti direttamente da PostgreSQL. La struttura generale di una query di inserimento in PostgreSQL è:

INSERT INTO table(column1, column2, …)
VALUES
 (value1, value2, …);

Inseriamo alcuni record:

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');

Nota che non hai specificato esplicitamente le chiavi primarie. Vedrai tra poco l’effetto.

Quando esegui le due query sopra, dovresti ottenere il seguente risultato al termine degli inserimenti:

Query restituita con successo: una riga interessata, tempo di esecuzione 11 ms.

Leggere/visualizzare i dati dalla tabella

Probabilmente è qualcosa che farai spesso nel tuo percorso da data scientist. Per ora, vediamo com’è messa la tabella datacamp_courses.

Questo è generalmente chiamato select query, e la struttura generica di una select è:

SELECT
 column_1,
 column_2,
 ...
FROM
 table_name;

Selezioniamo tutte le colonne dalla tabella datacamp_courses

SELECT * FROM datacamp_courses;

E ottieni:

Nota ora le chiavi primarie. Se vuoi vedere solo i nomi dei corsi, puoi farlo così:

SELECT course_name from datacamp_courses;

E ottieni:

Puoi specificare quante colonne vuoi visualizzare nei risultati, purché esistano nella tabella. Se esegui select course_name, number_particpants from datacamp_courses; otterrai un errore perché la colonna number_particpants esiste nella tabella. Ora vedrai come aggiornare un record specifico nella tabella.

Aggiornare un record nella tabella

La struttura generale di una query di aggiornamento in SQL è la seguente:

UPDATE table
SET column1 = value1,
    column2 = value2 ,...
WHERE
 condition;

Andrai ad aggiornare il record in cui course_instructor = "Chester Ismay" impostando course_name su "Joining Data in SQL". Verificherai poi se il record è stato aggiornato. La query per farlo è:

UPDATE datacamp_courses SET course_name = 'Joining Data in SQL'
WHERE course_instructor = 'Chester Ismay';

Vediamo se la tua query di aggiornamento ha avuto l’effetto desiderato eseguendo una select:

Puoi vedere che la query di aggiornamento ha funzionato esattamente come volevi. Ora vedrai come eliminare un record dalla tabella.

Eliminare un record nella tabella

La struttura generale di una query di eliminazione in SQL è la seguente:

DELETE FROM table
WHERE condition;

Eliminerai il record in cui course_name = "Deep Learning in Python" e poi verificherai se è stato eliminato. Seguendo la struttura, la seguente query dovrebbe riuscirci:

DELETE from datacamp_courses
WHERE course_name = 'Deep Learning in Python';

Ricorda che le parole chiave non sono sensibili al maiuscolo/minuscolo in SQL, ma i dati lo sono. Ecco perché nelle query vedi un mix di maiuscole e minuscole.

Vediamo se il record previsto è stato eliminato dalla tabella oppure no:

E sì, ha effettivamente eliminato il record desiderato.

Le strutture generiche delle query menzionate nel tutorial sono tratte da PostgreSQL Tutorial.

Ora sai come scrivere le query CRUD di base in SQL. Alcuni di voi usano molto i Jupyter Notebook e potrebbero pensare che sarebbe fantastico poter eseguire queste query direttamente da Jupyter Notebook. Nella prossima sezione vedrai come farlo.

SQL + Jupyter Notebook

Per poter eseguire query SQL da Jupyter Notebook, il primo step è installare il pacchetto ipython-sql.

Se non è installato, installalo con:

pip install ipython-sql

Una volta fatto, carica l’estensione sql nel tuo Jupyter Notebook eseguendo:

%load_ext sql

Il passo successivo è connettersi a un database PostgreSQL. Ti connetterai al database che hai creato: DataCamp_Courses.

Per poterti connettere a un database già creato nel tuo sistema, devi istruire Python a rilevarne il dialetto. In parole semplici, devi dire a Python che si tratta di un database PostgreSQL. Per questo ti servirà psycopg2, installabile con:

pip install psycopg2

Una volta installato psycopg connettiti al database usando:

%sql postgresql://postgres:postgres@localhost:5432/DataCamp_Courses

'Connected: postgres@DataCamp_Courses'

Nota l’uso di %sql. È un magic command. Ti permette di eseguire istruzioni SQL da Jupyter Notebook. Quanto segue a %sql è chiamato URL di connessione al database, dove specifichi:

• Dialetto (postgres)
• Username (postgres)
• Password (postgres)
• Indirizzo del server (localhost)
• Numero di porta (5432)
• Nome del database (DaaCamp_Courses)

Ora puoi fare tutto dal tuo Jupyter Notebook come hai fatto nell’interfaccia di pgAdmin. Iniziamo creando la tabella _datacampcourses con lo stesso identico schema.

Ma prima di farlo devi eliminare la tabella, perché SQL non permette di avere due tabelle con lo stesso nome. Puoi eliminare una tabella così:

%sql DROP table datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] 

La tabella _datacampcourses è ora stata eliminata da PostgreSQL e quindi puoi crearne una nuova con questo nome.

%%sql
CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] 

Nota l’uso di %sql e %%sql qui. Per eseguire una singola riga di query, puoi usare %sql, ma se vuoi eseguire più query in un colpo solo, devi usare %%sql.

Inseriamo alcuni record:

%%sql
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.
1 rows affected.





[] 

Visualizza la tabella per assicurarti che gli inserimenti siano andati come previsto:

%%sql
select * from datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. 

Manteniamo il flusso. Come prossimo passo, aggiornerai un record nella tabella:

%sql update datacamp_courses set course_name = 'Joining Data in SQL' where course_instructor = 'Chester Ismay';

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] 

Fai molta attenzione quando lavori con le stringhe in SQL. A differenza dei linguaggi di programmazione tradizionali, i valori stringa devono essere racchiusi tra apici singoli.

Verifichiamo ora se la query di aggiornamento ha avuto l’effetto desiderato:

%%sql
select * from datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. 

Ora eliminiamo un record e verifichiamo:

%%sql
delete from datacamp_courses where course_name = 'Deep Learning in Python';

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] 

%%sql
select * from datacamp_courses;

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. 

A questo punto hai un’idea chiara di come eseguire operazioni CRUD in PostgreSQL e di come puoi farlo tramite Jupyter Notebook. Se conosci Python e sei interessato ad accedere al tuo database dal tuo codice Python, puoi farlo. La prossima sezione è dedicata a questo.

Primi passi con SQLAlchemy e integrazione con i magic command SQL

Per questa sezione ti servirà il pacchetto SQLAlchemy. In genere è incluso nella distribuzione Anaconda. Puoi anche installarlo con pip. Una volta installato, puoi importarlo così:

import sqlalchemy

Per poter interagire con i tuoi database usando SQLAlchemy, devi creare un engine per l’RDBMS in cui sono memorizzati i tuoi database. Nel tuo caso, è PostgreSQL. SQLAlchemy ti permette di creare un engine dell’RDBMS con una sola chiamata a create_engine(), e il metodo accetta un URL di connessione al database che hai già visto.

from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:postgres@localhost:5432/DataCamp_Courses')
print(engine.table_names()) # Ti permette di vedere i nomi delle tabelle presenti nel database

['datacamp_courses']

Puoi vedere la tabella chiamata _datacampcourses, il che conferma ulteriormente che hai creato correttamente l’engine. Eseguiamo una semplice select per vedere i record della tabella _datacampcourses e memorizzarli in un oggetto DataFrame di pandas.

Utilizzerai il metodo read_sql() (fornito da pandas), che accetta una stringa di query SQL e un engine.

import pandas as pd

df = pd.read_sql('select * from datacamp_courses', engine)
df.head()

Puoi anche abbinare il magic command %sql all’interno del tuo codice Python.

df_new = %sql select * from datacamp_courses
df_new.DataFrame().head()

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. 

Hai fatto molta strada!

Congratulazioni, datti una pacca sulla spalla! In questo tutorial non solo hai iniziato a lavorare con PostgreSQL, ma hai anche visto come eseguire query SQL in diversi modi. Come prossimo passo, dovresti guardare questo webinar di David Robinson (Chief Data Scientist di DataCamp), in cui mostra come usa SQL nelle sue attività quotidiane. Dai un’occhiata al nostro tutorial Come installare PostgreSQL su Windows e Mac OS X. Se vuoi migliorare le tue competenze in SQL, le seguenti sono ottime risorse da cui iniziare:

• Introduzione a SQL per Data Science
• SQL di Kaggle Learn
• Joining Data in SQL