网站专题页面设计欣赏,wordpress网站如何app,个人建站平台,百度手机助手app安卓版官方下载一、功能样式
Python桌面应用之XX学院水卡报表查询系统功能#xff1a;
连接Oracle数据库#xff0c;查询XX学院水卡操作总明细报表#xff0c;汇总数据报表#xff0c;个人明细报表#xff0c;进行预览并且支持导出报表
1.总明细报表样式 2.汇总明细样式
3.个人明细…一、功能样式
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
preopdtself.pretimeInput.get()
nextopdtself.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), showheadings, displaycolumns#all,yscrollcommand scrollBar.set)self.tree.pack()self.tree.heading(outid, text学号, anchortk.W)self.tree.heading(name, text姓名, anchortk.W)self.tree.heading(opfare, text交易金额, anchortk.W)self.tree.heading(opdt, text交易日期, anchortk.W)self.tree.heading(dscrp, text交易类型, anchortk.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 descpreopdtself.pretimeInput.get()nextopdtself.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,valuesitm)self.tree.pack(padx10,pady10, filltk.BOTH,expand1)exportbtn tk.Button(self.consumedetail,text导出,width8,commandself.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(columni)[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(提示, 没有数据无法导出)return5.遍历Treeview表格数据与标题获取Treeview里面的数据与标题这里现获取id值然后通过item获取[‘values’]值获取标题这里先遍历了第一行有多少数据然后使用self.tree.heading(columni)[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(columni)[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,textXX学院水卡报表管理系统,font(kaiti,18)).place(x190,y30)self.outidLab tk.Label(self.root,text学号:).place(x80,y100)self.outidInput tk.Entry(self.root, width20)self.outidInput.place(x130,y100)# 姓名# 学号self.nameLab tk.Label(self.root,text姓名:).place(x380,y100)self.nameInput tk.Entry(self.root,width20)self.nameInput.place(x430,y100)# 起始时间self.mustLabel1 tk.Label(self.root,text*,font(Arial, 16),fg red).place(x45,y160)self.pretimeLab tk.Label(self.root,text起始时间:).place(x55,y160)self.pretimeInput tk.Entry(self.root, width20)self.pretimeInput.place(x130,y160)self.pretimeInput.insert(0,self.pretime)# 终始时间self.mustLabel2 tk.Label(self.root,text*,font(Arial, 16),fg red).place(x350,y160)self.nexttimeLab tk.Label(self.root,text终止时间:).place(x360,y160)self.nexttimeInput tk.Entry(self.root,width20)self.nexttimeInput.place(x430,y160)self.nexttimeInput.insert(0,self.nexttime)self.consumeBtn tk.Button(self.root,text明细查询,commandself.Consumedetail,width10).place(x130,y260)self.sumBtn tk.Button(root,text汇总查询,commandself.sumDetail,width10).place(x300,y260)self.personBtn tk.Button(root,text个人查询,commandself.personDetail,width10).place(x480,y260)# 明细查询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), showheadings, displaycolumns#all,yscrollcommand scrollBar.set)self.tree.pack()self.tree.heading(outid, text学号, anchortk.W)self.tree.heading(name, text姓名, anchortk.W)self.tree.heading(opfare, text交易金额, anchortk.W)self.tree.heading(opdt, text交易日期, anchortk.W)self.tree.heading(dscrp, text交易类型, anchortk.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 descpreopdtself.pretimeInput.get()nextopdtself.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,valuesitm)self.tree.pack(padx10,pady10, filltk.BOTH,expand1)exportbtn tk.Button(self.consumedetail,text导出,width8,commandself.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 dscrpself.sumtail tk.Tk()self.sumtail.title(XX学院汇总查询)self.sumtail.geometry(800x600)# 加载滚动条# exportbtn Button(sumtail,text导出,width8,commandexport).pack()scrollBar tk.Scrollbar(self.sumtail)scrollBar.pack(side tk.RIGHT,fill tk.Y)self.tree ttk.Treeview(self.sumtail, columns(sum, count, dscrp), showheadings, displaycolumns#all,yscrollcommand scrollBar.set)self.tree.pack()self.tree.heading(sum, text总金额, anchortk.W)self.tree.heading(count, text总次数, anchortk.W)self.tree.heading(dscrp, text交易类型, anchortk.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 descpreopdtself.pretimeInput.get()nextopdtself.nexttimeInput.get()data self.cursor.execute(sql,(preopdt,nextopdt)).fetchall()# print(data)for itm in data:self.tree.insert(,tk.END,valuesitm)self.tree.pack(padx10,pady10, filltk.BOTH,expand1)exportbtn tk.Button(self.sumtail,text导出,width8,commandself.export).pack()# 个人明细def personDetail(self):if(self.outidInput.get()):print(outid not is null)sqlselect 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 descoutidname self.outidInput.get()elif(self.nameInput.get()):sqlselect 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 descoutidname self.nameInput.get()else:messagebox.showwarning(提示, 请输入学号或者姓名)returnself.persontail tk.Tk()self.persontail.title(XX学院个人查询)self.persontail.geometry(1200x600)# 加载滚动条# exportbtn Button(persontail,text导出,width8,commandexport).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), showheadings, displaycolumns#all,yscrollcommand scrollBar.set)self.tree.pack()self.tree.heading(outid, text学号, anchortk.W)self.tree.heading(name, text姓名, anchortk.W)self.tree.heading(opfare, text交易金额, anchortk.W)self.tree.heading(oddfare, text账户余额, anchortk.W)self.tree.heading(opdt, text交易日期, anchortk.W)self.tree.heading(dscrp, text交易类型, anchortk.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 descpreopdtself.pretimeInput.get()nextopdtself.nexttimeInput.get()# print(outidname)data self.cursor.execute(sql,(preopdt,nextopdt,outidname)).fetchall()# print(data)for itm in data:self.tree.insert(,tk.END,valuesitm)self.tree.pack(padx10,pady10, filltk.BOTH,expand1)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导出,width8,commandself.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(columni)[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()
