Hello World

目标

通过 sqlite3 CLI 和 Python 标准库，完成 SQLite 数据库创建、表操作、CRUD，体验 SQLite "零配置"的优势。

环境准备

sqlite3 --version
python3 -c "import sqlite3; print('OK')"  # 确认自带

第一步：CLI 方式（最简）

# 创建数据库文件并进入交互模式
sqlite3 hello.db

-- 创建表
CREATE TABLE todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    task TEXT NOT NULL,
    done BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 插入
INSERT INTO todos (task) VALUES
    ('完成数据库作业'),
    ('复习SQL语法'),
    ('写实验报告');

-- 查询
SELECT * FROM todos;
.mode column          -- 格式化输出
.headers on
SELECT * FROM todos WHERE done = 0;

-- 更新
UPDATE todos SET done = 1 WHERE id = 1;

-- 删除
DELETE FROM todos WHERE id = 3;

SELECT * FROM todos;
.quit

第二步：Python 方式

import sqlite3

# 连接数据库（不存在则自动创建）
conn = sqlite3.connect("hello.db")
cursor = conn.cursor()

# 创建表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        score REAL,
        grade TEXT GENERATED ALWAYS AS (
            CASE
                WHEN score >= 90 THEN 'A'
                WHEN score >= 80 THEN 'B'
                WHEN score >= 70 THEN 'C'
                ELSE 'F'
            END
        ) VIRTUAL
    )
""")

# 批量插入
students = [
    ("Alice", 95.5),
    ("Bob", 82.0),
    ("Charlie", 76.5),
]
cursor.executemany(
    "INSERT INTO students (name, score) VALUES (?, ?)",
    students
)
conn.commit()

# 查询
cursor.execute("SELECT * FROM students ORDER BY score DESC")
for row in cursor.fetchall():
    print(f"ID={row[0]} | {row[1]} | {row[2]}分 | {row[3]}")

# 统计
cursor.execute("SELECT grade, COUNT(*) FROM students GROUP BY grade")
for grade, count in cursor.fetchall():
    print(f"Grade {grade}: {count}人")

cursor.close()
conn.close()

第三步：Python 上下文管理器（推荐）

import sqlite3

with sqlite3.connect("hello.db") as conn:
    conn.row_factory = sqlite3.Row  # 字典式访问
    rows = conn.execute("SELECT * FROM students").fetchall()
    for r in rows:
        print(f"{r['name']}: {r['score']}分 → {r['grade']}")

预期输出

# CLI
id  task        done  created_at
--  ----------  ----  -------------------
1   完成数据库作业  1    2025-01-01 10:00:00
2   复习SQL语法     0    2025-01-01 10:00:00

# Python
ID=1 | Alice | 95.5分 | A
ID=2 | Bob | 82.0分 | B
ID=3 | Charlie | 76.5分 | C
Grade A: 1人
Grade B: 1人
Grade C: 1人