Come creare e manipolare un database SQL con Python

Articolo originale: https://www.freecodecamp.org/news/connect-python-with-sql/

Python e SQL sono due dei linguaggi più importanti per l'analisi di dati.

In questo articolo, ti mostrerò tutto ciò di cui hai bisogno per collegare Python e SQL.

Imparerai come prendere i dati da un database relazionale, memorizzare dati da un'applicazione Python in un database che possiedi e altri casi di utilizzo pratico in cui potresti imbatterti.

Parleremo di:

• Come imparare a usare Python e SQL insieme
• Come configurare il tuo ambiente Python e il Server MySQL
• Connettere il Server MySQL in Python
• Creare un nuovo database
• Creare tabelle e relazioni
• Inserire dati nelle tabelle
• Leggeri i dati
• Aggiornare i dati
• Cancellare i dati
• Creare dati da liste in Python
• Creare funzioni riutilizzabili per svolgere tutte queste operazioni

C'è un bel po' di materiale utile e interessante. Iniziamo!

Una breve nota prima di partire: su questo repository GitHub, c'è un Jupiter Notebook con tutto il codice usato in questo tutorial. Seguire scrivendo il codice passo passo è altamente consigliato.

Se non hai familiarità con SQL e i concetti dietro i database relazionali, c'è una grande quantità di risorse disponibile qui su freeCodeCamp!

Perché Python con SQL?

Per gli analisti e gli scienziati dei dati, Python ha molti vantaggi. Un enorme numero di librerie open source lo rendono uno strumento incredibilmente utile per chi maneggia dei dati.

Ci sono pandas, NumPy e Vaex per l'analisi dei dati, Matplotlib, seaborn e Bokeh per la visualizzazione, TensorFlow, scikit-learn e PyTorch per le applicazioni del machine learning (e tanti altri ancora).

Con una curva di apprendimento (relativamente) buona e una certa versatilità, non è una sorpresa che Python sia uno dei linguaggi di programmazione più rapidi da apprendere.

Quindi se stiamo usando Python per l'analisi di dati, vale la pena chiedersi da dove provengono i dati.

Mentre c'è un'ampia varietà di risorse di set di dati, in molti casi – particolarmente nelle attività imprenditoriali – i dati sono conservati in un database relazionale. I database relazionali sono estremamente efficienti, hanno grandi potenzialità e vengono ampiamente usati per creare, leggere, aggiornare e cancellare dati di ogni tipo.

I sistemi di gestione di database relazionali (RDBMS) utilizzati più di frequente – Oracle, MySQL, Microsoft SQL Server, PostgreSQL, IBM DB2 – usano tutti SQL (Structured Query Language) per consultare e apportare modifiche ai dati.

Nota che ogni RDBMS utilizza implementazioni leggermente diverse di SQL, quindi il codice SQL scritto per uno non funziona per gli altri senza qualche modifica (di solito piccola). I concetti, le strutture e le operazioni però, sono sostanzialmente uguali.

Ciò vuol dire che per un analista dei dati, una profonda comprensione di SQL è di fondamentale importanza. E conoscere come usare Python e SQL insieme, conferisce dei vantaggi ancora più consistenti quando si tratta di lavorare con dei dati.

Il resto di questo articolo sarà dedicato a mostrarti esattamente come possiamo farlo.

Per iniziare

Requisiti & Installazione

Per seguire passo passo questo tutorial, avrai bisogno di un ambiente Python configurato.

Io utilizzo Anaconda, ma ci sono molte altre opzioni. Puoi semplicemente cercare su google "come installare Python" se hai bisogno di un aiuto extra. Puoi anche usare Binder per programmare con il Jupyter Notebook associato.

Utilizzeremo MySQL Community Server dato che è gratuito e ampiamente utilizzato nell'industria. Se stai usando Windows, questa guida ti aiuterà a fare la configurazione, mentre qui puoi trovare le guide per gli utenti Mac e Linux.

Una volta terminata la configurazione avremo bisogno di metterli in comunicazione tra loro.

Per questo, dovremo installare la libreria Python MySQL Connector, usando pip:

pip install mysql-connector-python

Utilizzeremo anche pandas, quindi assicurati di averlo installato.

