🗄️ Modulo 6: Database SQL con SQLAlchemy

Impara a salvare dati in modo permanente con database relazionali

📚

MODULO
6 di 8

⏱️

TEMPO
90 minuti

🎯

LIVELLO
Avanzato

🎯 Perché un Database?

Finora abbiamo lavorato con dati temporanei. Un database risolve questi problemi:

💾

Persistenza
I dati sopravvivono al riavvio del server

👥

Multi-utente
Più utenti vedono gli stessi dati

🔍

Ricerca
Trovare dati complessi in modo efficiente

🛡️

Sicurezza
Backup, transazioni, integrità dati

✅ CON DATABASE

Dati salvati permanentemente
Supporto per milioni di record
Query complesse e JOIN
Backup automatici
Transazioni ACID

❌ SENZA DATABASE

Dati persi al riavvio
Limitato a memoria RAM
Ricerca lenta e inefficiente
Nessun backup
Rischio di corruzione dati

🔧 SQLite + SQLAlchemy: La Combinazione Perfetta

SQLite – Il Database Embedded

⚡ Leggerissimo: Single file, nessun server
🚀 Facile Setup: Nessuna installazione
💾 Portatile: File .db che puoi copiare ovunque
🎯 Perfetto per: Sviluppo, testing, app piccole

SQLAlchemy – L’ORM Python

🐍 Python Puro: Scrivere SQL in Python
🛡️ Sicuro: Previene SQL Injection
🔄 Portabile: Cambia database facilmente
⚙️ Potente: Query complesse semplificate

💡 Cos’è un ORM?

Object-Relational Mapping traduce tra:

Python (SQLAlchemy)	SQL (Database)
`User` class	`users` table
`user.name = "Mario"`	`UPDATE users SET name='Mario'`
`session.query(User)`	`SELECT * FROM users`

⚙️ Installazione e Setup

Installa le dipendenze

Terminale

pip install flask-sqlalchemy flask-migrate

Questo installerà:

Flask-SQLAlchemy: Integrazione Flask + SQLAlchemy
Flask-Migrate: Migrazioni database (come Git per DB)

Configura Flask per il database

📄 app.py – Configurazione Base

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
import os

# Crea l'app Flask
app = Flask(__name__)

# Configurazione Database
basedir = os.path.abspath(os.path.dirname(__file__))
app.config['SQLALCHEMY_DATABASE_URI'] = f"sqlite:///{os.path.join(basedir, 'app.db')}"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Inizializza SQLAlchemy
db = SQLAlchemy(app)

# Inizializza Migrate per le migrazioni
migrate = Migrate(app, db)

🔍 Spiegazione Configurazione

SQLALCHEMY_DATABASE_URI: Percorso del file SQLite (app.db nella cartella del progetto)
SQLALCHEMY_TRACK_MODIFICATIONS: Disabilitato per performance
basedir: Trova automaticamente la cartella del progetto

🏗️ Definire i Modelli (Tabelle)

I modelli sono classi Python che rappresentano tabelle del database.

Modello Utente Base

📄 models.py

from datetime import datetime
from app import db  # Importa db da app.py

class User(db.Model):
    """Modello per gli utenti"""
    
    # Nome della tabella (opzionale, default: nome classe in minuscolo)
    __tablename__ = 'users'
    
    # Colonne della tabella
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password_hash = db.Column(db.String(128), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    is_active = db.Column(db.Boolean, default=True)
    
    # Relazione 1-a-Molti (un utente molti post)
    posts = db.relationship('Post', backref='author', lazy=True)
    
    def __repr__(self):
        return f'<User {self.username}>'

Modello Post con Relazioni

📄 models.py – Continua

class Post(db.Model):
    """Modello per i post del blog"""
    
    __tablename__ = 'posts'
    
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Foreign Key: collegamento a User
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    
    # Relazione Molti-a-Molti (tag)
    tags = db.relationship('Tag', secondary='post_tags', back_populates='posts')
    
    def __repr__(self):
        return f'<Post {self.title}>'

class Tag(db.Model):
    """Modello per i tag"""
    
    __tablename__ = 'tags'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)
    
    # Relazione Molti-a-Molti
    posts = db.relationship('Post', secondary='post_tags', back_populates='tags')

# Tabella di join per relazione Molti-a-Molti
post_tags = db.Table('post_tags',
    db.Column('post_id', db.Integer, db.ForeignKey('posts.id'), primary_key=True),
    db.Column('tag_id', db.Integer, db.ForeignKey('tags.id'), primary_key=True)
)

📊 Tipi di Colonna SQLAlchemy

Tipo Python	Tipo SQL	Descrizione	Esempio
`db.Integer`	INTEGER	Numeri interi	id, quantità
`db.String(255)`	VARCHAR(255)	Testo a lunghezza fissa	nome, email
`db.Text`	TEXT	Testo lungo	contenuto, descrizione
`db.Boolean`	BOOLEAN	Vero/Falso	is_active, is_admin
`db.DateTime`	DATETIME	Data e ora	created_at, updated_at
`db.Float`	FLOAT	Numeri decimali	prezzo, valutazione

