CREATE TABLE demo_test (
tid INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT (0),
title TEXT,
notes TEXT,
dateline INTEGER
);
"""
demo_test 表管理程序 (SQLite 版本)
功能：增加、编辑、删除、查看（双击查看）、分页列表
数据库表结构：
tid integer primary key autoincrement
title text not null
notes text not null
dateline integer not null default 0 (Unix timestamp)
依赖库：tkinter, sqlite3, datetime, time, urllib, PIL (Pillow)
"""
import sqlite3
import sys
import tkinter as tk
from tkinter import ttk, messagebox, Toplevel
from datetime import datetime
import time
import urllib.request
import tempfile
import os
# 尝试导入 Pillow，如果失败则禁用图标功能
try:
from PIL import Image, ImageTk
PILLOW_AVAILABLE = True
except ImportError:
PILLOW_AVAILABLE = False
print("提示：未安装 Pillow 库，无法设置窗口图标。请执行 'pip install Pillow' 安装。")
# ======================= 配置（可修改）=======================
TABLE_NAME = "demo_test" # 表名，可随时修改
DB_FILE = "data.db" # SQLite 数据库文件
PAGE_SIZE = 10 # 每页显示数量
ICON_URL = "http://www.hacms.com/assets/python/locate.png" # 窗口图标 URL
# ============================================================
# ----------------------- 数据库封装 -----------------------
def show_error_and_exit(error_message):
"""弹出错误对话框并退出程序"""
root = tk.Tk()
root.withdraw()
messagebox.showerror("致命错误", error_message)
root.destroy()
sys.exit(1)
class Database:
"""SQLite 数据库操作单例类"""
_instance = None
def __new__(cls):
if not cls._instance:
cls._instance = super().__new__(cls)
cls._instance.conn = None
try:
cls._instance.connect()
cls._instance.create_table()
except Exception as e:
cls._instance = None
show_error_and_exit(f"数据库连接失败：{e}")
return cls._instance
def connect(self):
"""建立数据库连接"""
self.conn = sqlite3.connect(DB_FILE)
self.conn.row_factory = sqlite3.Row
def create_table(self):
"""创建表（如果不存在）"""
sql = f"""
CREATE TABLE IF NOT EXISTS {TABLE_NAME} (
tid INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
notes TEXT NOT NULL,
dateline INTEGER NOT NULL DEFAULT 0
)
"""
self.conn.execute(sql)
self.conn.commit()
def close(self):
"""关闭数据库连接"""
if self.conn:
self.conn.close()
def query(self, sql, args=()):
"""执行 SELECT 查询，返回字典列表"""
cursor = self.conn.cursor()
cursor.execute(sql, args)
rows = cursor.fetchall()
return [dict(row) for row in rows]
def execute(self, sql, args=()):
"""执行 INSERT/UPDATE/DELETE，返回受影响行数，自动提交"""
try:
cursor = self.conn.cursor()
cursor.execute(sql, args)
self.conn.commit()
return cursor.rowcount
except Exception as e:
self.conn.rollback()
raise e
def get_total_count(self):
"""获取表中总记录数"""
sql = f"SELECT COUNT(*) as cnt FROM {TABLE_NAME}"
result = self.query(sql)
return result[0]['cnt'] if result else 0
def get_page(self, page, page_size=PAGE_SIZE):
"""分页获取数据（按 tid 降序）"""
offset = (page - 1) * page_size
sql = f"SELECT tid, title, notes, dateline FROM {TABLE_NAME} ORDER BY tid DESC LIMIT ? OFFSET ?"
rows = self.query(sql, (page_size, offset))
return rows
def insert_record(self, title, notes, dateline):
"""新增记录，返回新插入的 tid"""
sql = f"INSERT INTO {TABLE_NAME} (title, notes, dateline) VALUES (?, ?, ?)"
self.execute(sql, (title, notes, dateline))
cursor = self.conn.cursor()
cursor.execute("SELECT last_insert_rowid() as tid")
result = cursor.fetchone()
return result[0] if result else None
def update_record(self, tid, title, notes, dateline):
"""更新记录"""
sql = f"UPDATE {TABLE_NAME} SET title=?, notes=?, dateline=? WHERE tid=?"
return self.execute(sql, (title, notes, dateline, tid))
def delete_record(self, tid):
"""删除记录"""
sql = f"DELETE FROM {TABLE_NAME} WHERE tid=?"
return self.execute(sql, (tid,))
# ----------------------- GUI 应用程序 -----------------------
class App:
def __init__(self, root):
self.root = root
self.root.title("Demo_Test 数据管理 (SQLite)")
self.root.geometry("900x600")
self.root.configure(bg='white')
# 设置窗口图标
self.set_window_icon()
# 数据库实例
self.db = Database()
# 分页参数
self.current_page = 1
self.page_size = PAGE_SIZE
self.total_pages = 1
# 创建界面
self.create_menu()
self.create_widgets()
self.load_data()
# 程序退出时关闭数据库连接
self.root.protocol("WM_DELETE_WINDOW", self.on_closing)
def set_window_icon(self):
"""从网络下载图片并设置为窗口图标（需 Pillow 支持）"""
if not PILLOW_AVAILABLE:
return
try:
# 下载图片
with urllib.request.urlopen(ICON_URL, timeout=5) as response:
img_data = response.read()
# 保存到临时文件
with tempfile.NamedTemporaryFile(delete=False, suffix='.png') as tmp_file:
tmp_file.write(img_data)
tmp_path = tmp_file.name
# 打开并转换为 PhotoImage
img = Image.open(tmp_path)
icon = ImageTk.PhotoImage(img)
self.root.iconphoto(True, icon)
# 删除临时文件
os.unlink(tmp_path)
except Exception as e:
# 图标加载失败不影响主程序，仅打印调试信息
print(f"窗口图标加载失败：{e}")
def create_menu(self):
"""创建菜单栏（一级菜单）"""
menubar = tk.Menu(self.root)
self.root.config(menu=menubar)
menubar.add_command(label="新增", command=self.add_record)
menubar.add_command(label="编辑", command=self.edit_record)
menubar.add_command(label="删除", command=self.delete_record)
menubar.add_command(label="刷新", command=self.refresh)
menubar.add_command(label="显示", command=self.view_record)
def create_widgets(self):
"""创建所有控件（列表区域和分页控件）"""
main_frame = tk.Frame(self.root, bg='white')
main_frame.pack(fill=tk.BOTH, expand=True, padx=5, pady=5)
# 列表区域 (Treeview)
columns = ("tid", "title", "notes", "dateline")
self.tree = ttk.Treeview(main_frame, columns=columns, show="headings")
self.tree.heading("tid", text="序号")
self.tree.heading("title", text="标题")
self.tree.heading("notes", text="内容")
self.tree.heading("dateline", text="日期")
self.tree.column("tid", width=60, anchor="center")
self.tree.column("title", width=200)
self.tree.column("notes", width=400)
self.tree.column("dateline", width=120, anchor="center")
scrollbar = ttk.Scrollbar(main_frame, orient=tk.VERTICAL, command=self.tree.yview)
self.tree.configure(yscrollcommand=scrollbar.set)
self.tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
self.tree.bind("<Double-1>", self.view_record)
# 分页控件区域
page_frame = tk.Frame(self.root, bg='white')
page_frame.pack(side=tk.BOTTOM, fill=tk.X, pady=5)
tk.Button(page_frame, text="首页", command=self.first_page, width=6).pack(side=tk.LEFT, padx=2)
tk.Button(page_frame, text="上一页", command=self.prev_page, width=6).pack(side=tk.LEFT, padx=2)
self.page_label = tk.Label(page_frame, text="第 1 页 / 共 1 页", width=15, bg='white')
self.page_label.pack(side=tk.LEFT, padx=10)
tk.Button(page_frame, text="下一页", command=self.next_page, width=6).pack(side=tk.LEFT, padx=2)
tk.Button(page_frame, text="末页", command=self.last_page, width=6).pack(side=tk.LEFT, padx=2)
tk.Label(page_frame, text="跳转页:", bg='white').pack(side=tk.LEFT, padx=(10, 2))
self.page_spinbox = tk.Spinbox(page_frame, from_=1, to=1, width=5, command=self.jump_page)
self.page_spinbox.pack(side=tk.LEFT, padx=2)
def load_data(self):
"""加载当前页数据到 Treeview"""
try:
total = self.db.get_total_count()
if total == 0:
self.total_pages = 1
self.current_page = 1
for item in self.tree.get_children():
self.tree.delete(item)
self.update_page_controls()
return
self.total_pages = (total + self.page_size - 1) // self.page_size
if self.current_page > self.total_pages:
self.current_page = self.total_pages
if self.current_page < 1:
self.current_page = 1
rows = self.db.get_page(self.current_page, self.page_size)
for item in self.tree.get_children():
self.tree.delete(item)
for row in rows:
dt_str = datetime.fromtimestamp(row['dateline']).strftime("%Y-%m-%d") if row['dateline'] else ""
self.tree.insert("", tk.END, values=(row['tid'], row['title'], row['notes'], dt_str))
self.update_page_controls()
except Exception as e:
messagebox.showerror("错误", f"加载数据失败：{e}")
def update_page_controls(self):
"""更新分页控件显示及 Spinbox 范围"""
self.page_label.config(text=f"第 {self.current_page} 页 / 共 {self.total_pages} 页")
self.page_spinbox.config(from_=1, to=self.total_pages)
self.page_spinbox.delete(0, tk.END)
self.page_spinbox.insert(0, str(self.current_page))
def refresh(self):
self.load_data()
def first_page(self):
if self.current_page != 1:
self.current_page = 1
self.load_data()
def prev_page(self):
if self.current_page > 1:
self.current_page -= 1
self.load_data()
def next_page(self):
if self.current_page < self.total_pages:
self.current_page += 1
self.load_data()
def last_page(self):
if self.current_page != self.total_pages:
self.current_page = self.total_pages
self.load_data()
def jump_page(self):
try:
page = int(self.page_spinbox.get())
if 1 <= page <= self.total_pages:
self.current_page = page
self.load_data()
else:
messagebox.showwarning("警告", f"页码范围 1-{self.total_pages}")
except ValueError:
messagebox.showerror("错误", "请输入有效数字")
def get_selected_tid(self):
selected = self.tree.selection()
if not selected:
messagebox.showwarning("提示", "请先选择一条记录")
return None
item = self.tree.item(selected[0])
return item['values'][0]
def add_record(self):
self.open_edit_window(is_edit=False)
def edit_record(self):
tid = self.get_selected_tid()
if tid is None:
return
sql = f"SELECT tid, title, notes, dateline FROM {TABLE_NAME} WHERE tid=?"
rows = self.db.query(sql, (tid,))
if not rows:
messagebox.showerror("错误", "记录不存在")
return
self.open_edit_window(is_edit=True, record=rows[0])
def open_edit_window(self, is_edit=False, record=None):
win = Toplevel(self.root)
win.title("编辑记录" if is_edit else "新增记录")
win.geometry("500x350")
win.resizable(False, False)
win.grab_set()
win.configure(bg='white')
title_var = tk.StringVar()
notes_var = tk.StringVar()
date_var = tk.StringVar()
if is_edit and record:
title_var.set(record['title'])
notes_var.set(record['notes'])
if record['dateline']:
date_var.set(datetime.fromtimestamp(record['dateline']).strftime("%Y-%m-%d"))
else:
date_var.set(datetime.now().strftime("%Y-%m-%d"))
tk.Label(win, text="标题:", font=("", 10), bg='white').grid(row=0, column=0, padx=10, pady=10, sticky="e")
tk.Entry(win, textvariable=title_var, width=40).grid(row=0, column=1, padx=10, pady=10, sticky="w")
tk.Label(win, text="内容:", font=("", 10), bg='white').grid(row=1, column=0, padx=10, pady=10, sticky="ne")
notes_text = tk.Text(win, width=40, height=10)
notes_text.grid(row=1, column=1, padx=10, pady=10, sticky="w")
notes_text.insert("1.0", notes_var.get())
tk.Label(win, text="日期:", font=("", 10), bg='white').grid(row=2, column=0, padx=10, pady=10, sticky="e")
tk.Entry(win, textvariable=date_var, width=15).grid(row=2, column=1, padx=10, pady=10, sticky="w")
error_label = tk.Label(win, fg="red", bg='white')
error_label.grid(row=3, column=0, columnspan=2, pady=5)
def save():
title = title_var.get().strip()
notes = notes_text.get("1.0", tk.END).strip()
date_str = date_var.get().strip()
if not title or not notes or not date_str:
error_label.config(text="所有字段都不能为空")
return
try:
dt = datetime.strptime(date_str, "%Y-%m-%d")
dateline = int(time.mktime(dt.timetuple()))
except ValueError:
error_label.config(text="日期格式错误，应为 YYYY-MM-DD")
return
try:
if is_edit:
self.db.update_record(record['tid'], title, notes, dateline)
messagebox.showinfo("成功", "更新成功")
else:
new_id = self.db.insert_record(title, notes, dateline)
messagebox.showinfo("成功", f"新增成功，新序号：{new_id}")
win.destroy()
self.refresh()
except Exception as e:
messagebox.showerror("数据库错误", f"保存失败：{e}")
btn_frame = tk.Frame(win, bg='white')
btn_frame.grid(row=4, column=0, columnspan=2, pady=20)
tk.Button(btn_frame, text="保存", command=save, width=10).pack(side=tk.LEFT, padx=10)
tk.Button(btn_frame, text="取消", command=win.destroy, width=10).pack(side=tk.LEFT, padx=10)
def delete_record(self):
tid = self.get_selected_tid()
if tid is None:
return
if not messagebox.askyesno("确认删除", f"确定要删除序号为 {tid} 的记录吗？"):
return
try:
self.db.delete_record(tid)
messagebox.showinfo("成功", "删除成功")
total = self.db.get_total_count()
new_total_pages = (total + self.page_size - 1) // self.page_size
if new_total_pages == 0:
self.current_page = 1
elif self.current_page > new_total_pages:
self.current_page = new_total_pages
self.load_data()
except Exception as e:
messagebox.showerror("数据库错误", f"删除失败：{e}")
def view_record(self, event=None):
tid = self.get_selected_tid()
if tid is None:
return
sql = f"SELECT tid, title, notes, dateline FROM {TABLE_NAME} WHERE tid=?"
rows = self.db.query(sql, (tid,))
if not rows:
messagebox.showerror("错误", "记录不存在")
return
rec = rows[0]
dt_str = datetime.fromtimestamp(rec['dateline']).strftime("%Y-%m-%d") if rec['dateline'] else ""
win = Toplevel(self.root)
win.title(f"查看记录 - 序号 {rec['tid']}")
win.geometry("500x400")
win.grab_set()
win.configure(bg='white')
tk.Label(win, text=f"标题：{rec['title']}", font=("", 12, "bold"), bg='white').pack(pady=5, anchor="w", padx=10)
tk.Label(win, text=f"日期：{dt_str}", font=("", 10), bg='white').pack(pady=5, anchor="w", padx=10)
tk.Label(win, text="内容：", font=("", 10, "bold"), bg='white').pack(pady=5, anchor="w", padx=10)
text_box = tk.Text(win, wrap=tk.WORD, height=15)
text_box.pack(fill=tk.BOTH, expand=True, padx=10, pady=5)
text_box.insert("1.0", rec['notes'])
text_box.config(state=tk.DISABLED)
tk.Button(win, text="关闭", command=win.destroy, width=10).pack(pady=10)
def on_closing(self):
self.db.close()
self.root.destroy()
if __name__ == "__main__":
root = tk.Tk()
app = App(root)
root.mainloop()HACMS进销存管理系统V1.0(桌面版)采用的是Python+MySQL开发，开发初衷仅为自己所用，主要包括采购管理、采购退货管理、销售管理、销售退货管理、库存预警、库存报表、报盈管理、报损管理、流水导出等功能 ...
PHP is a popular general-purpose scripting language that is especially suited to web development. Fast, flexible and pragmatic, PHP powers everything from your blog to the most popular websites in the world.
CodeIgniter is a powerful PHP framework with a very small footprint, built for developers who need a simple and elegant toolkit to create full-featured web applications.