pip install pandas

Importare le librerie

Come per ogni progetto in Python, la prima cosa da fare è importare le librerie che ci servono.

È una buona pratica importare tutte le librerie che andremo a utilizzare all'inizio del progetto, così che chi legge o revisiona il nostro codice sa grossomodo cosa aspettarsi senza troppe sorprese.

Per questo tutorial, utilizzeremo due librerie – MySQL Connector e pandas.

import mysql.connector
from mysql.connector import Error
import pandas as pd

Importiamo la funzione di errore separatamente in modo da potervi accedere facilmente per le nostre funzioni.

Connettersi al Server MySQL

A questo punto dovremmo avere MySQL Community Server configurato sul nostro sistema. Adesso dobbiamo scrivere del codice Python che ci permetta di stabilire un collegamento al server.

def create_server_connection(host_name, user_name, user_password):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print("MySQL Database connection successful")
    except Error as err:
        print(f"Error: '{err}'")

    return connection

Creare funzioni riutilizzabili come questa è una buona pratica. In questo modo possiamo riusarla ancora e ancora senza sforzo. Una volta che una funzione è stata scritta, puoi riutilizzarla in tutti i tuoi progetti. Il futuro te ti sarà grato per questo!

Analizziamo il codice di questa funzione riga per riga per capire cosa sta succedendo:

Nella prima riga diamo il nome alla funzione (create_server_connection) e agli argomenti che la funzione accetterà (host_name, user_name e user_password).

La riga successiva chiude ogni connessione esistente così che il server non possa confondersi con connessioni multiple aperte.

In seguito, usiamo un blocco try-except per gestire dei potenziali errori. La prima parte tenta di creare una connessione con il server usando il metodo mysql.connector.connect() in base ai dettagli specificati dall'utente negli argomenti. In caso di successo, la funzione stampa un messaggio che comunica che la connessione è avvenuta.

La parte except del blocco stampa l'errore restituito dal server MySQL nel caso in cui si verifichi un errore durante la connessione.

Infine, se la connessione avviene con successo, la funzione restituisce un oggetto connection.

In pratica, lo utilizziamo assegnando l'output della funzione a una variabile, che poi diventa il nostro oggetto connection, a cui possiamo applicare altri metodi per creare altri oggetti utili.

connection = create_server_connection("localhost", "root", pw)

Dovremmo ottenere il messaggio che ci dice che la connessione è andata a buon fine:

Creare un nuovo database

Ora che abbiamo stabilito una connessione, il prossimo passo è creare un nuovo database sul server.

In questo tutorial, lo faremo solo una volta, ma anche in questo caso scriveremo tutto come una funzione riutilizzabile in modo da poterla riusare per progetti futuri.

