CREATE TABLE IF NOT EXISTS demo_test (
tid SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
notes TEXT NOT NULL,
dateline BIGINT NOT NULL DEFAULT 0
);# demo_test 表管理程序（PostgreSQL 版）
# 功能：增加、编辑、删除、查看（双击查看）、分页列表
# 数据库表结构（PostgreSQL）：
# tid SERIAL PRIMARY KEY
# title VARCHAR(255) NOT NULL
# notes TEXT NOT NULL
# dateline BIGINT NOT NULL DEFAULT 0 (Unix timestamp)
# 依赖库：psycopg2, pillow, tkinter (标准库), datetime, time, urllib
import psycopg2
from psycopg2 import extras
import sys
import tkinter as tk
from tkinter import ttk, messagebox, Toplevel
from datetime import datetime
import time
import urllib.request
import os
import tempfile
from PIL import Image, ImageTk # 需要 pip install pillow
# ======================= 配置（可修改）=======================
TABLE_NAME = "demo_test" # 表名，可随时修改
# 数据库连接配置（请修改为实际值）
DB_CONFIG = {
'host': 'localhost',
'port': 5432,
'user': 'postgres',
'password': 'your_password',
'database': 'your_database',
'client_encoding': 'utf8',
}
# 窗口图标 URL
ICON_URL = "http://www.hacms.com/assets/python/locate.png"
# ============================================================
# ----------------------- 数据库封装 -----------------------
def show_error_and_exit(error_message):
"""弹出错误对话框并退出程序"""
root = tk.Tk()
root.withdraw()
messagebox.showerror("致命错误", error_message)
root.destroy()
sys.exit(1)
class Database:
"""数据库操作单例类，支持连接异常处理"""
_instance = None
def __new__(cls):
if not cls._instance:
cls._instance = super().__new__(cls)
cls._instance.conn = None
try:
cls._instance.connect()
except Exception as e:
cls._instance = None
show_error_and_exit(f"数据库连接失败：{e}")
return cls._instance
def connect(self):
"""建立 PostgreSQL 连接"""
# 使用 psycopg2 连接，开启 DictCursor
self.conn = psycopg2.connect(
host=DB_CONFIG['host'],
port=DB_CONFIG.get('port', 5432),
user=DB_CONFIG['user'],
password=DB_CONFIG['password'],
dbname=DB_CONFIG['database'],
client_encoding=DB_CONFIG.get('client_encoding', 'utf8')
)
# 默认 autocommit 为 False，需要手动 commit
def close(self):
"""关闭数据库连接（程序退出时调用）"""
if self.conn:
self.conn.close()
def query(self, sql, args=None):
"""执行 SELECT 查询，返回字典列表"""
with self.conn.cursor(cursor_factory=extras.DictCursor) as cursor:
cursor.execute(sql, args)
return cursor.fetchall()
def execute(self, sql, args=None):
"""执行 INSERT/UPDATE/DELETE，返回受影响行数，自动提交"""
try:
with self.conn.cursor(cursor_factory=extras.DictCursor) as 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=10):
"""分页获取数据（按 tid 降序）"""
offset = (page - 1) * page_size
sql = f"SELECT tid, title, notes, dateline FROM {TABLE_NAME} ORDER BY tid DESC LIMIT %s OFFSET %s"
rows = self.query(sql, (page_size, offset))
return rows
def insert_record(self, title, notes, dateline):
"""新增记录，返回新插入的 tid（使用 RETURNING）"""
sql = f"INSERT INTO {TABLE_NAME} (title, notes, dateline) VALUES (%s, %s, %s) RETURNING tid"
with self.conn.cursor(cursor_factory=extras.DictCursor) as cursor:
cursor.execute(sql, (title, notes, dateline))
result = cursor.fetchone()
self.conn.commit()
return result['tid']
def update_record(self, tid, title, notes, dateline):
"""更新记录"""
sql = f"UPDATE {TABLE_NAME} SET title=%s, notes=%s, dateline=%s WHERE tid=%s"
return self.execute(sql, (title, notes, dateline, tid))
def delete_record(self, tid):
"""删除记录"""
sql = f"DELETE FROM {TABLE_NAME} WHERE tid=%s"
return self.execute(sql, (tid,))
# ----------------------- GUI 应用程序 -----------------------
class App:
def __init__(self, root):
self.root = root
self.root.title("Demo_Test 数据管理")
self.root.geometry("900x600")
# 设置窗口图标
self.set_window_icon()
# 数据库实例
self.db = Database()
# 分页参数
self.current_page = 1
self.page_size = 10
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):
"""从 URL 下载图片并设置为窗口图标"""
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
# 用 Pillow 打开并转换为 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):
"""创建所有控件（列表区域和分页控件）"""
# 主区域：列表 + 滚动条（放在一个 Frame 中，以便自动填充）
main_frame = tk.Frame(self.root)
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)
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)
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="跳转页:").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"""
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()
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):
"""获取当前选中的记录 tid"""
selected = self.tree.selection()
if not selected:
messagebox.showwarning("提示", "请先选择一条记录")
return None
item = self.tree.item(selected[0])
tid = item['values'][0]
return tid
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=%s"
rows = self.db.query(sql, (tid,))
if not rows:
messagebox.showerror("错误", "记录不存在")
return
record = rows[0]
self.open_edit_window(is_edit=True, record=record)
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()
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_str = datetime.fromtimestamp(record['dateline']).strftime("%Y-%m-%d")
date_var.set(date_str)
else:
date_var.set("")
else:
date_var.set(datetime.now().strftime("%Y-%m-%d"))
# 表单布局
tk.Label(win, text="标题:", font=("", 10)).grid(row=0, column=0, padx=10, pady=10, sticky="e")
title_entry = tk.Entry(win, textvariable=title_var, width=40)
title_entry.grid(row=0, column=1, padx=10, pady=10, sticky="w")
tk.Label(win, text="内容:", font=("", 10)).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)).grid(row=2, column=0, padx=10, pady=10, sticky="e")
date_entry = tk.Entry(win, textvariable=date_var, width=15)
date_entry.grid(row=2, column=1, padx=10, pady=10, sticky="w")
error_label = tk.Label(win, fg="red")
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:
error_label.config(text="标题不能为空")
return
if not notes:
error_label.config(text="内容不能为空")
return
if 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)
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=%s"
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()
tk.Label(win, text=f"标题：{rec['title']}", font=("", 12, "bold")).pack(pady=5, anchor="w", padx=10)
tk.Label(win, text=f"日期：{dt_str}", font=("", 10)).pack(pady=5, anchor="w", padx=10)
tk.Label(win, text="内容：", font=("", 10, "bold")).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.