🚀 Migrazioni Database

Le migrazioni sono come “Git per database”: tengono traccia dei cambiamenti allo schema.

Inizializza il sistema di migrazioni

Terminale – Prima volta

# Posizionati nella cartella del progetto
cd /percorso/del/tuo/progetto

# Inizializza le migrazioni
flask db init

Crea una cartella migrations/ con tutto il necessario.

Crea una migrazione

Terminale – Dopo aver cambiato i modelli

# Genera lo script di migrazione
flask db migrate -m "Creazione tabelle utenti e post"

# Applica la migrazione al database
flask db upgrade

Comandi utili per le migrazioni

# Vedere lo stato delle migrazioni
flask db current

# Tornare indietro di una migrazione
flask db downgrade

# Applicare tutte le migrazioni
flask db upgrade

# Creare una migrazione vuota (per modifiche manuali)
flask db revision -m "Descrizione"

💡 Perché le Migrazioni?

Version Control: Tieni traccia dei cambiamenti allo schema
Team Work: Sincronizza database tra sviluppatori
Rollback: Torna indietro se qualcosa va storto
Produzione: Aggiorna database live senza perdere dati

💾 Operazioni CRUD Complete

CRUD = Create, Read, Update, Delete – le 4 operazioni fondamentali.

📝

CREATE

Inserire nuovi dati

🔍

READ

Leggere dati esistenti

✏️

UPDATE

Modificare dati

🗑️

DELETE

Eliminare dati

CREATE – Inserire Dati

📄 app.py – Route per creare

from flask import request, jsonify
from models import db, User, Post

@app.route('/api/users', methods=['POST'])
def create_user():
    data = request.json
    
    # Crea nuovo utente
    new_user = User(
        username=data['username'],
        email=data['email'],
        password_hash=data['password']  # In realtà: hash della password
    )
    
    # Aggiungi alla sessione
    db.session.add(new_user)
    
    # Salva nel database
    db.session.commit()
    
    return jsonify({
        'message': 'Utente creato con successo',
        'user': {
            'id': new_user.id,
            'username': new_user.username,
            'email': new_user.email
        }
    }), 201

READ – Leggere Dati

📄 app.py – Varie query

# GET tutti gli utenti
@app.route('/api/users', methods=['GET'])
def get_users():
    users = User.query.all()  # SELECT * FROM users
    return jsonify([{
        'id': u.id,
        'username': u.username,
        'email': u.email
    } for u in users])

# GET utente per ID
@app.route('/api/users/<int:user_id>', methods=['GET'])
def get_user(user_id):
    user = User.query.get_or_404(user_id)  # 404 se non trovato
    return jsonify({
        'id': user.id,
        'username': user.username,
        'email': user.email
    })

# GET con filtri
@app.route('/api/users/active', methods=['GET'])
def get_active_users():
    # SELECT * FROM users WHERE is_active = True
    active_users = User.query.filter_by(is_active=True).all()
    
    # Query complessa con AND/OR
    users = User.query.filter(
        (User.is_active == True) & 
        (User.created_at >= '2024-01-01')
    ).order_by(User.created_at.desc()).limit(10).all()

UPDATE – Modificare Dati

@app.route('/api/users/<int:user_id>', methods=['PUT'])
def update_user(user_id):
    user = User.query.get_or_404(user_id)
    data = request.json
    
    # Aggiorna i campi
    if 'username' in data:
        user.username = data['username']
    if 'email' in data:
        user.email = data['email']
    
    # Salva le modifiche
    db.session.commit()
    
    return jsonify({'message': 'Utente aggiornato'})

# Update con metodo più Pythonico
user = User.query.get(1)
user.username = "NuovoUsername"
db.session.commit()

DELETE – Eliminare Dati

@app.route('/api/users/<int:user_id>', methods=['DELETE'])
def delete_user(user_id):
    user = User.query.get_or_404(user_id)
    
    # Elimina l'utente
    db.session.delete(user)
    db.session.commit()
    
    return jsonify({'message': 'Utente eliminato'}), 204

# Soft delete (marcare come eliminato invece di cancellare)
@app.route('/api/users/<int:user_id>/deactivate', methods=['POST'])
def deactivate_user(user_id):
    user = User.query.get_or_404(user_id)
    user.is_active = False
    db.session.commit()
    return jsonify({'message': 'Utente disattivato'})

🔗 Relazioni tra Tabelle

Le relazioni sono la potenza dei database relazionali.

Relazione 1-a-Molti

Un utente può scrivere molti post:

# In User model
posts = db.relationship('Post', backref='author', lazy=True)

# In Post model
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

# Uso
user = User.query.get(1)
user_posts = user.posts  # Tutti i post di quell'utente

post = Post.query.get(1)
post_author = post.author  # L'autore di quel post

Relazione Molti-a-Molti

Un post può avere molti tag, un tag può essere in molti post:

