MySQL in Python Tutorial: prime basi

Creare un nuovo database in MySQL con Python

Da qui in poi avrai bisogno di competenze SQL; il corso Introduction to SQL di DataCamp è un buon punto di partenza per metterti in pari. 

Il modo tipico per creare un database in MySQL è il seguente: 

CREATE DATABASE book_ratings;

Per eseguire la stessa query in Python, devi creare un’istanza dell’oggetto cursor(). 

Dall’oggetto cursor() puoi passare i comandi SQL come stringa al metodo execute(), che eseguirà il codice. 

Creiamo un database chiamato “book_ratings…”

create_db = "CREATE DATABASE book_ratings"
with database.cursor() as cursor:
    cursor.execute(create_db)

Nota: eseguire questo codice più volte genererà un errore per informarti che il database esiste già.

Puoi verificare i database esistenti su un server come segue:

show_existing_db = "SHOW DATABASES"

with database.cursor() as cursor: 
    cursor.execute(show_existing_db)
    for db in cursor:
        print(db)

"""
('book_ratings',)
('information_schema',)
('mysql',)
('performance_schema',)
('sakila',)
('sys',)
('world',)
"""

Il codice sopra stampa i nomi di tutti i database attualmente presenti sul nostro server MySQL. Ce ne sono diversi perché il server MySQL genera automaticamente database per fornire accesso a varie metainformazioni e impostazioni del server.

Connettersi a un database esistente

Supponiamo che tu abbia già creato un database, ma non ti sia ancora connesso al server; come ti connetteresti direttamente al database? 

Semplice. 

Passa il nome del database come argomento al metodo connect():

try:
    with connector.connect(
        host = "localhost",
        user = "root",
        password = PASSWORD,
        database = "book_ratings"
    ) as database:
       
        print(database})

except connector.Error as e:
    print(e)

"""
<mysql.connector.connection_cext.CMySQLConnection object at 0x0000016DD8C24970>
"""

Creare tabelle

Le informazioni in un database sono archiviate in una tabella. Una tabella è semplicemente un formato usato per memorizzare una raccolta di dati correlati composta da colonne e righe. 

In un database possono esserci più tabelle, anche se non sono tra loro collegate. Puoi anche avere un database relazionale che contiene una sola tabella, come mostreremo in questa sezione – approfondisci i database relazionali con il corso Introduction to Relational Databases in SQL. 

La creazione di tabelle segue un approccio simile a quanto già visto; 

• Scrivi un comando SQL come stringa 
• Passalo al metodo execute() dell’oggetto cursor. 

L’istruzione SQL in questo caso è CREATE TABLE.

Usiamola per creare una tabella per i libri:

create_books_table = """
CREATE TABLE books(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    genre VARCHAR(100),
    release_year YEAR(4)
)
"""
# Create cursor object
with existing_database.cursor() as cursor:
    cursor.execute(create_books_table)
    existing_database.commit()

Nell’ultima riga del codice avrai notato che abbiamo usato il metodo commit() sul nostro oggetto existing_database. È stato necessario perché il comportamento predefinito del connettore MySQL per Python è di non effettuare automaticamente il commit di una transazione. 

Ogni volta che modifichi i dati di una tabella, devi chiamare il metodo commit() – scopri di più sul metodo commit() nella documentazione MySQL. 

Altre cose importanti da notare nel codice includono: 

• NOT NULL: un vincolo che impone a una colonna di non accettare valori NULL. 
• PRIMARY KEY: un valore univoco usato per identificare righe specifiche in una tabella. 
• AUTO_INCREMENT: genera automaticamente un numero univoco per i nuovi record inseriti nella tabella. 

Possiamo verificare che lo schema sia stato creato correttamente usando l’istruzione DESCRIBE e poi stampando tutti gli elementi con il metodo fetchall() sul nostro oggetto cursor…

describe_books = "DESCRIBE books"
with existing_database.cursor() as cursor:
    cursor.execute(describe_books)
    books_schema = cursor.fetchall()
    for column in books_schema:
        print(column)

"""
('id', b'int', 'NO', 'PRI', None, 'auto_increment')
('title', b'varchar(100)', 'YES', '', None, '')
('author', b'varchar(100)', 'YES', '', None, '')
('genre', b'varchar(100)', 'YES', '', None, '')
('release_year', b'year', 'YES', '', None, '')
"""

Inserimento dei dati

Nelle sezioni precedenti abbiamo creato una tabella chiamata books nel nostro database book_ratings. Al momento, questa tabella è vuota. La prossima sezione coprirà due approcci a tua disposizione per popolare una tabella. 

Inserire un singolo record

Potrebbe capitare di dover inserire un singolo record in una tabella. Anche se nella pratica è raro, è comunque possibile farlo così:

# INSERT SINGLE RECORD
insert_single_record = "INSERT INTO books (id, title, author, genre, release_year)\
    VALUES (%s, %s, %s, %s, %s)"
single_record = (
    "1", "Choose Yourself! Be Happy, Make Millions, Live the Dream", "James Altucher", "self-help", "2013"
    )

