Python桌面应用之XX学院水卡报表查询系统（Tkinter+cx_Oracle）

一、功能样式

Python桌面应用之XX学院水卡报表查询系统功能：

连接Oracle数据库，查询XX学院水卡操作总明细报表，汇总数据报表，个人明细报表，进行预览并且支持导出报表

1.总明细报表样式

2.汇总明细样式

3.个人明细样式

4.导出报表样式

5.错误提示样式

二、核心点

1. 安装cx_Oracle：使用cx_Oracle三方库连接Oracle，该库使用的python版本略低，可以在[https://cx-oracle.readthedocs.io/en/latest/](https://cx-oracle.readthedocs.io/en/latest/进行查询，安装前先确定：python版本、Orale客户端版本（要不都是64位，要不都是32位），安装cx_Oracle的版本位数是跟python的位数相关的。
使用代码进行测试

import cx_Oracle
# 账户  密码  ip:1521/实例名
conn = cx_Oracle.connect('system','Yxq123456','127.0.0.1:1521/ecard'
# 挂载数据库连接游标
self.cursor = conn.cursor()
print('连接数据库成功！')

2. 多参数查询Sql： sql语句使用:参数名来定义参数，多参数使用cursor.execute(sql,(参数1，参数2)).fetchall()来查询

sql = "select a.outid ,a.name ,b.opfare,b.opdt,b.dscrp from base_customers a,rec_cust_acc b where a.customerid = b. customerid and b.opdt >= to_date(:preopdt,'yyyy-MM-dd HH24:mi:ss') and b.opdt <= to_date(:nextopdt,'yyyy-MM-dd HH24:mi:ss') order by b.opdt desc"
preopdt=self.pretimeInput.get()
nextopdt=self.nexttimeInput.get()
data = self.cursor.execute(sql,(preopdt,nextopdt)).fetchall()

**3. Treeview表格组件的使用：**这里使用了三个报表，其实可以将打开的Treeview做成一个表格class类，要使用的时候直接生成要使用的对象，传入该对象的大小，heading标题，data数据即可。

# 明细查询def Consumedetail(self):self.consumedetail = tk.Tk()self.consumedetail.title('XX学院明细查询')self.consumedetail.geometry("1000x600")# 加载滚动条scrollBar = tk.Scrollbar(self.consumedetail)scrollBar.pack(side = tk.RIGHT,fill = tk.Y)self.tree = ttk.Treeview(self.consumedetail, columns=('outid', 'name', 'opfare', 'opdt','dscrp'), show="headings", displaycolumns="#all",yscrollcommand = scrollBar.set)self.tree.pack()self.tree.heading('outid', text="学号", anchor=tk.W)self.tree.heading('name', text="姓名", anchor=tk.W)self.tree.heading('opfare', text="交易金额", anchor=tk.W)self.tree.heading('opdt', text="交易日期", anchor=tk.W)self.tree.heading('dscrp', text="交易类型", anchor=tk.W)# 设置关联scrollBar.config(command = self.tree.yview)# 每次打开清空页面for item in self.tree.get_children():self.consumedetail.tree.delete(item)sql = "select a.outid ,a.name ,b.opfare,b.opdt,b.dscrp from base_customers a,rec_cust_acc b where a.customerid = b. customerid and b.opdt >= to_date(:preopdt,'yyyy-MM-dd HH24:mi:ss') and b.opdt <= to_date(:nextopdt,'yyyy-MM-dd HH24:mi:ss') order by b.opdt desc"preopdt=self.pretimeInput.get()nextopdt=self.nexttimeInput.get()data = self.cursor.execute(sql,(preopdt,nextopdt)).fetchall()# print(data)# data = [['2013090101','张三','100','2023-10-19','PC存款']]for itm in data:self.tree.insert("",tk.END,values=itm)self.tree.pack(padx=10,pady=10, fill=tk.BOTH,expand=1)exportbtn = tk.Button(self.consumedetail,text='导出',width=8,command=self.export).pack()

4. 导出数据自定义文件名：报表里面导出数据，其实使用遍历treeview组件数据，在进行整理后写入csv文件，自定义文件名是使用filedialog.asksaveasfilename来打开文件框，里面的文件类型使用参数filetypes ，输入文件名后获取名称生成文件。这里导出的文件就只是csv文件，如果需要其他文件类型，可以自行导入其他三方库。

     def export(self):# 导出export        # 打开文件夹选择对话框# 更新标签文本# print(folder_path)list = []columns = []# 获取表格内容idfor row_id in self.tree.get_children():list.append(self.tree.item(row_id)['values'])print(len(self.tree.get_children()))   # 通过第一行获取列数生成标题# print(self.tree.item)if len(self.tree.get_children()) != 0:print('ok')folder_path = filedialog.asksaveasfilename(title='请选择一个文件',filetypes=[("CSV", ".csv")]) for i in range(0,len(self.tree.item('I001')['values'])):columns.append(self.tree.heading(column=i)['text'])# 导出csvwith open(f'{folder_path}.csv','w',newline='') as csvfile:fieldnames = columnswriter = csv.writer(csvfile)writer.writerow(fieldnames)writer.writerows(list)else:messagebox.showwarning("提示", "没有数据，无法导出")return

5.遍历Treeview表格数据与标题：获取Treeview里面的数据与标题，这里现获取id值，然后通过item获取[‘values’]值，获取标题这里先遍历了第一行有多少数据，然后使用self.tree.heading(column=i)['text']来获取标题。

 # 获取表格内容id
for row_id in self.tree.get_children():list.append(self.tree.item(row_id)['values'])

 # 通过第一行获取列数生成标题
for i in range(0,len(self.tree.item('I001')['values'])):columns.append(self.tree.heading(column=i)['text'])

三、完整代码

import tkinter as tk
from tkinter import ttk
from tkinter import messagebox
import cx_Oracle
import time
import csv
from tkinter import filedialog# mainapp
class mainApp(object):def __init__(self,parent,**kwargs):self.root = parentcurrent_timestamp = time.time()# 将时间戳转换为本地时间的struct_time对象local_time = time.localtime(current_timestamp)# 使用strftime()方法将struct_time对象格式化为指定的时间字符串  # 挂在self时间self.pretime = time.strftime("%Y-%m-%d 00:00:00", local_time)self.nexttime = time.strftime("%Y-%m-%d %H:%M:%S", local_time)conn = cx_Oracle.connect('system','Yxq123456','127.0.0.1:1521/ecard')# conn = cx_Oracle.connect('ccense','XCXY123*','127.0.0.1:1521/ecard')# 挂载数据库连接游标self.cursor = conn.cursor()print('连接数据库成功！')self.root.config(**kwargs)self.root.title('XX学院')self.root.resizable(False, False)self.create_widgets()# 获取屏幕尺寸screen_width = self.root.winfo_screenwidth()screen_height = self.root.winfo_screenheight()# 确定窗口位置，并设置大小x_coordinate = (screen_width / 2) - 300 # 300是窗口的宽度y_coordinate = (screen_height / 2) - 200 # 200是窗口的高度self.root.geometry('650x400+{}+{}'.format(int(x_coordinate), int(y_coordinate)))# self.root.geometry("600x400")# 创建UIdef create_widgets(self):self.titleLab = tk.Label(self.root,text='XX学院水卡报表管理系统',font=("kaiti",18)).place(x=190,y=30)self.outidLab = tk.Label(self.root,text='学号:').place(x=80,y=100)self.outidInput = tk.Entry(self.root, width=20)self.outidInput.place(x=130,y=100)# 姓名# 学号self.nameLab = tk.Label(self.root,text='姓名:').place(x=380,y=100)self.nameInput = tk.Entry(self.root,width=20)self.nameInput.place(x=430,y=100)# 起始时间self.mustLabel1 = tk.Label(self.root,text='*',font=('Arial', 16),fg = 'red').place(x=45,y=160)self.pretimeLab = tk.Label(self.root,text='起始时间:').place(x=55,y=160)self.pretimeInput = tk.Entry(self.root, width=20)self.pretimeInput.place(x=130,y=160)self.pretimeInput.insert(0,self.pretime)# 终始时间self.mustLabel2 = tk.Label(self.root,text='*',font=('Arial', 16),fg = 'red').place(x=350,y=160)self.nexttimeLab = tk.Label(self.root,text='终止时间:').place(x=360,y=160)self.nexttimeInput = tk.Entry(self.root,width=20)self.nexttimeInput.place(x=430,y=160)self.nexttimeInput.insert(0,self.nexttime)self.consumeBtn = tk.Button(self.root,text='明细查询',command=self.Consumedetail,width=10).place(x=130,y=260)self.sumBtn = tk.Button(root,text='汇总查询',command=self.sumDetail,width=10).place(x=300,y=260)self.personBtn = tk.Button(root,text='个人查询',command=self.personDetail,width=10).place(x=480,y=260)# 明细查询def Consumedetail(self):self.consumedetail = tk.Tk()self.consumedetail.title('XX学院明细查询')self.consumedetail.geometry("1000x600")# 加载滚动条scrollBar = tk.Scrollbar(self.consumedetail)scrollBar.pack(side = tk.RIGHT,fill = tk.Y)self.tree = ttk.Treeview(self.consumedetail, columns=('outid', 'name', 'opfare', 'opdt','dscrp'), show="headings", displaycolumns="#all",yscrollcommand = scrollBar.set)self.tree.pack()self.tree.heading('outid', text="学号", anchor=tk.W)self.tree.heading('name', text="姓名", anchor=tk.W)self.tree.heading('opfare', text="交易金额", anchor=tk.W)self.tree.heading('opdt', text="交易日期", anchor=tk.W)self.tree.heading('dscrp', text="交易类型", anchor=tk.W)# 设置关联scrollBar.config(command = self.tree.yview)# 每次打开清空页面for item in self.tree.get_children():self.consumedetail.tree.delete(item)sql = "select a.outid ,a.name ,b.opfare,b.opdt,b.dscrp from base_customers a,rec_cust_acc b where a.customerid = b. customerid and b.opdt >= to_date(:preopdt,'yyyy-MM-dd HH24:mi:ss') and b.opdt <= to_date(:nextopdt,'yyyy-MM-dd HH24:mi:ss') order by b.opdt desc"preopdt=self.pretimeInput.get()nextopdt=self.nexttimeInput.get()data = self.cursor.execute(sql,(preopdt,nextopdt)).fetchall()# print(data)# data = [['2013090101','张三','100','2023-10-19','PC存款']]for itm in data:self.tree.insert("",tk.END,values=itm)self.tree.pack(padx=10,pady=10, fill=tk.BOTH,expand=1)exportbtn = tk.Button(self.consumedetail,text='导出',width=8,command=self.export).pack()# 汇总查询def sumDetail(self):sql = "select sum(opfare),count(acccode),dscrp from rec_cust_acc where opdt >= to_date(:preopdt,'yyyy-MM-dd HH24:mi:ss') and opdt <= to_date(:nextopdt,'yyyy-MM-dd HH24:mi:ss') group by dscrp"self.sumtail = tk.Tk()self.sumtail.title('XX学院汇总查询')self.sumtail.geometry("800x600")# 加载滚动条# exportbtn = Button(sumtail,text='导出',width=8,command=export).pack()scrollBar = tk.Scrollbar(self.sumtail)scrollBar.pack(side = tk.RIGHT,fill = tk.Y)self.tree = ttk.Treeview(self.sumtail, columns=('sum', 'count', 'dscrp'), show="headings", displaycolumns="#all",yscrollcommand = scrollBar.set)self.tree.pack()self.tree.heading('sum', text="总金额", anchor=tk.W)self.tree.heading('count', text="总次数", anchor=tk.W)self.tree.heading('dscrp', text="交易类型", anchor=tk.W)# 设置关联scrollBar.config(command = self.tree.yview)# 每次打开清空页面for item in self.tree.get_children():self.tree.delete(item)# sql = "select a.outid ,a.name ,b.opfare,b.opdt,b.dscrp from base_customers a,rec_cust_acc b where a.customerid = b. customerid and b.opdt >= to_date(:preopdt,'yyyy-MM-dd HH24:mi:ss') and b.opdt <= to_date(:nextopdt,'yyyy-MM-dd HH24:mi:ss') order by b.opdt desc"preopdt=self.pretimeInput.get()nextopdt=self.nexttimeInput.get()data = self.cursor.execute(sql,(preopdt,nextopdt)).fetchall()# print(data)for itm in data:self.tree.insert("",tk.END,values=itm)self.tree.pack(padx=10,pady=10, fill=tk.BOTH,expand=1)exportbtn = tk.Button(self.sumtail,text='导出',width=8,command=self.export).pack()# 个人明细def personDetail(self):if(self.outidInput.get()):print('outid not is null')sql="select a.outid ,a.name ,b.opfare,b.oddfare,b.opdt,b.dscrp from base_customers a,rec_cust_acc b where a.customerid = b. customerid and b.opdt >= to_date(:preopdt,'yyyy-MM-dd HH24:mi:ss') and b.opdt <= to_date(:nextopdt,'yyyy-MM-dd HH24:mi:ss') and a.outid = :outid order by b.opdt desc"outidname = self.outidInput.get()elif(self.nameInput.get()):sql="select a.outid ,a.name ,b.opfare,b.oddfare,b.opdt,b.dscrp from base_customers a,rec_cust_acc b where a.customerid = b. customerid and b.opdt >= to_date(:preopdt,'yyyy-MM-dd HH24:mi:ss') and b.opdt <= to_date(:nextopdt,'yyyy-MM-dd HH24:mi:ss') and a.name like :name order by b.opdt desc"outidname = self.nameInput.get()else:messagebox.showwarning("提示", "请输入学号或者姓名！")returnself.persontail = tk.Tk()self.persontail.title('XX学院个人查询')self.persontail.geometry("1200x600")# 加载滚动条# exportbtn = Button(persontail,text='导出',width=8,command=export).pack()scrollBar = tk.Scrollbar(self.persontail)scrollBar.pack(side = tk.RIGHT,fill = tk.Y)self.tree = ttk.Treeview(self.persontail, columns=('outid', 'name', 'opfare','oddfare', 'opdt','dscrp'), show="headings", displaycolumns="#all",yscrollcommand = scrollBar.set)self.tree.pack()self.tree.heading('outid', text="学号", anchor=tk.W)self.tree.heading('name', text="姓名", anchor=tk.W)self.tree.heading('opfare', text="交易金额", anchor=tk.W)self.tree.heading('oddfare', text="账户余额", anchor=tk.W)self.tree.heading('opdt', text="交易日期", anchor=tk.W)self.tree.heading('dscrp', text="交易类型", anchor=tk.W)# 设置关联scrollBar.config(command = self.tree.yview)# 每次打开清空页面for item in self.tree.get_children():self.tree.delete(item)# sql = "select a.outid ,a.name ,b.opfare,b.opdt,b.dscrp from base_customers a,rec_cust_acc b where a.customerid = b. customerid and b.opdt >= to_date(:preopdt,'yyyy-MM-dd HH24:mi:ss') and b.opdt <= to_date(:nextopdt,'yyyy-MM-dd HH24:mi:ss') order by b.opdt desc"preopdt=self.pretimeInput.get()nextopdt=self.nexttimeInput.get()# print(outidname)data = self.cursor.execute(sql,(preopdt,nextopdt,outidname)).fetchall()# print(data)for itm in data:self.tree.insert("",tk.END,values=itm)self.tree.pack(padx=10,pady=10, fill=tk.BOTH,expand=1)def export():# 导出export        # 打开文件夹选择对话框folder_path = filedialog.asksaveasfilename(title='请选择一个文件',filetypes=[("CSV", ".csv")]) # 更新标签文本print(folder_path)list = []for row_id in self.tree.get_children():list.append(self.tree.item(row_id)['values'])with open(f'{folder_path}.csv','w',newline='') as csvfile:fieldnames = ['学号', '姓名', '交易金额','账户余额','交易日期','交易类型']writer = csv.writer(csvfile)writer.writerow(fieldnames)writer.writerows(list)exportbtn = tk.Button(self.persontail,text='导出',width=8,command=self.export).pack()def export(self):# 导出export        # 打开文件夹选择对话框# 更新标签文本# print(folder_path)list = []columns = []# 获取表格内容idfor row_id in self.tree.get_children():list.append(self.tree.item(row_id)['values'])print(len(self.tree.get_children()))   # 通过第一行获取列数生成标题# print(self.tree.item)if len(self.tree.get_children()) != 0:print('ok')folder_path = filedialog.asksaveasfilename(title='请选择一个文件',filetypes=[("CSV", ".csv")]) for i in range(0,len(self.tree.item('I001')['values'])):columns.append(self.tree.heading(column=i)['text'])# 导出csvwith open(f'{folder_path}.csv','w',newline='') as csvfile:fieldnames = columnswriter = csv.writer(csvfile)writer.writerow(fieldnames)writer.writerows(list)else:messagebox.showwarning("提示", "没有数据，无法导出")returnif __name__ == "__main__":root = tk.Tk()app =  mainApp(root)root.mainloop()