Application Development — Phát triển ứng dụng với Database

Application Development — Phát triển ứng dụng với Database

Ứng dụng thực tế kết nối database qua database connector, không phải MySQL Workbench trực tiếp.

1. Kiến trúc 3-Tier

Ba tầng: Presentation (UI: Web/Desktop/Mobile) ↔ Application (Business logic: Python/FastAPI) ↔ Data (MySQL database).

2. Kết nối Python với MySQL

import mysql.connector

db = mysql.connector.connect(
    host='localhost', user='app_user',
    password='securePass123', database='archery_db'
)
cursor = db.cursor()

SELECT

cursor.execute('SELECT ArcherID, ArcherName FROM Archer')
archers = cursor.fetchall()
for a in archers:
    print(a[0], a[1])

INSERT (Prepared Statement — an toan)

query = 'INSERT INTO Score (ArcherID, RoundID, TotalScore) VALUES (%s, %s, %s)'
cursor.execute(query, (archer_id, round_id, total))
db.commit()

3. FastAPI REST API

from fastapi import FastAPI
app = FastAPI()

@app.get('/archers')
def get_archers():
    cursor.execute('SELECT * FROM Archer')
    return cursor.fetchall()

@app.post('/scores')
def add_score(data: dict):
    cursor.execute('INSERT INTO Score VALUES (%s,%s,%s)',
        (data['archer_id'], data['round_id'], data['total']))
    db.commit()
    return {'id': cursor.lastrowid}

4. Use Case: Archery Score App

Ứng dung ghi diem ban cung (vi du tu bai giang):

• Chon Archer tu danh sach (SELECT FROM Archer)
• Chon Round & Range (SELECT JOIN)
• Nhap diem tung mui ten (6 arrows/end)
• Luu vao database (INSERT + COMMIT)
• Xem Personal Bests: MAX(TotalScore) GROUP BY ArcherID

Personal Best Query

SELECT a.ArcherName, a.Equipment,
       MAX(s.TotalScore) AS PersonalBest,
       MAX(s.ScoreDate)  AS DateAchieved
FROM Archer a
INNER JOIN Score s ON a.ArcherID = s.ArcherID
GROUP BY a.ArcherID, a.ArcherName, a.Equipment
ORDER BY PersonalBest DESC;

5. Client-side Validation

function validateArcherID() {
  var id = document.getElementById('archerid').value;
  var regex = /^[0-9]+$/;
  if (!regex.test(id)) { alert('ArcherID must be numeric.'); }
}

6. ORM — SQLAlchemy

from sqlalchemy.orm import Session, declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class Archer(Base):
    __tablename__ = 'archer'
    archer_id = Column(Integer, primary_key=True)
    name      = Column(String(100))

with Session(engine) as session:
    archers = session.query(Archer).all()