# ===== CodeLab: Python 数据库操作入门 =====
# 来源: https://aoerliang.dpdns.org/articles/python-databases
# 以下代码片段按文章出现顺序拼接, 共 7 段

# ----- 片段 1 (python) -----
import sqlite3

conn = sqlite3.connect("app.db")   # 文件不存在会自动创建
cur = conn.cursor()
cur.execute("SELECT sqlite_version()")
print(cur.fetchone())   # ('3.x.x',)
conn.close()

# ----- 片段 2 (python) -----
import sqlite3

conn = sqlite3.connect("app.db")
cur = conn.cursor()
cur.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER
    )
""")
cur.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Ada", 18))
conn.commit()      # 别忘了提交!
conn.close()

# ----- 片段 3 (python) -----
conn = sqlite3.connect("app.db")
cur = conn.cursor()

# 参数化查询,返回所有行
rows = cur.execute("SELECT * FROM users WHERE age >= ?", (18,)).fetchall()

# 也可以按字典传参
cur.execute(
    "INSERT INTO users (name, age) VALUES (:name, :age)",
    {"name": "Bob", "age": 20},
)
conn.commit()
conn.close()

# ----- 片段 4 (python) -----
conn = sqlite3.connect("app.db")
cur = conn.cursor()

cur.execute("UPDATE users SET age = ? WHERE name = ?", (19, "Ada"))
cur.execute("DELETE FROM users WHERE age < ?", (18,))
print("影响行数:", cur.rowcount)
conn.commit()
conn.close()

# ----- 片段 5 (python) -----
import sqlite3

conn = sqlite3.connect("app.db")
try:
    with conn:                       # 成功提交,异常回滚
        conn.execute("INSERT INTO users (name, age) VALUES (?, ?)",
                     ("Carol", 25))
        conn.execute("INSERT INTO users (name, age) VALUES (?, ?)",
                     ("Dave", 30))
except sqlite3.Error as e:
    print("数据库错误:", e)
finally:
    conn.close()

# ----- 片段 6 (bash) -----
pip install pymysql psycopg2-binary

# ----- 片段 7 (python) -----
import pymysql

conn = pymysql.connect(
    host="127.0.0.1", user="root", password="xxx",
    database="app", charset="utf8mb4",
)
cur = conn.cursor()
cur.execute("SELECT COUNT(*) FROM users")
print(cur.fetchone())
conn.close()