python 自帶 SQLite 數據庫，SQLite 支持多個用戶同時讀，但寫操作只能一個時間一個用戶，數據庫文件單一。

以下代碼實現數據庫 CURD 操作，books 的數據內容為:
book_name book_author finished_read_or_not,

首先導入數據庫 package:

import sqlite3

1 創建表

def create_book_table():
connection = sqlite3.connect('data.db')
cursor = connection.cursor()
# name as primary key cannot be duplicated
# cursor.execute('CREATE TABLE books(name text primary key, author text, read integer)')
cursor.execute('CREATE TABLE IF NOT EXISTS books(name text primary key, author text, read integer)')
connection.commit()
connection.close()

2 列出全部記錄

def get_all_books():
connection = sqlite3.connect('data.db')
cursor = connection.cursor()
cursor.execute("SELECT * FROM books")
# cursor.fetchall() return a list of tuples: [(name,author,read),(name,author,read)]
# to support the interface of app.py, change tuples into dicts
books = [{
'name': row[0], 'author': row[1], 'read': row[2]} for row in cursor.fetchall()]
# connection.commit() # Nothing to save to the disk, no need commit
connection.close()
return books

3 添加記錄

(name, author) 是 tuple，這種寫法是為了防止 sql injection attack:

def add_book(name, author):
connection = sqlite3.connect('data.db')
cursor = connection.cursor()
# 不安全的寫法，容易被攻擊，例如 author： ", 0); DROP TABLE books;
# f'INSERT INTO books VALUES("{name}", "", 0); DROP TABLE books;", 0)'
# cursor.execute(f'INSERT INTO books VALUES("{name}", "{author}", 0)')
# (name, author) is a tuple
cursor.execute(f'INSERT INTO books VALUES(?, ?, 0)', (name, author))
connection.commit()
connection.close()

4 更新記錄

(name,) 同樣是 tuple，不能寫成 (name), 也不能是 name

def mark_book_as_read(name):
connection = sqlite3.connect("data.db")
cursor = connection.cursor()
cursor.execute('UPDATE books SET read=1 WHERE name=?', (name,))
connection.commit()
connection.close()

5 刪除記錄

def delete_book(name):
connection = sqlite3.connect("data.db")
cursor = connection.cursor()
cursor.execute('DELETE FROM books WHERE name=?', (name,))
connection.commit()
connection.close()

這種寫法同樣適用於數據庫 MySQL 和 PostgreSQL，為了避免上面的數據庫的連接和斷開操作的重復代碼，可以實現自定義的 context manager 即上下文管理器，類似於 with ... open ...