从零构建商场档案管理系统：Python+SQLite实战指南

一、系统核心功能与架构

商场档案管理系统需要管理三类核心数据：商户档案、合同档案、证照档案。我们将使用Python作为后端语言，SQLite作为数据库，Tkinter构建图形界面。这个组合无需安装复杂环境，适合快速开发部署。

1.1 系统功能模块

• 商户管理：基本信息、联系人、入驻时间
• 合同管理：合同编号、起止时间、租金信息
• 证照管理：营业执照、许可证件扫描件管理
• 查询统计：多条件筛选、到期提醒、数据导出

1.2 技术选型理由

Python 3.8+内置SQLite支持，无需单独安装数据库。Tkinter是Python标准GUI库，兼容Windows/macOS/Linux。整个系统可打包成单个exe文件分发。

二、开发环境搭建

2.1 Python环境安装

访问Python官网下载安装包：https://www.python.org/downloads/

安装时务必勾选"Add Python to PATH"。验证安装是否成功：

``` python --version ```

应显示Python 3.8或更高版本。

2.2 创建项目目录结构

在D盘创建项目文件夹：

``` mkdir D:\mall_archive cd D:\mall_archive ```

创建以下子目录：

• data - 存放数据库文件
• docs - 存放文档模板
• scans - 存放证照扫描件
• src - 存放源代码

三、数据库设计与实现

3.1 数据库初始化脚本

在src目录下创建database.py文件：

``` import sqlite3 import os def init_database(): 确保data目录存在 if not os.path.exists('../data'): os.makedirs('../data') 连接数据库 conn = sqlite3.connect('../data/mall_archive.db') cursor = conn.cursor() 创建商户表 cursor.execute(''' CREATE TABLE IF NOT EXISTS merchants ( id INTEGER PRIMARY KEY AUTOINCREMENT, merchant_code VARCHAR(20) UNIQUE NOT NULL, merchant_name VARCHAR(100) NOT NULL, contact_person VARCHAR(50), contact_phone VARCHAR(20), email VARCHAR(100), floor INTEGER, booth_number VARCHAR(20), entry_date DATE, status INTEGER DEFAULT 1, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') 创建合同表 cursor.execute(''' CREATE TABLE IF NOT EXISTS contracts ( id INTEGER PRIMARY KEY AUTOINCREMENT, contract_no VARCHAR(50) UNIQUE NOT NULL, merchant_id INTEGER NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, rent_amount DECIMAL(10,2), payment_cycle VARCHAR(20), deposit_amount DECIMAL(10,2), contract_file VARCHAR(255), FOREIGN KEY (merchant_id) REFERENCES merchants(id) ) ''') 创建证照表 cursor.execute(''' CREATE TABLE IF NOT EXISTS licenses ( id INTEGER PRIMARY KEY AUTOINCREMENT, merchant_id INTEGER NOT NULL, license_type VARCHAR(50) NOT NULL, license_no VARCHAR(100), issue_date DATE, expiry_date DATE, scan_path VARCHAR(255), remark TEXT, FOREIGN KEY (merchant_id) REFERENCES merchants(id) ) ''') conn.commit() conn.close() print("数据库初始化完成") if __name__ == "__main__": init_database() ```

运行这个脚本创建数据库：python src/database.py

3.2 数据库操作类

创建db_operations.py文件，封装常用数据库操作：

``` import sqlite3 from datetime import datetime class DBOperations: def __init__(self): self.db_path = '../data/mall_archive.db' def get_connection(self): return sqlite3.connect(self.db_path) def add_merchant(self, data): conn = self.get_connection() cursor = conn.cursor() sql = '''INSERT INTO merchants (merchant_code, merchant_name, contact_person, contact_phone, email, floor, booth_number, entry_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?)''' cursor.execute(sql, data) conn.commit() conn.close() return cursor.lastrowid def search_merchants(self, keyword=None, floor=None, status=1): conn = self.get_connection() cursor = conn.cursor() query = "SELECT FROM merchants WHERE status = ?" params = [status] if keyword: query += " AND (merchant_name LIKE ? OR merchant_code LIKE ?)" params.extend([f'%{keyword}%', f'%{keyword}%']) if floor: query += " AND floor = ?" params.append(floor) cursor.execute(query, params) results = cursor.fetchall() conn.close() return results def get_expiring_contracts(self, days=30): conn = self.get_connection() cursor = conn.cursor() sql = '''SELECT m.merchant_name, c.contract_no, c.end_date FROM contracts c JOIN merchants m ON c.merchant_id = m.id WHERE date(c.end_date) BETWEEN date('now') AND date('now', ?)''' cursor.execute(sql, [f'+{days} days']) results = cursor.fetchall() conn.close() return results ```

