Gestire database PostgreSQL in Python con psycopg2

Creare una tabella in PostgreSQL

È il momento di creare la tua prima tabella nel database datacamp_courses. Vogliamo creare una tabella con informazioni su alcuni corsi del catalogo di DataCamp. La tabella ha il seguente schema:

La specifica ci fornisce molte informazioni sulle colonne della tabella. La chiave primaria della tabella dovrebbe essere course_id e il suo tipo di dato dovrebbe essere un intero. Una chiave primaria è un vincolo che impone che i valori della colonna non siano nulli e siano univoci. Ti consente di identificare in modo univoco una o più istanze presenti nella tabella.

Le restanti colonne forniscono informazioni sul nome del corso, il nome del docente del corso e l’argomento del corso.

Prima di creare la tabella, è importante spiegare come funziona l’istanza di connessione che hai appena creato. In sostanza, la connessione incapsula una sessione del database e ti consente di eseguire comandi e query SQL, come SELECT, INSERT, CREATE, UPDATE o DELETE, usando il metodo cursor(), e di rendere persistenti le modifiche usando il metodo commit() .

Una volta creata l’istanza del cursore, puoi inviare comandi al database usando il metodo execute() e recuperare dati da una tabella usando fetchone(), fetchmany() o fetchall().

Infine, è importante chiudere il cursore e la connessione al database quando hai terminato le operazioni. In caso contrario, continueranno a occupare risorse lato server. Per farlo, puoi usare il metodo close() .

Qui sotto trovi il codice per creare la tabella datacamp_courses:

# Open a cursor to perform database operations
cur = conn.cursor()
# Execute a command: create datacamp_courses table
cur.execute("""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);
            """)
# Make the changes to the database persistent
conn.commit()
# Close cursor and communication with the database
cur.close()
conn.close()

Questo è un esempio molto basilare di come creare tabelle su PostgreSQL, ma le cose possono diventare molto più complesse. Se vuoi saperne di più su come creare un database PostgreSQL ed esplorarne la struttura, i tipi di dati e come normalizzare i database, dai un’occhiata al nostro corso Creating PostgreSQL Database.

Eseguire query PostgreSQL di base in Python

La tabella datacamp_courses è pronta; ora è il momento di usare SQL per eseguire alcune query di base!

INSERT 

Avrai notato che finora la tabella non ha valori. Per creare record nella tabella datacamp_courses, ci serve il comando INSERT.

cur = conn.cursor()

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to SQL','Izzy Weber','Julia')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Analyzing Survey Data in Python','EbunOluwa Andrew','Python')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to ChatGPT','James Chapman','Theory')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to Statistics in R','Maggie Matsui','R')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Hypothesis Testing in Python','James Chapman','Python')");

conn.commit()
cur.close()
conn.close()

La tabella risultante appare così:

SELECT 

Leggere i dati nei database SQL è probabilmente qualcosa che farai spesso nel tuo percorso di data science. In genere si parla di query SELECT. Per ora, vediamo come se la cava la tabella datacamp_courses.

Useremo l’intramontabile istruzione SELECT * FROM database_name per leggere tutti i dati disponibili nella tabella. Poi useremo il metodo fetchall() per recuperare tutte le righe disponibili. Nota che PostgreSQL crea automaticamente un indice numerico per la colonna course_id.

cur = conn.cursor()
cur.execute('SELECT * FROM datacamp_courses;')
rows = cur.fetchall()
conn.commit()
conn.close()
for row in rows:
    print(row)

