第 28 天：資料庫操作與 SQLite 的應用

課程目標：

今天我們將介紹如何在 Python 中進行資料庫操作，並使用內建的 SQLite 來處理數據。資料庫是儲存和管理大量資料的工具，SQLite 是一種輕量級的資料庫，非常適合小型應用程式或開發環境中使用。

1. 什麼是 SQLite？

SQLite 是一種內建於 Python 中的關聯式資料庫管理系統。它是輕量級的，不需要額外安裝伺服器軟體。SQLite 儲存的數據以檔案的形式存在，非常適合用於桌面應用和小型應用程式。

2. 如何使用 Python 與 SQLite 進行互動？

Python 提供了內建的 sqlite3 模組來與 SQLite 資料庫互動。通過 sqlite3，我們可以執行 SQL 查詢，創建、插入、更新和刪除資料庫中的數據。

範例：建立資料庫與表格

import sqlite3

# 連接到一個 SQLite 資料庫（如果不存在，則會自動創建）
conn = sqlite3.connect('example.db')

# 創建一個游標對象來執行 SQL 語句
cursor = conn.cursor()

# 創建一個表格
cursor.execute('''CREATE TABLE users (
                  id INTEGER PRIMARY KEY,
                  name TEXT,
                  age INTEGER
                  )''')

# 提交改變
conn.commit()

# 關閉連接
conn.close()

3. 插入數據

使用 INSERT INTO 語句來插入數據。可以通過參數化的 SQL 語句來避免 SQL 注入攻擊。

範例：插入數據

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 插入數據
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 25))

# 提交改變
conn.commit()
conn.close()

4. 查詢數據

可以使用 SELECT 語句來查詢數據，並且可以選擇只提取某些欄位或特定的數據條件。

範例：查詢數據

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 查詢數據
cursor.execute("SELECT * FROM users")

# 取得所有數據
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

5. 更新與刪除數據

• UPDATE 語句用於修改表格中的現有數據。
• DELETE 語句用於刪除表格中的數據。

範例：更新與刪除數據

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 更新數據
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (35, 'Alice'))

# 刪除數據
cursor.execute("DELETE FROM users WHERE name = ?", ('Bob',))

conn.commit()
conn.close()

6. 使用上下文管理器進行資料庫操作

SQLite 連接可以通過 Python 的上下文管理器來自動關閉，確保資源被正確釋放。

範例：使用上下文管理器

import sqlite3

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()
    for row in rows:
        print(row)

今日實作練習：

1. 建立與操作資料庫： 使用 SQLite 建立一個簡單的學生資料庫，包括插入學生數據、更新年齡、刪除特定的學生記錄，最後列出所有學生的數據。
2. 查詢與過濾： 查詢所有年齡大於 20 歲的學生，並列出他們的姓名與年齡。

今日實作練習：使用 SQLite 操作學生資料庫

以下是如何建立和操作一個簡單的學生資料庫，包括插入、更新、刪除數據，以及進行查詢和過濾的實作範例。

步驟一：建立 students 資料表

import sqlite3

# 連接資料庫（如果資料庫不存在，則會自動建立）
conn = sqlite3.connect('students.db')
cursor = conn.cursor()

# 建立 students 表格，包含 id, name, age 欄位
cursor.execute('''
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    )
''')

conn.commit()  # 確保更改被保存

步驟二：插入學生數據

# 插入學生數據
students_data = [
    ("Alice", 22),
    ("Bob", 19),
    ("Charlie", 25)
]

cursor.executemany('INSERT INTO students (name, age) VALUES (?, ?)', students_data)

conn.commit()  # 保存更改

步驟三：更新特定學生的年齡

# 更新特定學生（如 Alice）的年齡
cursor.execute('UPDATE students SET age = ? WHERE name = ?', (23, "Alice"))

conn.commit()  # 保存更改

步驟四：刪除特定的學生記錄

# 刪除特定學生（如 Bob）的記錄
cursor.execute('DELETE FROM students WHERE name = ?', ("Bob",))

conn.commit()  # 保存更改

步驟五：查詢所有學生的數據

# 查詢並列出所有學生的數據
cursor.execute('SELECT * FROM students')
all_students = cursor.fetchall()

for student in all_students:
    print(student)  # 每個學生記錄將顯示 (id, name, age)

步驟六：查詢年齡大於 20 歲的學生

# 查詢年齡大於 20 歲的學生，並列出他們的姓名和年齡
cursor.execute('SELECT name, age FROM students WHERE age > 20')
students_above_20 = cursor.fetchall()

for student in students_above_20:
    print(student)  # 每個學生記錄將顯示 (name, age)

步驟七：關閉資料庫連接

# 關閉資料庫連接
conn.close()

程式運行結果

1. 所有學生的數據會顯示 (id, name, age)。
2. 所有年齡大於 20 歲的學生會顯示 (name, age)。

這樣就完成了一個簡單的學生資料庫操作，包括插入、更新、刪除、查詢及過濾數據！

小結：

今天我們學習了 Python 與 SQLite 資料庫的基本操作。資料庫是管理大規模數據的重要工具，而 SQLite 提供了便捷的方式來儲存和操作資料。透過今天的學習，你應該能夠使用 Python 進行基本的資料庫操作，包括建立表格、插入、查詢、更新和刪除數據。