def create_database(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database created successfully")
    except Error as err:
        print(f"Error: '{err}'")

Questa funzione accetta due argomenti, connection (il nostro oggetto connection) e query (una query SQL che scriveremo nel prossimo passaggio), ed esegue la query nel server attraverso la connessione.

Utilizziamo il metodo cursor sull'oggetto connection per creare un oggetto cursor (MySQL Connector utilizza un paradigma di programmazione orientata agli oggetti, quindi ci sono molti oggetti che ereditano le proprietà dagli oggetti genitori).

L'oggetto cursor possiede metodi come execute, executemany (che useremo in questo tutorial) insieme ad altri metodi utili.

Possiamo pensare all'oggetto cursor come a un modo per accedere al cursore lampeggiante in una finestra del terminale del server MySQL.

Definiamo una query per creare il database e chiamare la funzione:

Probabilmente questa è la query SQL più semplice.

Eseguire la funzione create_database con questi argomenti determina la creazione nel server del database chiamato school.

Perché il nostro database ha questo nome? Questo potrebbe essere un buon momento per guardare più nel dettaglio ciò che andremo a implementare in questo tutorial.

Il nostro database

Stiamo per implementare il database per la scuola internazionale di lingua – una scuola di lingua fittizia che offre lezioni di lingua professionali a clienti aziendali.

Questo diagramma entità-relazione stabilisce le entità (Teacher, Client, Course e Participant) e definisce le relazioni tra di loro.

Il codice SQL grezzo, i requisiti del database e i dati per entrare nel database sono contenuti in questo repository GitHub, ma li vedrai anche proseguendo con questo tutorial.

Connettersi al database

Ora che abbiamo creato un database nel server MySQL, possiamo modificare la funzione create_server_connection per connetterci direttamente a questo database.

Nota che è possibile – comune, in realtà – avere più database su un solo server MySQL, così da connetterci sempre e automaticamente al database al quale siamo interessati.

Possiamo farlo in questo modo:

def create_db_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("MySQL Database connection successful")
    except Error as err:
        print(f"Error: '{err}'")

    return connection

Questa è esattamente la stessa funzione, ma ora prendiamo un argomento in più – il nome del database (db_name) – e lo passiamo come argomento del metodo connect().

Creare una funzione per eseguire una query

La funzione finale che creeremo è di fondamentale importanza – una funzione per eseguire una query, che prenderà le nostre query SQL, conservate in Python come stringhe, e le passerà al metodo cursor.execute() per eseguirle sul server.

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query successful")
    except Error as err:
        print(f"Error: '{err}'")

Questa funzione è esattamente la stessa funzione create_database creata precedentemente, tranne che per l'utilizzo del metodo connection.commit() per assicurare che i comandi definiti nelle query SQL siano implementati.

Questa funzione sarà il nostro cavallo di battaglia, che useremo (insieme a create_db_connection) per creare tabelle, stabilire relazioni tra tabelle e inserirvi dei dati, aggiornare e cancellare i dati nel nostro database.

Se sei un esperto di SQL, questa funzione ti permetterà di eseguire tutti i comandi complessi e le query che possono servirti, direttamente da uno script Python. È uno strumento estremamente utile per la gestione dei dati.

Creare tabelle

Ora, abbiamo tutto pronto per iniziare a eseguire dei comandi SQL sul nostro server e costruire un database. La prima cosa che vogliamo fare è creare le tabelle necessarie.

Partiamo con la tabella teacher:

create_teacher_table = """
CREATE TABLE teacher (
  teacher_id INT PRIMARY KEY,
  first_name VARCHAR(40) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  language_1 VARCHAR(3) NOT NULL,
  language_2 VARCHAR(3),
  dob DATE,
  tax_id INT UNIQUE,
  phone_no VARCHAR(20)
  );
 """

connection = create_db_connection("localhost", "root", pw, db) # Connettiti al database
execute_query(connection, create_teacher_table) # Esegui la query definita

Prima di tutto, assegniamo il nostro comando SQL a una variabile con un nome appropriato.

In questo caso, usiamo le virgolette triple per le stringhe multi-riga in Python per memorizzare la query SQL, che poi usiamo come argomento della funzione execute_query per implementarla.

Nota che la formattazione multi-riga è esclusivamente per migliorare la leggibilità del codice. A SQL o Python non interessa che il comando sia disposto in questo modo. Finché la sintassi è corretta, entrambi i linguaggi la accetteranno.

Tuttavia, a beneficio di chi legge il codice (anche se sarà soltanto il futuro te!), è molto utile fare questa operazione per rendere il codice più leggibile e comprensibile.

Ciò è vero anche per la scrittura degli operatori SQL in maiuscolo, una convenzione ampiamente utilizzata e fortemente raccomandata, anche se il software che esegue il codice non è case-sentive e tratterà CREATE TABLE teacher e create table teacher come comandi identici.

L'esecuzione del codice qui sopra ci dà questi messaggi, che possiamo anche verificare nella linea di comando client del server MySQL:

Ottimo! E ora creiamo le altre tabelle.

create_client_table = """
CREATE TABLE client (
  client_id INT PRIMARY KEY,
  client_name VARCHAR(40) NOT NULL,
  address VARCHAR(60) NOT NULL,
  industry VARCHAR(20)
);
 """

create_participant_table = """
CREATE TABLE participant (
  participant_id INT PRIMARY KEY,
  first_name VARCHAR(40) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  phone_no VARCHAR(20),
  client INT
);
"""

create_course_table = """
CREATE TABLE course (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(40) NOT NULL,
  language VARCHAR(3) NOT NULL,
  level VARCHAR(2),
  course_length_weeks INT,
  start_date DATE,
  in_school BOOLEAN,
  teacher INT,
  client INT
);
"""


connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, create_client_table)
execute_query(connection, create_participant_table)
execute_query(connection, create_course_table)

