Python整合SQLite搭建一个轻量级账本应用

前言

一个账本应用，剥开界面看只有两层：一笔一笔的记录（事件流）和各种角度的汇总（视图）。加一条流水、按月份看收支、按分类看花了多少——需求就这么点，但里面藏着一个必须先做对的决策。

第一个决策：金额绝对不能用float存。这不是洁癖。Python 里0.1 + 0.2的结果是0.30000000000000004，不是0.3；把一串浮点数加起来，误差会累积，最终出现「账目差一分钱」这种无法解释的现象。正确做法是用整数存「分」（或者用decimal.Decimal）。SQLite 支持INTEGER，用它存分，所有加减都是精确整数运算，汇总时再除以 100 展示。

第二个决策：SQLite 正好是这个场景的对口选择。账本是典型的单机、单人、读多写少、需要数据随身携带的应用——正是 SQLite 的设计目标。不用装服务、不用配账号，整个账本就是一个文件，拷走就能用。

本文从表结构开始，一路写到能跑的命令行账本，包含录入、按月汇总、按分类汇总、删除和导出 CSV。所有 SQL 都参数化。

一、表设计

CREATE TABLE IF NOT EXISTS entry (
id INTEGER PRIMARY KEY AUTOINCREMENT,
occurred_on TEXT NOT NULL, -- 'YYYY-MM-DD'
kind TEXT NOT NULL CHECK (kind IN ('income', 'expense')),
category TEXT NOT NULL,
amount_cents INTEGER NOT NULL CHECK (amount_cents > 0),
note TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_entry_date ON entry(occurred_on);
CREATE INDEX IF NOT EXISTS idx_entry_category ON entry(category);

设计说明：

• 
  
• amount_cents存正整数，方向由kind决定。另一种常见设计是「有符号金额」：收入为正、支出为负。两种都可以，但「正数 + 类型字段」更不容易在聚合时写错条件，而且CHECK (amount_cents > 0)能从数据库层挡住「金额为负或为零」的脏数据。
• 
  
• occurred_on用TEXT存 ISO 8601 的'YYYY-MM-DD'。SQLite 没有独立的日期类型，ISO 格式的字符串按字典序排序就等于按时间排序，而且能被strftime()直接解析。这是 SQLite 里处理日期的通行做法。
• 
  
• CHECK约束不是装饰。它挡住的是「应用层漏了校验」的情况，比在 Python 里写if更可靠——因为任何绕过应用的写入路径都会被执行。
• 
  
• 索引加在occurred_on和category上，因为这两个是汇总和筛选的主要条件。索引会让写入略慢一点，对账本这种写少读多的场景是划算的。
• 
  

二、金额的输入与展示

金额在「用户输入的元」和「数据库存的分」之间来回转换，这个转换必须精确。

# 适用于 Python 3.8+
from decimal import Decimal, ROUND_HALF_UP, InvalidOperation


def yuan_to_cents(text):
"""把用户输入的元转成整数分。'12.34' -> 1234"""
text = (text or "").strip()
if not text:
raise ValueError("金额不能为空")
try:
value = Decimal(text)
except InvalidOperation:
raise ValueError(f"金额格式不正确: {text!r}")
if value <= 0:
raise ValueError("金额必须大于 0")
# 乘以 100 后用 HALF_UP 四舍五入到整数，避免 float 的二进制误差
return int((value * 100).quantize(Decimal("1"), rounding=ROUND_HALF_UP))


def cents_to_yuan(cents):
"""整数分转成展示用的两位小数字符串。1234 -> '12.34'"""
return f"{Decimal(cents) / 100:.2f}"

为什么用Decimal而不是float？因为Decimal("12.34")是精确的十进制表示，float("12.34")不是。用float做「元转分」，int(12.34 * 100)在某些数值上会得到1233而不是1234——这类错误极难排查，因为它在绝大多数金额上都「碰巧对」。

三、数据访问层

# 适用于 Python 3.8+
import sqlite3
from datetime import date

SCHEMA = """
CREATE TABLE IF NOT EXISTS entry (
id INTEGER PRIMARY KEY AUTOINCREMENT,
occurred_on TEXT NOT NULL,
kind TEXT NOT NULL CHECK (kind IN ('income', 'expense')),
category TEXT NOT NULL,
amount_cents INTEGER NOT NULL CHECK (amount_cents > 0),
note TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_entry_date ON entry(occurred_on);
CREATE INDEX IF NOT EXISTS idx_entry_category ON entry(category);
"""


class Ledger:
def __init__(self, path):
self.con = sqlite3.connect(path)
self.con.row_factory = sqlite3.Row
self.con.executescript(SCHEMA)
self.con.commit()

def close(self):
self.con.close()

def add(self, occurred_on, kind, category, amount_cents, note=""):
with self.con:
cur = self.con.execute(
"INSERT INTO entry (occurred_on, kind, category, amount_cents, note) "
"VALUES (?, ?, ?, ?, ?)",
(occurred_on, kind, category, amount_cents, note),
)
return cur.lastrowid

def remove(self, entry_id):
with self.con:
cur = self.con.execute("DELETE FROM entry WHERE id = ?", (entry_id,))
return cur.rowcount

def recent(self, limit=20):
return self.con.execute(
"SELECT id, occurred_on, kind, category, amount_cents, note "
"FROM entry ORDER BY occurred_on DESC, id DESC LIMIT ?",
(limit,),
).fetchall()

def monthly(self, year=None):
"""按月汇总收入与支出。"""
sql = """
SELECT strftime('%Y-%m', occurred_on) AS month,
SUM(CASE WHEN kind = 'income' THEN amount_cents ELSE 0 END) AS income_cents,
SUM(CASE WHEN kind = 'expense' THEN amount_cents ELSE 0 END) AS expense_cents
FROM entry
"""
params = ()
if year is not None:
sql += " WHERE occurred_on LIKE ?"
params = (f"{year}-%",)
sql += " GROUP BY month ORDER BY month DESC"
return self.con.execute(sql, params).fetchall()

def by_category(self, month):
"""某个月的分类明细，按金额从大到小。"""
return self.con.execute(
"SELECT kind, category, SUM(amount_cents) AS total_cents "
"FROM entry WHERE occurred_on LIKE ? "
"GROUP BY kind, category "
"ORDER BY total_cents DESC",
(f"{month}-%",),
).fetchall()

几个细节值得停一下：

• 
  
• with self.con:包住每个写操作，退出时自动提交、异常时自动回滚。但要记住它不关闭连接——连接的生命周期由close()负责。
• 
  
• ORDER BY occurred_on DESC, id DESC：同一天有多笔时，按 id 倒序保证最新的在最前面，不会因为日期相同而出现随机顺序。
• 
  
• LIKE '2026-%'：因为日期是 ISO 文本，这个前缀匹配就等价于「按月份筛选」。百分号在参数值里，SQL 本身是纯占位符，所以依然安全。月份如果来自用户输入，必须先校验格式（比如用datetime.strptime(month, "%Y-%m")试解析一次），把不合法输入挡在进数据库之前。
• 
  
• strftime('%Y-%m', occurred_on)依赖occurred_on确实是 ISO 格式；如果表里有历史脏数据（比如'2026/10/07'），strftime会返回NULL，这些行会被分到NULL组里。入库时就统一格式，别指望查询时兜底。
• 
  

四、命令行界面

# 适用于 Python 3.8+
import csv
import sys
from datetime import date

KINDS = {"1": "income", "2": "expense"}


def show_recent(ledger):
rows = ledger.recent()
if not rows:
print("（还没有任何记录）")
return
print(f"{'id':<5}{'日期':<12}{'类型':<6}{'分类':<10}{'金额':>10} 备注")
for r in rows:
mark = "+" if r["kind"] == "income" else "-"
print(f"{r['id']:<5}{r['occurred_on']:<12}{r['kind']:<6}"
f"{r['category']:<10}{mark}{cents_to_yuan(r['amount_cents']):>9} {r['note']}")


def show_monthly(ledger, year):
rows = ledger.monthly(year)
if not rows:
print("（该年份没有记录）")
return
print(f"{'月份':<10}{'收入':>12}{'支出':>12}{'结余':>12}")
for r in rows:
income = r["income_cents"] or 0
expense = r["expense_cents"] or 0
print(f"{r['month']:<10}{cents_to_yuan(income):>12}"
f"{cents_to_yuan(expense):>12}{cents_to_yuan(income - expense):>12}")


def show_category(ledger, month):
rows = ledger.by_category(month)
for r in rows:
print(f" {r['kind']:<8}{r['category']:<12}{cents_to_yuan(r['total_cents']):>10}")


def export_csv(ledger, filename):
rows = ledger.recent(limit=10 ** 9)
# 显式指定 encoding，避免 Windows 上用默认编码写出乱码
with open(filename, "w", encoding="utf-8", newline="") as fp:
writer = csv.writer(fp)
writer.writerow(["id", "日期", "类型", "分类", "金额", "备注"])
for r in rows:
mark = "" if r["kind"] == "income" else "-"
writer.writerow([r["id"], r["occurred_on"], r["kind"],
r["category"], mark + cents_to_yuan(r["amount_cents"]),
r["note"]])
print(f"已导出 {len(rows)} 条记录到 {filename}")


def main():
ledger = Ledger("ledger.db")
try:
while True:
print("\n1 记一笔 2 最近记录 3 按月汇总 4 分类明细 5 删除 6 导出CSV 0 退出")
choice = input("请选择: ").strip()

if choice == "0":
break
elif choice == "1":
kind = KINDS.get(input("类型(1收入/2支出): ").strip())
if kind is None:
print("类型无效")
continue
category = input("分类: ").strip()
if not category:
print("分类不能为空")
continue
try:
amount_cents = yuan_to_cents(input("金额(元): "))
except ValueError as exc:
print(f"[输入有误] {exc}")
continue
day = input(f"日期(YYYY-MM-DD，回车用今天 {date.today()}): ").strip() or str(date.today())
note = input("备注(可空): ").strip()
new_id = ledger.add(day, kind, category, amount_cents, note)
print(f"已记录，id = {new_id}")
elif choice == "2":
show_recent(ledger)
elif choice == "3":
show_monthly(ledger, input("年份(如 2026，回车表示全部): ").strip() or None)
elif choice == "4":
show_category(ledger, input("月份(YYYY-MM): ").strip())
elif choice == "5":
try:
entry_id = int(input("要删除的 id: ").strip())
except ValueError:
print("[输入有误] id 必须是整数")
continue
print("已删除" if ledger.remove(entry_id) else "未找到该 id")
elif choice == "6":
export_csv(ledger, input("导出文件名: ").strip() or "ledger.csv")
else:
print("无效选项")
except (KeyboardInterrupt, EOFError):
print("\n再见")
finally:
ledger.close()


if __name__ == "__main__":
main()

预期输出长这样：

1 记一笔 2 最近记录 3 按月汇总 4 分类明细 5 删除 6 导出CSV 0 退出
请选择: 3
月份 收入 支出 结余
2026-10 8000.00 1234.56 6765.44
2026-09 8000.00 2010.00 5990.00

注意int(input(...))只在删除那条分支里用了，而且包在try里——任何来自input()的值都是字符串，直接参与算术或当主键用之前一定要转换并处理转换失败。

五、还能加什么

这套骨架可以按需要往外长：加预算提醒（在by_category上比一个预算值）、加recurring表记周期账单、给entry加account列做多账户。

要提醒的是：「多账户之间的转账」是一对记录（一出一进），必须放在同一个事务里。用with self.con:把两条插入包起来，就是这个账本应用里第一个真正需要事务的地方。

常见坑点

1. 用float存金额

❌amount REAL，加总之后出现0.30000000000000004这类尾差，账对不上。

✅ 用INTEGER存「分」，或用decimal.Decimal；只在展示时转成两位小数。

2. 用float做「元转分」

❌int(12.34 * 100)——浮点误差可能算出1233。

✅int((Decimal("12.34") * 100).quantize(Decimal("1"), rounding=ROUND_HALF_UP))。

3. 拼接 SQL 构造筛选条件

❌sql = "SELECT ... WHERE occurred_on LIKE '" + month + "-%'"——月份来自输入，就是注入入口。

✅sql = "SELECT ... WHERE occurred_on LIKE ?"，参数传(f"{month}-%",)；月份本身再用strptime校验一次格式。

4. 拿input()的结果直接算

❌amount = input("金额: "); total = total + amount * 100——input()返回字符串，"12" * 100是重复字符串，不是乘法。

✅ 先转换再校验：amount_cents = yuan_to_cents(input("金额: "))。

5. 汇总时忘了处理NULL

❌ 某个月只有支出没有收入时，SUM(CASE WHEN kind='income' THEN ... END)返回NULL，直接参与算术会抛TypeError。

✅ 用r["income_cents"] or 0兜底，或者把CASE的ELSE分支写成ELSE 0（本例就是这么做的）。

6. 日期格式不统一

❌ 有的记录存'2026-10-07'，有的存'2026/10/07'——strftime对后者返回NULL，汇总时这些记录全被归到NULL组。

✅ 入库前统一成 ISO 格式；可以在表上加CHECK (occurred_on GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]')从数据层兜住。

7. 导出 CSV 不写encoding和newline

❌open("out.csv", "w")——Windows 上默认编码写出中文会乱码，csv模块还会因为换行处理产生多余空行。

✅open(filename, "w", encoding="utf-8", newline="")。

8. 用with con:之后就以为连接关了

❌with Ledger("ledger.db") as lg:（在Ledger上加了__enter__/__exit__只管事务），程序退出时连接没关，Windows 上文件还被占用。

✅try: ... finally: ledger.close()，事务提交和连接关闭分开处理。

总结

账本应用是个很好的练手题材：它足够小，能一次写完；又足够真实，逼你面对精度、事务、输入校验这三个数据库编程的核心问题。把金额用整数分存下来这件事，比多加十个功能更能体现你懂不懂数据。