# Create cursor object
with existing_database.cursor() as cursor:
    cursor.execute(insert_single_record, single_record)
    existing_database.commit()

Inserire più record

Il modo più comune per inserire dati in una tabella è con il metodo executemany(operation_to_perform, seq_of_params) dell’oggetto cursor; questo ti permette di inserire più record in una volta sola. 

La struttura dell’operazione che vogliamo eseguire resta la stessa; aggiorniamo solo la sequenza dei parametri. 

Ecco come appare nel codice: 

multiple_records = [
    (
        "2",
        "Skip the Line: The 10,000 Experiments Rule and Other Surprising Advice for Reaching Your Goals",
        "James Altucher",
        "self-help",
        "2021"        
    ),
    (
        "3",
        "The Power of No: Because One Little Word Can Bring Health, Abundance, and Happiness",
        "James Altucher",
        "self-help",
        "2014"
    ),
    ]

with existing_database.cursor() as cursor:
    cursor.executemany(insert_multiple_records, multiple_records)
    existing_database.commit()

Selezionare i dati 

L’istruzione SELECT viene usata per recuperare righe selezionate da una o più tabelle in un database. Possiamo usare SELECT in due modi: 

1. Selezionare colonne specifiche da una tabella.

select_specific_cols = "SELECT author, release_year FROM books"
with existing_database.cursor() as cursor:
    cursor.execute(select_specific_cols)

1. Restituire tutte le colonne usando l’asterisco “*”

# Create cursor object
select_all_cols = "SELECT * FROM books"
with existing_database.cursor() as cursor:
    cursor.execute(select_all_cols)

Ordinare e filtrare i dati 

Ordinare e filtrare i dati ti permette di restituire i risultati che ti interessano fornendo criteri specifici che i record devono soddisfare. 

Ecco alcuni esempi: 

WHERE

L’istruzione WHERE ti consente di filtrare i dati in base a una condizione specificata. 

Per esempio, supponiamo di voler estrarre il nome dell’autore e il titolo del libro per qualsiasi libro pubblicato entro e non oltre il 2010: 

condition = "SELECT author, title FROM books WHERE release_year <= 2010"
with existing_database.cursor() as cursor:
    cursor.execute(condition)

ORDER BY

Possiamo anche usare l’istruzione ORDER BY per ordinare i risultati in ordine crescente (ASC) o decrescente (DESC). Nota che la modalità predefinita è sempre l’ordine crescente, a meno che non venga specificato diversamente. 

Ecco come ordineremmo i dati per anno di pubblicazione in ordine decrescente:

order_by_year = "SELECT * FROM books ORDER BY release_year DESC"
with existing_database.cursor() as cursor:
    cursor.execute(order_by_year)

LIMIT

L’istruzione LIMIT ti permette di specificare il numero di record da restituire nel result set.

# Create cursor object
define_limit = "SELECT * FROM books LIMIT 3"
with existing_database.cursor() as cursor:
    cursor.execute(define_limit)

Il codice sopra limita a tre il numero di record restituiti. 

Aggiornare ed eliminare i dati

Capiterà che i dati cambino, non siano più utili o siano errati. In questa sezione imparerai a gestire questi casi con aggiornamenti ed eliminazioni. 

UPDATE

La query UPDATE ti consente di modificare i valori esistenti all’interno di una tabella senza alterarne la struttura. 

Per esempio, potresti dover aggiornare un record se qualcuno cambia nome; ecco come apparirebbe nel codice:

update_query = """
UPDATE
    books
SET
    author = "Big J"
WHERE
    author = "James Altucher"
"""
with existing_database.cursor() as cursor:
    cursor.execute(update_query)
    existing_database.commit()

DELETE

La query DELETE ti permette di eliminare un record da una tabella:

drop_record = "DELETE FROM books WHERE release_year <= 2000"
with existing_database.cursor() as cursor:
    cursor.execute(drop_record)
    existing_database.commit()

In questo codice abbiamo eliminato tutti i record con anno di pubblicazione minore o uguale a 2000. 

DROP TABLE

L’istruzione DROP TABLE ti consente di rimuovere in modo permanente una tabella da un database. Aggiungere IF EXISTS permette di gestire scenari in cui non sei sicuro che la tabella da eliminare esista, ma è del tutto opzionale. 

Eliminiamo la tabella che abbiamo creato nel nostro database book_ratings: 

drop_table = "DROP TABLE IF EXISTS books"
with existing_database.cursor() as cursor:
    cursor.execute(drop_table)
    existing_database.commit()

Conclusione

In questo tutorial hai imparato come stabilire una connessione con un server MySQL in Python, creare un nuovo database, connetterti a un database esistente, creare una tabella ed eseguire varie operazioni di query su una tabella MySQL da Python.  

Approfondisci con queste risorse:

• Introduction to Databases in Python 
• SQL Server, PostgreSQL, MySQL… Qual è la differenza? Da dove comincio?
• Corsi SQL per principianti
• PostgreSQL vs. MySQL