In questo modo, abbiamo creato le quattro tabelle necessarie per le nostre quattro entità.

Adesso dobbiamo definire le relazioni tra di loro e creare una o più tabelle per gestire la relazione many-to-many tra le tabelle partecipant e course.

E lo facciamo esattamente in questo modo:

alter_participant = """
ALTER TABLE participant
ADD FOREIGN KEY(client)
REFERENCES client(client_id)
ON DELETE SET NULL;
"""

alter_course = """
ALTER TABLE course
ADD FOREIGN KEY(teacher)
REFERENCES teacher(teacher_id)
ON DELETE SET NULL;
"""

alter_course_again = """
ALTER TABLE course
ADD FOREIGN KEY(client)
REFERENCES client(client_id)
ON DELETE SET NULL;
"""

create_takescourse_table = """
CREATE TABLE takes_course (
  participant_id INT,
  course_id INT,
  PRIMARY KEY(participant_id, course_id),
  FOREIGN KEY(participant_id) REFERENCES participant(participant_id) ON DELETE CASCADE,
  FOREIGN KEY(course_id) REFERENCES course(course_id) ON DELETE CASCADE
);
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, alter_participant)
execute_query(connection, alter_course)
execute_query(connection, alter_course_again)
execute_query(connection, create_takescourse_table)

A questo punto, le tabelle sono state create con i vincoli appropriati, le chiavi primarie, e le relazioni di chiave esterna.

Aggiungere dati alle tabelle

Il prossimo passo è aggiungere dei dati alle tabelle. Usiamo ancora execute_query per eseguire i comandi SQL sul server, partendo di nuovo dalla tabella teacher.

pop_teacher = """
INSERT INTO teacher VALUES
(1,  'James', 'Smith', 'ENG', NULL, '1985-04-20', 12345, '+491774553676'),
(2, 'Stefanie',  'Martin',  'FRA', NULL,  '1970-02-17', 23456, '+491234567890'), 
(3, 'Steve', 'Wang',  'MAN', 'ENG', '1990-11-12', 34567, '+447840921333'),
(4, 'Friederike',  'Müller-Rossi', 'DEU', 'ITA', '1987-07-07',  45678, '+492345678901'),
(5, 'Isobel', 'Ivanova', 'RUS', 'ENG', '1963-05-30',  56789, '+491772635467'),
(6, 'Niamh', 'Murphy', 'ENG', 'IRI', '1995-09-08',  67890, '+491231231232');
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, pop_teacher)

Funziona tutto correttamente? Possiamo verificarlo dalla riga di comando del client di MySQL:

E adesso riempiamo le tabelle restanti.

pop_client = """
INSERT INTO client VALUES
(101, 'Big Business Federation', '123 Falschungstraße, 10999 Berlin', 'NGO'),
(102, 'eCommerce GmbH', '27 Ersatz Allee, 10317 Berlin', 'Retail'),
(103, 'AutoMaker AG',  '20 Künstlichstraße, 10023 Berlin', 'Auto'),
(104, 'Banko Bank',  '12 Betrugstraße, 12345 Berlin', 'Banking'),
(105, 'WeMoveIt GmbH', '138 Arglistweg, 10065 Berlin', 'Logistics');
"""

pop_participant = """
INSERT INTO participant VALUES
(101, 'Marina', 'Berg','491635558182', 101),
(102, 'Andrea', 'Duerr', '49159555740', 101),
(103, 'Philipp', 'Probst',  '49155555692', 102),
(104, 'René',  'Brandt',  '4916355546',  102),
(105, 'Susanne', 'Shuster', '49155555779', 102),
(106, 'Christian', 'Schreiner', '49162555375', 101),
(107, 'Harry', 'Kim', '49177555633', 101),
(108, 'Jan', 'Nowak', '49151555824', 101),
(109, 'Pablo', 'Garcia',  '49162555176', 101),
(110, 'Melanie', 'Dreschler', '49151555527', 103),
(111, 'Dieter', 'Durr',  '49178555311', 103),
(112, 'Max', 'Mustermann', '49152555195', 104),
(113, 'Maxine', 'Mustermann', '49177555355', 104),
(114, 'Heiko', 'Fleischer', '49155555581', 105);
"""