(1, 'Introduction to SQL', 'Izzy Weber', 'Julia')
(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Introduction to Statistics in R', 'Maggie Matsui', 'R')
(5, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

UPDATE 

I dati spesso contengono errori. Avrai notato nella sezione precedente che l’argomento associato al corso “Introduction to SQL” è Julia. Dopo aver verificato le informazioni sul corso, abbiamo scoperto l’errore. Dobbiamo cambiarlo in “SQL”. Possiamo farlo con l’istruzione UPDATE, come segue:

cur = conn.cursor()
cur.execute("UPDATE datacamp_courses SET topic = 'SQL' WHERE course_name = 'Introduction to SQL';")
conn.commit()
conn.close()

DELETE 

Infine, potresti voler eliminare uno dei record nella tua tabella. Per esempio, eliminiamo il corso Introduction to Statistics in R:

cur = conn.cursor()
cur.execute("""DELETE from datacamp_courses WHERE course_name = 'Introduction to Statistics in R'""");
conn.commit()
cur.close()

Query PostgreSQL avanzate in Python

Nella sezione precedente abbiamo visto le query SQL più basilari. Ma c’è molto altro da imparare. Vediamo alcune query più avanzate.

ORDER BY

Mettiamo che tu voglia ordinare il database per nome del docente. Puoi usare l’istruzione ORDER BY:

cur = conn.cursor()

cur.execute('SELECT * FROM datacamp_courses ORDER BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(1, 'Introduction to SQL', 'Izzy Weber', 'SQL')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

GROUP BY

Potresti voler eseguire alcune funzioni di aggregazione in diversi gruppi di dati. Ad esempio, potresti essere interessato a calcolare il numero di corsi per i vari docenti. Puoi eseguire questo tipo di operazione con la funzione GROUP BY.

cur = conn.cursor()
cur.execute('SELECT course_instructor, COUNT(*) FROM datacamp_courses GROUP BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

('James Chapman', 2)
('Izzy Weber', 1)
('EbunOluwa Andrew', 1)

JOIN

Finora abbiamo lavorato solo con la tabella datacamp_course. Tuttavia, inizi a sfruttare il pieno potenziale dei database relazionali, come PostgreSQL, quando lavori con più tabelle contemporaneamente.

Lo strumento magico per combinare più tabelle è l’operazione JOIN. Immagina di avere una seconda tabella nel database chiamata programming_languages che contiene informazioni di base sui principali linguaggi di programmazione per la data science, inclusi il nome, la posizione nel TIOBE Index e il numero di corsi sul linguaggio su Datacamp. La tabella appare così:

Immagina di voler unire le due tabelle per ottenere le informazioni sui corsi insieme alla posizione del linguaggio nel TIOBE Index. Useremo un INNER JOIN per ottenere solo le informazioni dei linguaggi di programmazione che compaiono nella tabella datacamp_course.

cur = conn.cursor()
cur.execute("""SELECT course_name, course_instructor, topic, tiobe_ranking
FROM datacamp_courses
INNER JOIN programming_languages
ON datacamp_courses.topic = programming_languages.language_name""")
rows = cur.fetchall()
for row in rows:
    print(row)

('Introduction to SQL', 'Izzy Weber', 'SQL', 8)
('Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python', 1)
('Hypothesis Testing in Python', 'James Chapman', 'Python', 1)

Questo è solo un esempio, ma il potenziale dei join in SQL è infinito, rendendoli un argomento imprescindibile. Il nostro corso, il tutorial e il cheatsheet sui join ti aiuteranno a iniziare.

Prime basi con SQLAlchemy

Psycopg2 è un ottimo strumento per connettersi ai database PostgreSQL. Ma se devi lavorare con database ospitati su diversi sistemi SQL, come Oracle e MySQL? In questi scenari, può essere meglio usare uno strumento che si connetta a diversi tipi di database SQL. A tal fine, SQLAlchemy è un’ottima candidata.

In parole semplici, SQLAlchemy consente agli utenti di connettere i database usando il linguaggio Python, eseguire query SQL tramite programmazione a oggetti e snellire il flusso di lavoro.

Anche se la sintassi differisce rispetto a psycopg2, puoi fare pressoché le stesse cose. Qui sotto trovi un esempio su come creare un engine per connetterti ai nostri dataset datacamp_courses e leggere tutti i dati disponibili nella tabella datacap_course

import sqlalchemy as db


engine = db.create_engine("postgresql://datacamp@localhost:5432/datacamp_courses")
conn = engine.connect() 
output = conn.execute("SELECT * FROM datacamp_courses")
print(output.fetchall())
conn.close()

[(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory'),
 (5, 'Hypothesis Testing in Python', 'James Chapman', 'Python'),
 (2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python'),
 (1, 'Introduction to SQL', 'Izzy Weber', 'SQL')]

SQLAlchemy è un altro ottimo strumento che vale la pena imparare. Per avere un assaggio di come funziona, dai un’occhiata al nostro Tutorial su SQLALchemy.

Usare PostgreSQL in DataCamp DataLab

In DataCamp, lavoriamo sodo per rendere la data science più semplice e accessibile a tutti. È da qui che è nato DataCamp DataLab, un notebook cloud progettato specificamente per la data science. Puoi pensare a DataCamp DataLab come a un mix tra Jupyter Notebook e Google Docs, che permette agli utenti di scrivere codice, analizzare dati individualmente o in gruppo e condividere insight.

Collegare DataLab ai database PostgreSQL, così come ad altri database SQL, è estremamente semplice. DataLab ti permette di connetterti alle fonti dati in modo sicuro. Allo stesso tempo, puoi proseguire senza soluzione di continuità la tua analisi dei dati con Python o R. Ti basta creare un’integrazione, seguendo un processo semplice e senza codice, in cui dovrai inserire le tue credenziali di connessione.

Infine, se vuoi provare l’integrazione ma non hai un database PostgreSQL a cui connetterti, puoi usare uno dei nostri database PostgreSQL di esempio!

Curioso di DataCamp DataLab? Dai un’occhiata a questo articolo per saperne di più su questo notebook di nuova generazione per la data science.

Conclusione

Ottimo lavoro! In questo tutorial hai preso confidenza con PostgreSQL e hai imparato a gestire i database senza uscire da Python, usando il pacchetto psycopg2. Ma questo è solo l’inizio del viaggio. SQL è uno dei linguaggi di programmazione più utilizzati nella data science. È anche abbastanza facile da imparare, quindi non hai scuse.

Per fortuna, Datacamp ti copre le spalle. Ecco alcune risorse che possono aiutarti a diventare fluente in PostgreSQL e in SQL in generale:

• Beginner Introductions to PostgreSQL Tutorial
• Creating PostgreSQL Databases Course
• Improving Query Performance in PostgreSQL Course
• Managing Databases in SQL Course
• Functions for Manipulating Data in PostgreSQL Course