Python+MySQL制作一个每日记账小程序
·
我来教你用Python+MySQL制作一个实用的每日记账小程序。
第一步：创建数据库和表
CREATE DATABASE IF NOT EXISTS daily_account;
USE daily_account;
-- 账单分类表
CREATE TABLE IF NOT EXISTS categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
type ENUM('income', 'expense') NOT NULL COMMENT '收入/支出',
icon VARCHAR(10) DEFAULT '💰'
);
-- 账单记录表
CREATE TABLE IF NOT EXISTS transactions (
id INT AUTO_INCREMENT PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL COMMENT '金额',
category_id INT NOT NULL COMMENT '分类ID',
note VARCHAR(500) COMMENT '备注',
transaction_date DATE NOT NULL COMMENT '日期',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(id)
);
-- 预算表
CREATE TABLE IF NOT EXISTS budgets (
id INT AUTO_INCREMENT PRIMARY KEY,
month VARCHAR(7) NOT NULL COMMENT '月份 YYYY-MM',
category_id INT NOT NULL COMMENT '分类ID',
budget_amount DECIMAL(10,2) NOT NULL COMMENT '预算金额',
UNIQUE KEY unique_month_category (month, category_id),
FOREIGN KEY (category_id) REFERENCES categories(id)
);
-- 插入默认分类
INSERT INTO categories (name, type, icon) VALUES
-- 支出分类
('餐饮', 'expense', '🍜'),
('交通', 'expense', '🚗'),
('购物', 'expense', '🛍️'),
('住房', 'expense', '🏠'),
('娱乐', 'expense', '🎮'),
('医疗', 'expense', '💊'),
('教育', 'expense', '📚'),
('其他支出', 'expense', '📦'),
-- 收入分类
('工资', 'income', '💼'),
('兼职', 'income', '💻'),
('投资', 'income', '📈'),
('红包', 'income', '🧧'),
('其他收入', 'income', '💰');完整代码实现
import pymysql
from datetime import datetime, date
import calendar
from prettytable import PrettyTable
import matplotlib.pyplot as plt
import matplotlib
matplotlib.rcParams['font.sans-serif'] = ['SimHei', 'DejaVu Sans']
matplotlib.rcParams['axes.unicode_minus'] = False
class DailyAccount:
def __init__(self):
"""初始化数据库连接"""
try:
self.connection = pymysql.connect(
host='localhost',
user='root',
password='your_password', # 修改为你的密码
database='daily_account',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
self.cursor = self.connection.cursor()
print("✅ 记账本已打开")
except Exception as e:
print(f"❌ 连接失败: {e}")
exit()
def get_categories(self, trans_type=None):
"""获取分类列表"""
if trans_type:
sql = "SELECT * FROM categories WHERE type = %s ORDER BY id"
self.cursor.execute(sql, (trans_type,))
else:
sql = "SELECT * FROM categories ORDER BY type, id"
self.cursor.execute(sql)
return self.cursor.fetchall()
def add_transaction(self):
"""添加记账记录"""
print("\n--- 📝 记一笔 ---")
# 选择类型
print("\n类型:")
print("1. 💸 支出")
print("2. 💰 收入")
type_choice = input("请选择 (1/2): ").strip()
trans_type = 'expense' if type_choice == '1' else 'income'
# 选择分类
categories = self.get_categories(trans_type)
print(f"\n{'支出' if trans_type == 'expense' else '收入'}分类:")
for cat in categories:
print(f"{cat['id']}. {cat['icon']} {cat['name']}")
while True:
try:
cat_id = int(input("请选择分类编号: ").strip())
if any(cat['id'] == cat_id for cat in categories):
break
print("❌ 无效的分类编号")
except ValueError:
print("❌ 请输入数字")
# 输入金额
while True:
try:
amount = float(input("金额: ").strip())
if amount <= 0:
print("❌ 金额必须大于0")
continue
break
except ValueError:
print("❌ 请输入有效数字")
# 输入备注
note = input("备注 (可选): ").strip()
# 输入日期
date_input = input("日期 (YYYY-MM-DD, 默认今天): ").strip()
if date_input:
try:
trans_date = datetime.strptime(date_input, '%Y-%m-%d').date()
except ValueError:
print("❌ 日期格式错误，使用今天")
trans_date = date.today()
else:
trans_date = date.today()
# 保存到数据库
sql = """INSERT INTO transactions (amount, category_id, note, transaction_date)
VALUES (%s, %s, %s, %s)"""
try:
self.cursor.execute(sql, (amount, cat_id, note, trans_date))
self.connection.commit()
# 获取分类名
cat_name = next(cat['name'] for cat in categories if cat['id'] == cat_id)
icon = next(cat['icon'] for cat in categories if cat['id'] == cat_id)
print(f"✅ 记录成功! {icon} {cat_name}: {amount:.2f}元")
except Exception as e:
print(f"❌ 记录失败: {e}")
self.connection.rollback()
def view_today(self):
"""查看今日账单"""
today = date.today()
print(f"\n--- 📅 {today.strftime('%Y年%m月%d日')} 账单 ---")
sql = """SELECT t.*, c.name as category_name, c.type, c.icon
FROM transactions t
JOIN categories c ON t.category_id = c.id
WHERE t.transaction_date = %s
ORDER BY t.created_at DESC"""
self.cursor.execute(sql, (today,))
records = self.cursor.fetchall()
if not records:
print("📭 今天还没有记账哦~")
return
table = PrettyTable()
table.field_names = ["时间", "类型", "分类", "金额", "备注"]
table.align["备注"] = "l"
total_income = 0
total_expense = 0
for r in records:
time_str = r['created_at'].strftime('%H:%M')
type_str = "💰收入" if r['type'] == 'income' else "💸支出"
amount = r['amount']
if r['type'] == 'income':
total_income += amount
else:
total_expense += amount
table.add_row([time_str, type_str, f"{r['icon']}{r['category_name']}",
f"{amount:.2f}", r['note'] or ''])
print(table)
print(f"\n📊 今日汇总:")
print(f" 💰 收入: {total_income:.2f}元")
print(f" 💸 支出: {total_expense:.2f}元")
print(f" 💵 结余: {total_income - total_expense:.2f}元")
def view_by_date(self):
"""按日期查看账单"""
print("\n--- 📅 按日期查询 ---")
date_input = input("请输入日期 (YYYY-MM-DD): ").strip()
try:
query_date = datetime.strptime(date_input, '%Y-%m-%d').date()
except ValueError:
print("❌ 日期格式错误")
return
sql = """SELECT t.*, c.name as category_name, c.type, c.icon
FROM transactions t
JOIN categories c ON t.category_id = c.id
WHERE t.transaction_date = %s
ORDER BY t.created_at DESC"""
self.cursor.execute(sql, (query_date,))
records = self.cursor.fetchall()
if not records:
print(f"📭 {date_input} 没有记账记录")
return
print(f"\n📅 {query_date.strftime('%Y年%m月%d日')} 账单详情:")
table = PrettyTable()
table.field_names = ["时间", "类型", "分类", "金额", "备注"]
total_income = 0
total_expense = 0
for r in records:
time_str = r['created_at'].strftime('%H:%M')
type_str = "💰收入" if r['type'] == 'income' else "💸支出"
amount = r['amount']
if r['type'] == 'income':
total_income += amount
else:
total_expense += amount
table.add_row([time_str, type_str, f"{r['icon']}{r['category_name']}",
f"{amount:.2f}", r['note'] or ''])
print(table)
print(f"\n📊 汇总: 收入 {total_income:.2f}元 | 支出 {total_expense:.2f}元 | 结余 {total_income-total_expense:.2f}元")
def view_monthly_report(self):
"""查看月度报表"""
print("\n--- 📊 月度报表 ---")
# 选择月份
year = input("年份 (默认今年): ").strip()
month = input("月份 (1-12): ").strip()
if not year:
year = str(date.today().year)
if not month:
month = str(date.today().month)
month_str = f"{year}-{int(month):02d}"
# 获取当月所有记录
sql = """SELECT t.*, c.name as category_name, c.type, c.icon
FROM transactions t
JOIN categories c ON t.category_id = c.id
WHERE DATE_FORMAT(t.transaction_date, '%Y-%m') = %s
ORDER BY t.transaction_date, t.created_at"""
self.cursor.execute(sql, (month_str,))
records = self.cursor.fetchall()
if not records:
print(f"📭 {month_str} 没有记账记录")
return
# 计算各项统计
expense_by_category = {}
income_by_category = {}
daily_stats = {}
total_income = 0
total_expense = 0
for r in records:
day = r['transaction_date'].day
if day not in daily_stats:
daily_stats[day] = {'income': 0, 'expense': 0}
if r['type'] == 'income':
total_income += r['amount']
daily_stats[day]['income'] += r['amount']
cat_key = f"{r['icon']}{r['category_name']}"
income_by_category[cat_key] = income_by_category.get(cat_key, 0) + r['amount']
else:
total_expense += r['amount']
daily_stats[day]['expense'] += r['amount']
cat_key = f"{r['icon']}{r['category_name']}"
expense_by_category[cat_key] = expense_by_category.get(cat_key, 0) + r['amount']
# 打印报表
print(f"\n📊 {year}年{int(month)}月 财务报告")
print("=" * 45)
print(f"💰 总收入: {total_income:.2f}元")
print(f"💸 总支出: {total_expense:.2f}元")
print(f"💵 本月结余: {total_income - total_expense:.2f}元")
print(f"📝 总笔数: {len(records)}笔")
# 支出分类统计
if expense_by_category:
print(f"\n📉 支出分类排行:")
sorted_expense = sorted(expense_by_category.items(), key=lambda x: x[1], reverse=True)
for cat, amount in sorted_expense:
percentage = (amount / total_expense) * 100
bar = '█' * int(percentage / 5)
print(f" {cat}: {amount:.2f}元 ({percentage:.1f}%) {bar}")
# 收入分类统计
if income_by_category:
print(f"\n📈 收入分类排行:")
sorted_income = sorted(income_by_category.items(), key=lambda x: x[1], reverse=True)
for cat, amount in sorted_income:
print(f" {cat}: {amount:.2f}元")
# 每日支出趋势
print(f"\n📅 每日支出趋势:")
days_in_month = calendar.monthrange(int(year), int(month))[1]
for day in range(1, days_in_month + 1):
stats = daily_stats.get(day, {'income': 0, 'expense': 0})
bar = '█' * min(int(stats['expense'] / 10), 30)
print(f" {day:2d}日: {stats['expense']:>7.2f}元 {bar}")
# 检查预算
self.check_budget(month_str, expense_by_category)
def check_budget(self, month, actual_expense):
"""检查预算执行情况"""
sql = """SELECT b.*, c.name, c.icon
FROM budgets b
JOIN categories c ON b.category_id = c.id
WHERE b.month = %s"""
self.cursor.execute(sql, (month,))
budgets = self.cursor.fetchall()
if budgets:
print(f"\n🎯 预算执行情况:")
for b in budgets:
cat_key = f"{b['icon']}{b['name']}"
actual = actual_expense.get(cat_key, 0)
budget = b['budget_amount']
percentage = (actual / budget) * 100 if budget > 0 else 0
status = "✅" if percentage <= 100 else "⚠️"
bar = '█' * min(int(percentage / 5), 25)
print(f" {status} {cat_key}: {actual:.2f}/{budget:.2f} ({percentage:.1f}%) {bar}")
def set_budget(self):
"""设置预算"""
print("\n--- 🎯 设置预算 ---")
month = input("月份 (YYYY-MM, 默认本月): ").strip()
if not month:
month = date.today().strftime('%Y-%m')
# 获取支出分类
categories = self.get_categories('expense')
print("\n支出分类:")
for cat in categories:
print(f"{cat['id']}. {cat['icon']} {cat['name']}")
while True:
try:
cat_id = int(input("\n选择分类编号 (输入0结束): ").strip())
if cat_id == 0:
break
if not any(cat['id'] == cat_id for cat in categories):
print("❌ 无效的分类编号")
continue
amount = float(input("预算金额: ").strip())
if amount <= 0:
print("❌ 预算金额必须大于0")
continue
# 保存预算
sql = """INSERT INTO budgets (month, category_id, budget_amount)
VALUES (%s, %s, %s)
ON DUPLICATE KEY UPDATE budget_amount = %s"""
self.cursor.execute(sql, (month, cat_id, amount, amount))
self.connection.commit()
cat_name = next(cat['name'] for cat in categories if cat['id'] == cat_id)
print(f"✅ {cat_name} 预算设置为 {amount:.2f}元")
except ValueError:
print("❌ 请输入有效数字")
except Exception as e:
print(f"❌ 设置失败: {e}")
def generate_chart(self):
"""生成图表分析"""
print("\n--- 📊 生成图表 ---")
# 选择月份
year = input("年份 (默认今年): ").strip()
month = input("月份 (1-12): ").strip()
if not year:
year = str(date.today().year)
if not month:
month = str(date.today().month)
month_str = f"{year}-{int(month):02d}"
# 获取数据
sql = """SELECT c.name, c.icon, SUM(t.amount) as total
FROM transactions t
JOIN categories c ON t.category_id = c.id
WHERE DATE_FORMAT(t.transaction_date, '%Y-%m') = %s AND c.type = 'expense'
GROUP BY c.id, c.name, c.icon
ORDER BY total DESC"""
self.cursor.execute(sql, (month_str,))
data = self.cursor.fetchall()
if not data:
print("📭 没有数据可以生成图表")
return
# 绘制饼图
labels = [f"{d['icon']}{d['name']}" for d in data]
sizes = [d['total'] for d in data]
colors = plt.cm.Set3(range(len(data)))
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(14, 6))
# 饼图
wedges, texts, autotexts = ax1.pie(sizes, labels=None, autopct='%1.1f%%',
colors=colors, startangle=90)
ax1.set_title(f'{year}年{month}月支出分布', fontsize=14, fontweight='bold')
ax1.legend(wedges, labels, title="分类", loc="center left", bbox_to_anchor=(1, 0, 0.5, 1))
# 柱状图
bars = ax2.bar(labels, sizes, color=colors)
ax2.set_title(f'{year}年{month}月支出对比', fontsize=14, fontweight='bold')
ax2.set_ylabel('金额 (元)')
ax2.tick_params(axis='x', rotation=45)
# 在柱子上显示数值
for bar, size in zip(bars, sizes):
height = bar.get_height()
ax2.text(bar.get_x() + bar.get_width()/2., height,
f'{size:.0f}', ha='center', va='bottom')
plt.tight_layout()
# 保存图片
filename = f"支出分析_{month_str}.png"
plt.savefig(filename, dpi=150, bbox_inches='tight')
print(f"✅ 图表已保存: {filename}")
plt.show()
def search_records(self):
"""搜索记账记录"""
print("\n--- 🔍 搜索记录 ---")
keyword = input("请输入关键词搜索 (金额/备注/分类): ").strip()
sql = """SELECT t.*, c.name as category_name, c.type, c.icon
FROM transactions t
JOIN categories c ON t.category_id = c.id
WHERE t.note LIKE %s OR CAST(t.amount AS CHAR) LIKE %s OR c.name LIKE %s
ORDER BY t.transaction_date DESC, t.created_at DESC
LIMIT 50"""
search_term = f"%{keyword}%"
self.cursor.execute(sql, (search_term, search_term, search_term))
records = self.cursor.fetchall()
if not records:
print(f"📭 没有找到包含 '{keyword}' 的记录")
return
table = PrettyTable()
table.field_names = ["日期", "类型", "分类", "金额", "备注"]
for r in records:
type_str = "💰收入" if r['type'] == 'income' else "💸支出"
table.add_row([
r['transaction_date'].strftime('%m-%d'),
type_str,
f"{r['icon']}{r['category_name']}",
f"{r['amount']:.2f}",
r['note'] or ''
])
print(f"\n找到 {len(records)} 条记录:")
print(table)
def delete_record(self):
"""删除记录"""
print("\n--- 🗑️ 删除记录 ---")
# 先显示最近的记录
sql = """SELECT t.*, c.name as category_name, c.type, c.icon
FROM transactions t
JOIN categories c ON t.category_id = c.id
ORDER BY t.created_at DESC
LIMIT 10"""
self.cursor.execute(sql)
records = self.cursor.fetchall()
if not records:
print("📭 没有可删除的记录")
return
print("最近10条记录:")
table = PrettyTable()
table.field_names = ["ID", "日期", "类型", "分类", "金额", "备注"]
for r in records:
type_str = "💰收入" if r['type'] == 'income' else "💸支出"
table.add_row([
r['id'],
r['transaction_date'].strftime('%m-%d'),
type_str,
f"{r['icon']}{r['category_name']}",
f"{r['amount']:.2f}",
r['note'] or ''
])
print(table)
try:
record_id = int(input("请输入要删除的记录ID (输入0取消): ").strip())
if record_id == 0:
return
confirm = input(f"确认删除 ID={record_id} 的记录? (y/n): ").strip().lower()
if confirm == 'y':
self.cursor.execute("DELETE FROM transactions WHERE id = %s", (record_id,))
self.connection.commit()
if self.cursor.rowcount > 0:
print("✅ 删除成功")
else:
print("❌ 未找到该记录")
else:
print("已取消")
except ValueError:
print("❌ 请输入有效的ID")
def close(self):
"""关闭连接"""
if self.cursor:
self.cursor.close()
if self.connection:
self.connection.close()
print("🔒 记账本已关闭")
def run(self):
"""主循环"""
while True:
print("\n" + "=" * 38)
print("📒 每日记账本")
print("=" * 38)
print("1. 📝 记一笔")
print("2. 📅 今日账单")
print("3. 🔍 按日期查询")
print("4. 📊 月度报表")
print("5. 🎯 设置预算")
print("6. 📈 生成图表")
print("7. 🔎 搜索记录")
print("8. 🗑️ 删除记录")
print("9. 🚪 退出")
print("=" * 38)
choice = input("请选择 (1-9): ").strip()
actions = {
'1': self.add_transaction,
'2': self.view_today,
'3': self.view_by_date,
'4': self.view_monthly_report,
'5': self.set_budget,
'6': self.generate_chart,
'7': self.search_records,
'8': self.delete_record,
'9': lambda: (self.close(), print("👋 明天见！"), exit())
}
action = actions.get(choice)
if action:
action()
else:
print("❌ 无效选项")
if __name__ == "__main__":
app = DailyAccount()
try:
app.run()
except KeyboardInterrupt:
print("\n\n程序被中断")
finally:
app.close()安装依赖
pip install pymysql prettytable matplotlib pandas功能特点
✅ 快速记账：选择分类、输入金额即可
✅ 今日账单：查看当天收支明细
✅ 月度报表：自动统计各类支出占比
✅ 预算管理：设置每月预算，超支提醒
✅ 图表分析：生成饼图和柱状图
✅ 搜索功能：按关键词搜索历史记录
✅ 数据可视化：直观展示消费结构
使用示例
-
每天花10秒记一笔账
-
月底看报表了解钱花在哪
-
设置预算控制开支
-
生成图表分享给家人
更多推荐
4
0- 0
已为社区贡献4条内容
所有评论(0)