pop_course = """
INSERT INTO course VALUES
(12, 'English for Logistics', 'ENG', 'A1', 10, '2020-02-01', TRUE,  1, 105),
(13, 'Beginner English', 'ENG', 'A2', 40, '2019-11-12',  FALSE, 6, 101),
(14, 'Intermediate English', 'ENG', 'B2', 40, '2019-11-12', FALSE, 6, 101),
(15, 'Advanced English', 'ENG', 'C1', 40, '2019-11-12', FALSE, 6, 101),
(16, 'Mandarin für Autoindustrie', 'MAN', 'B1', 15, '2020-01-15', TRUE, 3, 103),
(17, 'Français intermédiaire', 'FRA', 'B1',  18, '2020-04-03', FALSE, 2, 101),
(18, 'Deutsch für Anfänger', 'DEU', 'A2', 8, '2020-02-14', TRUE, 4, 102),
(19, 'Intermediate English', 'ENG', 'B2', 10, '2020-03-29', FALSE, 1, 104),
(20, 'Fortgeschrittenes Russisch', 'RUS', 'C1',  4, '2020-04-08',  FALSE, 5, 103);
"""

pop_takescourse = """
INSERT INTO takes_course VALUES
(101, 15),
(101, 17),
(102, 17),
(103, 18),
(104, 18),
(105, 18),
(106, 13),
(107, 13),
(108, 13),
(109, 14),
(109, 15),
(110, 16),
(110, 20),
(111, 16),
(114, 12),
(112, 19),
(113, 19);
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, pop_client)
execute_query(connection, pop_participant)
execute_query(connection, pop_course)
execute_query(connection, pop_takescourse)

Molto bene! Abbiamo creato un database completo con relazioni, vincoli e dati in MySQL usando soltanto dei comando Python.

Abbiamo fatto tutto ciò gradualmente per rendere il procedimento comprensibile. Ma a questo punto puoi ben capire che è possibile inserire facilmente queste operazioni in uno script Python da eseguire con un comando sul terminale.

Leggere i dati

Adesso abbiamo un database funzionante su cui lavorare. Come analista, è probabile che tu ti trovi a lavorare su database esistenti. Sapere come estrarre i dati da un database per poterli usare in Python è di fondamentale importanza, ed è esattamente ciò che andremo ad affrontare come step successivo.

Avremo bisogno di un'altra funzione. Invece di cursor.commit(), questa volta useremo cursor.fetchall(), per leggere i dati dal database senza apportare alcun cambiamento.

def read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as err:
        print(f"Error: '{err}'")

Di nuovo, implementeremo la query in modo molto simile usando execute_query. Proviamo con una query semplice per vedere come funziona.

q1 = """
SELECT *
FROM teacher;
"""

connection = create_db_connection("localhost", "root", pw, db)
results = read_query(connection, q1)

for result in results:
  print(result)

Esattamente ciò che ci aspettavamo. La funzione può operare anche su query più complesse, come questa che contiene un'istruzione JOIN sulle tabelle course e client.

q5 = """
SELECT course.course_id, course.course_name, course.language, client.client_name, client.address
FROM course
JOIN client
ON course.client = client.client_id
WHERE course.in_school = FALSE;
"""

connection = create_db_connection("localhost", "root", pw, db)
results = read_query(connection, q5)

for result in results:
  print(result)

Per far sì che questi dati siano più utili e pronti da manipolare, possiamo visualizzarli con una formattazione differente.

Facciamo un paio di esempi per vedere come fare.

Formattare l'output in una lista

#Inizializza una lista vuota
from_db = []

# Itera sui risultati e aggiungili alla fine della lista

# Restituisci una lista di tuple
for result in results:
  result = result
  from_db.append(result)

Formattare l'output in una lista di liste

# Restituisci una lista di liste
from_db = []

for result in results:
  result = list(result)
  from_db.append(result)

Formattare l'output in un dataframe pandas

Quando si tratta di analizzare dati con Python, pandas è il nostro vecchio e fidato amico. È molto semplice convertire l'output del database in un dataframe, e da qui le possibilità sono infinite!

# Restituisci una lista di lista e poi crea un dataframe pandas
from_db = []

for result in results:
  result = list(result)
  from_db.append(result)


columns = ["course_id", "course_name", "language", "client_name", "address"]
df = pd.DataFrame(from_db, columns=columns)

Auspicabilmente, potrai vedere un gran numero di possibilità dispiegarsi davanti ai tuoi occhi. Con poche righe di codice, possiamo estrarre facilmente tutti i dati che possiamo maneggiare dal database relazionale e convogliarli in un apparato analitico d'avanguardia. Molto utile.

Aggiornare i dati

Durante la manutenzione di un database, potrebbe essere necessario apportare delle modifiche ai dati esistenti. In questa sezione vedremo come fare.

Ipotizziamo che alla scuola venga notificato che uno dei suoi clienti, Big Business Federation, sta trasferendo la propria sede all'indirizzo 23 Fingiertweg, 14534 Berlin. In questo caso, l'amministratore del database (cioè noi!) dovrà fare una modifica.

Fortunatamente, possiamo operare tramite la funzione execute_query con l'istruzione SQL UPDATE.

update = """
UPDATE client 
SET address = '23 Fingiertweg, 14534 Berlin' 
WHERE client_id = 101;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, update)