四、图形界面开发

4.1 主窗口设计

创建main_window.py文件：

``` import tkinter as tk from tkinter import ttk, messagebox from db_operations import DBOperations class MainWindow: def __init__(self): self.root = tk.Tk() self.root.title("商场档案管理系统") self.root.geometry("1200x700") self.db = DBOperations() self.setup_ui() def setup_ui(self): 顶部菜单栏 menubar = tk.Menu(self.root) 档案管理菜单 archive_menu = tk.Menu(menubar, tearoff=0) archive_menu.add_command(label="新增商户", command=self.open_add_merchant) archive_menu.add_command(label="合同管理", command=self.open_contract_manage) archive_menu.add_separator() archive_menu.add_command(label="退出", command=self.root.quit) menubar.add_cascade(label="档案管理", menu=archive_menu) 查询菜单 query_menu = tk.Menu(menubar, tearoff=0) query_menu.add_command(label="商户查询", command=self.open_merchant_query) query_menu.add_command(label="合同到期提醒", command=self.show_expiring_contracts) menubar.add_cascade(label="查询统计", menu=query_menu) self.root.config(menu=menubar) 主内容区域 self.notebook = ttk.Notebook(self.root) self.notebook.pack(fill='both', expand=True, padx=10, pady=10) 商户列表标签页 self.merchant_frame = ttk.Frame(self.notebook) self.notebook.add(self.merchant_frame, text="商户列表") self.setup_merchant_list() 状态栏 self.status_bar = tk.Label(self.root, text="就绪", bd=1, relief=tk.SUNKEN, anchor=tk.W) self.status_bar.pack(side=tk.BOTTOM, fill=tk.X) def setup_merchant_list(self): 搜索区域 search_frame = ttk.Frame(self.merchant_frame) search_frame.pack(fill='x', padx=10, pady=10) tk.Label(search_frame, text="搜索:").pack(side=tk.LEFT, padx=5) self.search_entry = tk.Entry(search_frame, width=30) self.search_entry.pack(side=tk.LEFT, padx=5) tk.Button(search_frame, text="搜索", command=self.search_merchants).pack(side=tk.LEFT, padx=5) tk.Button(search_frame, text="刷新", command=self.refresh_merchant_list).pack(side=tk.LEFT) 商户列表表格 columns = ('ID', '商户编码', '商户名称', '联系人', '电话', '楼层', '铺位号') self.merchant_tree = ttk.Treeview(self.merchant_frame, columns=columns, show='headings', height=20) for col in columns: self.merchant_tree.heading(col, text=col) self.merchant_tree.column(col, width=100) scrollbar = ttk.Scrollbar(self.merchant_frame, orient=tk.VERTICAL, command=self.merchant_tree.yview) self.merchant_tree.configure(yscrollcommand=scrollbar.set) self.merchant_tree.pack(side=tk.LEFT, fill='both', expand=True) scrollbar.pack(side=tk.RIGHT, fill='y') 右键菜单 self.merchant_menu = tk.Menu(self.root, tearoff=0) self.merchant_menu.add_command(label="查看详情", command=self.view_merchant_detail) self.merchant_menu.add_command(label="编辑", command=self.edit_merchant) self.merchant_menu.add_command(label="删除", command=self.delete_merchant) self.merchant_tree.bind("", self.show_merchant_menu) def search_merchants(self): keyword = self.search_entry.get() results = self.db.search_merchants(keyword=keyword) self.update_merchant_tree(results) def refresh_merchant_list(self): results = self.db.search_merchants() self.update_merchant_tree(results) self.status_bar.config(text="列表已刷新") def update_merchant_tree(self, data): 清空现有数据 for item in self.merchant_tree.get_children(): self.merchant_tree.delete(item) 插入新数据 for row in data: self.merchant_tree.insert('', 'end', values=row) def show_merchant_menu(self, event): item = self.merchant_tree.identify_row(event.y) if item: self.merchant_tree.selection_set(item) self.merchant_menu.post(event.x_root, event.y_root) def open_add_merchant(self): 创建新增商户窗口 add_window = tk.Toplevel(self.root) add_window.title("新增商户") add_window.geometry("500x400") 创建表单 fields = [ ("商户编码:", "merchant_code"), ("商户名称:", "merchant_name"), ("联系人:", "contact_person"), ("联系电话:", "contact_phone"), ("邮箱:", "email"), ("所在楼层:", "floor"), ("铺位号:", "booth_number"), ("入驻日期:", "entry_date") ] entries = {} for i, (label_text, field_name) in enumerate(fields): tk.Label(add_window, text=label_text).grid(row=i, column=0, padx=10, pady=5, sticky='e') entry = tk.Entry(add_window, width=30) entry.grid(row=i, column=1, padx=10, pady=5) entries[field_name] = entry def save_merchant(): 获取表单数据 data = [] required_fields = ['merchant_code', 'merchant_name'] for field in ['merchant_code', 'merchant_name', 'contact_person', 'contact_phone', 'email', 'floor', 'booth_number', 'entry_date']: value = entries[field].get() if field in required_fields and not value: messagebox.showerror("错误", f"{field}不能为空") return 数据类型转换 if field == 'floor' and value: try: value = int(value) except: value = 0 data.append(value) try: self.db.add_merchant(tuple(data)) messagebox.showinfo("成功", "商户添加成功") add_window.destroy() self.refresh_merchant_list() except Exception as e: messagebox.showerror("错误", f"添加失败: {str(e)}") 按钮区域 button_frame = tk.Frame(add_window) button_frame.grid(row=len(fields), column=0, columnspan=2, pady=20) tk.Button(button_frame, text="保存", command=save_merchant, width=10).pack(side=tk.LEFT, padx=10) tk.Button(button_frame, text="取消", command=add_window.destroy, width=10).pack(side=tk.LEFT) def show_expiring_contracts(self): contracts = self.db.get_expiring_contracts(30) if not contracts: messagebox.showinfo("提示", "未来30天内没有到期的合同") return 显示到期合同窗口 expire_window = tk.Toplevel(self.root) expire_window.title("合同到期提醒") expire_window.geometry("600x300") columns = ('商户名称', '合同编号', '到期日期') tree = ttk.Treeview(expire_window, columns=columns, show='headings') for col in columns: tree.heading(col, text=col) tree.column(col, width=150) for contract in contracts: tree.insert('', 'end', values=contract) tree.pack(fill='both', expand=True, padx=10, pady=10) tk.Label(expire_window, text=f"共发现{len(contracts)}份合同将在30天内到期", fg='red').pack(pady=5) def run(self): 初始化时加载商户列表 self.refresh_merchant_list() self.root.mainloop() 其他窗口方法（编辑、删除、详情查看）需要根据实际需求补充完整 ```