# Tabella di join
post_tags = db.Table('post_tags',
    db.Column('post_id', db.Integer, db.ForeignKey('posts.id')),
    db.Column('tag_id', db.Integer, db.ForeignKey('tags.id'))
)

# In Post model
tags = db.relationship('Tag', secondary=post_tags, back_populates='posts')

# In Tag model  
posts = db.relationship('Post', secondary=post_tags, back_populates='tags')

# Uso
post = Post.query.get(1)
post.tags.append(Tag(name='Python'))  # Aggiungi tag
db.session.commit()

tag = Tag.query.get(1)
posts_with_tag = tag.posts  # Tutti i post con quel tag

Query con JOIN

# Post con i loro autori
posts_with_authors = db.session.query(Post, User)\\
    .join(User, Post.user_id == User.id)\\
    .all()

# Per ogni post: post[0] è il Post, post[1] è l'User

# Tag con conteggio post
from sqlalchemy import func

tags_with_count = db.session.query(
    Tag.name,
    func.count(Post.id).label('post_count')
).join(
    post_tags, Tag.id == post_tags.c.tag_id
).join(
    Post, Post.id == post_tags.c.post_id
).group_by(Tag.id).all()

🐚 Flask Shell – Console Interattiva

La shell di Flask è perfetta per testare query e manipolare dati.

Terminale – Avvia Flask Shell

# Attiva l'ambiente virtuale prima
source venv/bin/activate  # Linux/Mac
venv\Scripts\activate     # Windows

# Avvia la shell
flask shell

Flask Shell – Comandi Utili

>>> # Importa i modelli
>>> from app import db
>>> from models import User, Post

>>> # Crea un utente
>>> user = User(username='mario', email='mario@example.com')
>>> db.session.add(user)
>>> db.session.commit()

>>> # Query base
>>> users = User.query.all()
>>> user = User.query.get(1)
>>> user = User.query.filter_by(username='mario').first()

>>> # Aggiorna
>>> user.email = 'nuova@email.com'
>>> db.session.commit()

>>> # Elimina
>>> db.session.delete(user)
>>> db.session.commit()

>>> # Query complesse
>>> from sqlalchemy import or_
>>> users = User.query.filter(
...     or_(User.username.like('%mario%'), User.email.like('%example%'))
... ).order_by(User.created_at.desc()).limit(5).all()

>>> # Conta records
>>> user_count = User.query.count()
>>> active_users = User.query.filter_by(is_active=True).count()

🏆 Best Practices Database

✅ Cosa Fare

Sempre usare migrazioni per cambiamenti allo schema
Usare sessioni correttamente: commit dopo modifiche, rollback in caso di errore
Indici per colonne usate spesso in WHERE
Soft delete invece di eliminazioni fisiche quando possibile
Backup regolari del database

❌ Cosa Non Fare

Non modificare manualmente il database in produzione
Evitare N+1 query problem: usa .joinedload() per eager loading
Non usare SELECT * quando hai bisogno di colonne specifiche
Non esporre ID sequenziali in URL pubblici
Non memorizzare password in chiaro – usa hashing

💪 Esercizi Pratici

Esercizio 1
Facile

📝 Blog Semplice

Crea un sistema di blog con:

Modelli: User, Post, Comment
Relazioni: User 1→N Post, Post 1→N Comment
CRUD API per tutte le entità
Filtri: post per utente, commenti per post
Paginazione: 10 post per pagina

Esercizio 2
Medio

🛒 E-commerce

Crea un database per e-commerce con:

Modelli: Product, Category, Order, OrderItem, Customer
Relazioni:
- Product N→N Category
- Customer 1→N Order
- Order 1→N OrderItem
- OrderItem N→1 Product
Query complesse:
- Prodotti più venduti
- Clienti più attivi
- Fatturato per mese
- Prodotti in esaurimento (quantità < 10)

Esercizio 3
Difficile

🏆 Sistema di Prenotazioni

Crea un sistema di prenotazioni per hotel/ristorante con:

Modelli complessi: Room, Booking, Customer, Payment, Review
Validazioni:
- Non sovrapporre prenotazioni stessa stanza
- Check-out dopo check-in
- Capacità stanza vs numero ospiti
Query avanzate:
- Stanze disponibili in date specifiche
- Occupazione media per mese
- Clienti fedeli (più di 5 prenotazioni)
- Stanze più popolari
Transazioni: Atomicità per creazione prenotazione + pagamento

✅ Modulo 6 Completato!

Ora sai gestire database SQL in modo professionale. Pronto per proteggere la tua app con l’autenticazione?

MODULO 5

⬅️ Form e Input

ORA SEI QUI

🗄️ Modulo 6
Database SQL

MODULO 7

Autenticazione ➡️

🎯 Cosa Aspettarti nel Prossimo Modulo

🔐

Login/Logout

Sistemi di autenticazione sicuri

🔑

Hashing Password

Sicurezza password con bcrypt

👥

Sessioni & Ruoli

Gestione utenti e permessi

💪 Completa gli Esercizi

🚀 Vai al Modulo 7

🏆 Progetto Finale