Nota che la clausola WHERE è molto importante. Se eseguiamo questa query senza la clausola WHERE, tutti gli indirizzi della tabella client saranno aggiornati in 23 Fingiertweg. Non esattamente quello che volevamo fare.

Osserva anche che abbiamo usato WHERE client_id = 101 nella query UPDATE. Sarebbe stato possibile anche usare WHERE client_name = 'Big Business Federation' oppure WHERE address = '123 Falschungstraße, 10999 Berlin', o anche WHERE address LIKE '%Falschung%'.

La cosa importante è che la clausola WHERE ci permette di identificare in modo unico il dato (o i dati) che vogliamo aggiornare.

Cancellare i dati

È possibile eseguire la funzione execute_query per cancellare dei dati, usando l'istruzione DELETE.

Quando usiamo SQL con i database relazionali, dobbiamo stare attenti a utilizzare il comando DELETE. Questo non è Windows e non c'è nessun pop up di avvertimento "Sei sicuro di voler eliminare questa cosa?" e non c'è un cestino. Una volta eliminato un dato, è andato per sempre.

Detto ciò, a volte dobbiamo eliminare davvero delle cose. Vediamo come svolgere questa operazione eliminando un corso dalla tabella course.

Prima di tutto, vediamo quali corsi abbiamo:

Diciamo che il corso 20, Fortgeschrittenes Russisch (ovvero 'russo avanzato' per noi), sta terminando e vogliamo rimuoverlo dal database.

A questo punto, non sarai sorpreso di sapere come fare – salviamo il comando SQL come una stringa e poi lo usiamo nel nostro cavallo di battaglia, la funzione execute_query.

