背景
需要给客户提供数据，但是这个数据拿Excel放不下，但是又不值得开发应用展示，就是临时看一下~客户又不会用数据库，只会基础的Excel。 所以有了下面的想法：
- 这个小程序可以直接读取数据库（sqlite-免安装，支持大数据量）
- 支持简单的搜索过滤 （也支持SQL）
- 导出数据
实现
使用python的tkinter库实现，然后使用pyinstaller打包成可支持程序。效果如下：
下载地址：
https://gitcode.com/qq_30901367/worker/releases/download/sqlite_view/DataView_v2.exe
使用说明
目录结构如下：
config.ini # 配置文件
DataView.exe # 程序
example.db
- 主要是配置
config.ini文件即可：
[database]
db_path = example.db
table_name = users
columns = id, name, age, email, phone
column_labels = ID, 姓名, 年龄, 邮箱, 手机号
sort_rule = id ASC
注：仅是临时性的程序，功能简单
代码实现
import tkinter as tk
from tkinter import ttk, messagebox, scrolledtext
import sqlite3
import configparser
import os
import csv
from tkinter import filedialog
class SQLiteBrowserApp:
def __init__(self, root):
self.root = root
self.root.title("SQLite数据浏览应用")
self.root.geometry("900x600")
# 配置和数据库相关变量
self.config = configparser.ConfigParser()
self.db_path = ""
self.table_name = ""
self.columns = []
self.column_labels = []
self.sort_rule = ""
self.current_page = 1
self.items_per_page = 10
self.total_pages = 1
# 加载配置
self.load_config()
# 创建UI
self.create_widgets()
# 加载数据
self.load_data()
def load_config(self):
"""加载配置文件"""
try:
if os.path.exists('config.ini'):
self.config.read('config.ini')
if 'database' in self.config:
db_section = self.config['database']
self.db_path = db_section.get('db_path', '')
self.table_name = db_section.get('table_name', '')
columns_str = db_section.get('columns', '')
labels_str = db_section.get('column_labels', '')
self.sort_rule = db_section.get('sort_rule', '')
if columns_str and labels_str:
self.columns = [col.strip() for col in columns_str.split(',')]
self.column_labels = [label.strip() for label in labels_str.split(',')]
if len(self.columns) != len(self.column_labels):
messagebox.showerror("配置错误", "列名和标签数量不匹配")
else:
messagebox.showerror("配置错误", "缺少列名或标签配置")
else:
messagebox.showerror("配置错误", "配置文件中缺少 [database] 部分")
else:
messagebox.showerror("配置错误", "配置文件 config.ini 不存在")
except Exception as e:
messagebox.showerror("配置错误", f"加载配置文件时出错: {str(e)}")
def create_widgets(self):
"""创建UI组件"""
# 主框架
main_frame = ttk.Frame(self.root, padding="10")
main_frame.pack(fill=tk.BOTH, expand=True)
# 搜索框框架
search_frame = ttk.Frame(main_frame)
search_frame.pack(fill=tk.X, pady=(0, 10))
ttk.Label(search_frame, text="搜索:").pack(side=tk.LEFT, padx=(0, 5))
self.search_var = tk.StringVar()
search_entry = ttk.Entry(search_frame, textvariable=self.search_var, width=50)
search_entry.pack(side=tk.LEFT, fill=tk.X, expand=True)
search_button = ttk.Button(search_frame, text="搜索", command=self.search_data)
search_button.pack(side=tk.LEFT, padx=5)
clear_button = ttk.Button(search_frame, text="清除", command=self.clear_search)
clear_button.pack(side=tk.LEFT)
# SQL查询框框架
sql_frame = ttk.Frame(main_frame)
sql_frame.pack(fill=tk.X, pady=(0, 10))
ttk.Label(sql_frame, text="SQL查询:").pack(side=tk.LEFT, padx=(0, 5))
self.sql_var = tk.StringVar()
self.sql_entry = ttk.Entry(sql_frame, textvariable=self.sql_var, width=50)
self.sql_entry.pack(side=tk.LEFT, fill=tk.X, expand=True)
sql_button = ttk.Button(sql_frame, text="执行", command=self.execute_sql)
sql_button.pack(side=tk.LEFT, padx=5)
# 每页显示数量框架
page_size_frame = ttk.Frame(main_frame)
page_size_frame.pack(fill=tk.X, pady=(0, 10))
ttk.Label(page_size_frame, text="每页显示:").pack(side=tk.LEFT, padx=(0, 5))
self.page_size_var = tk.StringVar(value=str(self.items_per_page))
page_size_combo = ttk.Combobox(page_size_frame, textvariable=self.page_size_var, values=["10", "20", "50", "100"], width=5)
page_size_combo.pack(side=tk.LEFT)
page_size_combo.bind("<<ComboboxSelected>>", self.change_page_size)
ttk.Label(page_size_frame, text="条记录").pack(side=tk.LEFT, padx=5)
# 表格框架
table_frame = ttk.Frame(main_frame)
table_frame.pack(fill=tk.BOTH, expand=True)
# 创建表格
self.create_table(table_frame)
# 分页控制框架
pagination_frame = ttk.Frame(main_frame)
pagination_frame.pack(fill=tk.X, pady=10)
self.prev_button = ttk.Button(pagination_frame, text="上一页", command=self.prev_page)
self.prev_button.pack(side=tk.LEFT)
self.page_label = ttk.Label(pagination_frame, text="")
self.page_label.pack(side=tk.LEFT, padx=10)
self.next_button = ttk.Button(pagination_frame, text="下一页", command=self.next_page)
self.next_button.pack(side=tk.LEFT)
def create_table(self, parent):
"""创建表格组件"""
# 创建Treeview
columns_tuple = tuple(self.columns)
self.tree = ttk.Treeview(parent, columns=columns_tuple, show="headings")
# 设置列标题
for col, label in zip(self.columns, self.column_labels):
self.tree.heading(col, text=label)
self.tree.column(col, width=100, anchor=tk.CENTER)
# 添加垂直滚动条
vsb = ttk.Scrollbar(parent, orient="vertical", command=self.tree.yview)
self.tree.configure(yscrollcommand=vsb.set)
# 布局
vsb.pack(side=tk.RIGHT, fill=tk.Y)
self.tree.pack(fill=tk.BOTH, expand=True)
def load_data(self, search_text=""):
"""加载数据到表格"""
if not self.db_path or not self.table_name or not self.columns:
return
try:
conn = sqlite3.connect(self.db_path)
cursor = conn.cursor()
# 构建查询
base_query = f"SELECT {', '.join(self.columns)} FROM {self.table_name}"
count_query = f"SELECT COUNT(*) FROM {self.table_name}"
# 添加搜索条件
if search_text:
conditions = [f"{col} LIKE ?" for col in self.columns]
where_clause = "WHERE " + " OR ".join(conditions)
base_query += " " + where_clause
count_query += " " + where_clause
params = tuple([f"%{search_text}%"] * len(self.columns))
else:
params = ()
# 添加排序
if self.sort_rule:
base_query += f" ORDER BY {self.sort_rule}"
# 计算分页
start_idx = (self.current_page - 1) * self.items_per_page
limit_query = f"{base_query} LIMIT ? OFFSET ?"
limit_params = params + (self.items_per_page, start_idx)
# 获取总记录数
cursor.execute(count_query, params)
total_records = cursor.fetchone()[0]
# 计算总页数
self.total_pages = max(1, (total_records + self.items_per_page - 1) // self.items_per_page)
# 清空表格
for item in self.tree.get_children():
self.tree.delete(item)
# 获取当前页数据
cursor.execute(limit_query, limit_params)
rows = cursor.fetchall()
# 填充表格
for row in rows:
self.tree.insert("", tk.END, values=row)
# 更新分页信息
self.update_pagination_info()
conn.close()
except Exception as e:
messagebox.showerror("数据库错误", f"加载数据时出错: {str(e)}")
def update_pagination_info(self):
"""更新分页信息和按钮状态"""
self.page_label.config(text=f"第 {self.current_page} 页，共 {self.total_pages} 页")
# 禁用/启用按钮
self.prev_button.config(state=tk.NORMAL if self.current_page > 1 else tk.DISABLED)
self.next_button.config(state=tk.NORMAL if self.current_page < self.total_pages else tk.DISABLED)
def prev_page(self):
"""上一页"""
if self.current_page > 1:
self.current_page -= 1
self.load_data(self.search_var.get())
def next_page(self):
"""下一页"""
if self.current_page < self.total_pages:
self.current_page += 1
self.load_data(self.search_var.get())
def change_page_size(self, event=None):
"""更改每页显示数量"""
try:
self.items_per_page = int(self.page_size_var.get())
self.current_page = 1 # 重置为第一页
self.load_data(self.search_var.get())
except ValueError:
messagebox.showerror("输入错误", "请输入有效的数字")
self.page_size_var.set(str(self.items_per_page))
def search_data(self):
"""搜索数据"""
search_text = self.search_var.get().strip()
self.current_page = 1 # 重置为第一页
self.load_data(search_text)
def clear_search(self):
"""清除搜索"""
self.search_var.set("")
self.current_page = 1 # 重置为第一页
self.load_data()
def execute_sql(self):
"""执行SQL查询"""
sql = self.sql_var.get().strip()
if not sql:
messagebox.showwarning("警告", "请输入SQL查询语句")
return
try:
conn = sqlite3.connect(self.db_path)
cursor = conn.cursor()
# 执行查询
cursor.execute(sql)
rows = cursor.fetchall()
# 获取列名
column_names = [desc[0] for desc in cursor.description]
conn.close()
# 显示结果
if rows:
self.show_query_result(rows, column_names)
else:
messagebox.showinfo("查询结果", "查询返回0条记录")
except Exception as e:
messagebox.showerror("SQL错误", f"执行SQL时出错: {str(e)}")
def show_query_result(self, rows, column_names):
"""在新窗口中显示查询结果"""
result_window = tk.Toplevel(self.root)
result_window.title("查询结果")
result_window.geometry("800x500")
# 创建表格
result_tree = ttk.Treeview(result_window, columns=column_names, show="headings")
# 设置列标题
for col in column_names:
result_tree.heading(col, text=col)
result_tree.column(col, width=100, anchor=tk.CENTER)
# 添加垂直滚动条
vsb = ttk.Scrollbar(result_window, orient="vertical", command=result_tree.yview)
result_tree.configure(yscrollcommand=vsb.set)
# 填充表格
for row in rows:
result_tree.insert("", tk.END, values=row)
# 布局
vsb.pack(side=tk.RIGHT, fill=tk.Y)
result_tree.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)
# 导出按钮
export_button = ttk.Button(result_window, text="导出为CSV",
command=lambda: self.export_to_csv(rows, column_names))
export_button.pack(pady=10)
def export_to_csv(self, rows, column_names):
"""导出数据为CSV文件"""
if not rows:
messagebox.showinfo("导出", "没有数据可导出")
return
file_path = filedialog.asksaveasfilename(
defaultextension=".csv",
filetypes=[("CSV文件", "*.csv"), ("所有文件", "*.*")],
title="保存CSV文件"
)
if not file_path:
return
try:
with open(file_path, 'w', newline='', encoding='utf-8-sig') as csvfile:
writer = csv.writer(csvfile)
# 写入表头
writer.writerow(column_names)
# 写入数据
for row in rows:
writer.writerow(row)
messagebox.showinfo("导出成功", f"数据已成功导出到 {file_path}")
except Exception as e:
messagebox.showerror("导出错误", f"导出数据时出错: {str(e)}")
if __name__ == "__main__":
root = tk.Tk()
app = SQLiteBrowserApp(root)
root.mainloop()
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