五、数据导入导出功能

5.1 Excel数据导入

创建import_export.py文件：

``` import pandas as pd import sqlite3 from datetime import datetime class DataImporter: def __init__(self, db_path): self.db_path = db_path def import_merchants_from_excel(self, file_path): try: 读取Excel文件 df = pd.read_excel(file_path) 连接数据库 conn = sqlite3.connect(self.db_path) cursor = conn.cursor() 批量插入数据 for _, row in df.iterrows(): cursor.execute(''' INSERT OR IGNORE INTO merchants (merchant_code, merchant_name, contact_person, contact_phone, email, floor, booth_number, entry_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ''', ( str(row.get('商户编码', '')), str(row.get('商户名称', '')), str(row.get('联系人', '')), str(row.get('联系电话', '')), str(row.get('邮箱', '')), int(row.get('楼层', 0)) if pd.notna(row.get('楼层')) else 0, str(row.get('铺位号', '')), row.get('入驻日期', datetime.now().date()) )) conn.commit() conn.close() return True, f"成功导入{len(df)}条记录" except Exception as e: return False, f"导入失败: {str(e)}" def export_merchants_to_excel(self, output_path): conn = sqlite3.connect(self.db_path) query = ''' SELECT merchant_code as 商户编码, merchant_name as 商户名称, contact_person as 联系人, contact_phone as 联系电话, email as 邮箱, floor as 楼层, booth_number as 铺位号, entry_date as 入驻日期 FROM merchants WHERE status = 1 ''' df = pd.read_sql_query(query, conn) conn.close() 导出到Excel df.to_excel(output_path, index=False) return True, f"已导出到{output_path}" ```

5.2 在主界面添加导入导出功能

在MainWindow类的setup_ui方法中，在菜单栏添加：

``` 工具菜单 tool_menu = tk.Menu(menubar, tearoff=0) tool_menu.add_command(label="导入Excel数据", command=self.import_from_excel) tool_menu.add_command(label="导出商户列表", command=self.export_to_excel) menubar.add_cascade(label="工具", menu=tool_menu) ```

然后添加对应的方法：

``` def import_from_excel(self): from tkinter import filedialog from import_export import DataImporter file_path = filedialog.askopenfilename( title="选择Excel文件", filetypes=[