delete_course = """
DELETE FROM course 
WHERE course_id = 20;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, delete_course)

Controlliamo che abbia avuto l'effetto desiderato:

'Russo avanzato' non c'è più, proprio come volevamo.

Possiamo eliminare intere colonne usando il comando DROP COLUMN e intere tabelle con DROP TABLE, ma non approfondiremo queste operazioni in questo tutorial.

Procedi pure e fai le tue prove – non ha importanza se cancelli una colonna o una tabella del database di una scuola immaginaria ed è una buona idea acquisire sicurezza con questi comandi prima di passare a un ambiente di produzione.

CRUD

Adesso siamo in grado di completare le quattro operazioni principali per l'archiviazione permanente di dati.

Abbiamo imparato come:

• Creare - un nuovo database con tabelle e dati
• Leggere - estrarre dati dal database e memorizzarli in vari formati
• Aggiornare - apportare cambiamenti ai dati esistenti nel database
• Cancellare - eliminare dati che non sono più necessari

Queste sono delle operazioni incredibilmente utili.

Prima di terminare, c'è un'ultima abilità piuttosto utile da conoscere.

Creare dei dati da liste

Quando abbiamo riempito le nostre tabelle abbiamo visto che possiamo usare il comando SQL INSERT nella funzione execute_query per inserire dati nel database.

Dato che stiamo usando Python per manipolare il database SQL, sarebbe utile prendere una struttura di dati di Python (come una lista) e inserirla direttamente nel nostro database.

Potrebbe essere comodo se vogliamo salvare dei log di attività utente sull'app di un social media che abbiamo scritto in Python, o degli input da utenti in un Wiki che abbiamo realizzato, ad esempio. Ci sono molti possibili utilizzi a cui possiamo pensare.

Questo metodo è anche più sicuro se il database è aperto agli utenti in ogni punto, dato che aiuta a evitare attacchi SQL injection che possono danneggiare o addirittura distruggere un'intero database.

Per fare ciò, scriveremo una funzione usando il metodo executemany() al posto del più semplice metodo execute() che abbiamo usato finora.

def execute_list_query(connection, sql, val):
    cursor = connection.cursor()
    try:
        cursor.executemany(sql, val)
        connection.commit()
        print("Query successful")
    except Error as err:
        print(f"Error: '{err}'")

Adesso che abbiamo la funzione, dobbiamo definire un comando SQL (sql) e una lista contenente i valori che vorremmo inserire nel database (val). I valori devono essere salvati come una lista di tuple, un modo piuttosto comune di archiviare dati in Python.

Per aggiungere due nuovi insegnanti al database, possiamo scrivere del codice come il seguente:

sql = '''
    INSERT INTO teacher (teacher_id, first_name, last_name, language_1, language_2, dob, tax_id, phone_no) 
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    '''
    
val = [
    (7, 'Hank', 'Dodson', 'ENG', None, '1991-12-23', 11111, '+491772345678'), 
    (8, 'Sue', 'Perkins', 'MAN', 'ENG', '1976-02-02', 22222, '+491443456432')
]

Osserva che all'interno del codice sql usiamo %s come segnaposto per il nostro valore. La somiglianza con il segnaposto %s per una stringa in Python è solo casuale (e francamente, molto fuorviante). In MySQL Connector/Python usiamo %s per tutti i tipi di dati (stringhe, int, date, ecc.).

Puoi trovare un gran numero di domande su Stackoverflow in cui qualcuno si è confuso e ha provato a usare il segnaposto %d per degli interi, perché lo aveva usato in Python. Qui non funzionerà – dobbiamo usare %s per ogni colonna a cui vogliamo aggiungere un valore.

La funzione executemany() prende ogni tupla nella lista val e sostituisce al segnaposto i valori rilevanti per la colonna ed esegue il comando SQL per ogni tupla contenuta nella lista.

Questo può essere effettuato su più righe di dati, purché siano formattate correttamente. Nel nostro esempio, Aggiungeremo due nuovi insegnanti, a scopo illustrativo, ma in principio possiamo aggiungerne quanti ne desideriamo.

Proseguiamo eseguendo questa query per aggiungere gli insegnanti al database.

connection = create_db_connection("localhost", "root", pw, db)
execute_list_query(connection, sql, val)

Benvenuti alla scuola internazionale di lingue, Hank e Sue!

Questa è un'altra funzione estremamente utile, che ci permette di prendere i dati generati all'interno di script e applicazioni Python e di inserirli direttamente nel nostro database.

Conclusione

Abbiamo fatto parecchia strada in questo tutorial.

Abbiamo imparato come usare Python e MySQL Connector per creare un nuovo database sul server MySQL, creare tabelle nel database, definire le relazioni tra di loro e inserire dati.

Abbiamo parlato di come creare, leggere, aggiornare ed eliminare dati dal database.

Abbiamo visto come estrarre dati da un database esistente e caricarli in un dataframe pandas, pronti per analisi e ulteriori elaborazioni traendo vantaggio da tutte le possibilità offerte dal PyData stack.

Andando nella direzione opposta, abbiamo anche imparato come prendere dati generati da script e applicazioni Python, e aggiungerli in un database dove possono essere archiviati in modo sicuro per poi essere recuperati e manipolati in un secondo momento.

Spero che questo tutorial ti abbia aiutato a vedere come possiamo usare Python e SQL insieme per manipolare i dati al meglio!