1.使用python3 Tkinker编写操作数据库的桌面并打包成exe
2.主要应用本人学习以及实际的使用,代码中尚有不足之处。
主要的实现功能为:查询:按时间段查询以及查询当天数;批量更新;
使用网格布局的方式(grid)
文本框输入读取数据库进行查询的动态适配自己的一类处理需求
环境:
使用mongodb数据库操作
windows系统
anconda3
tkinter
datetime
打包程序看我之前的博文
整体代码如下
import tkinter
import pymongo
import datetime
def operatesql():
root = tkinter.Tk() # 创建窗体
# root.resizable(0,0) # 不允许调整窗口大小
root.geometry("718x600") # 创建设置窗口大小
root.title("数据库查询当日采集量")
# 背景图片
file = Image.open('1.jpg')
photo = ImageTk.PhotoImage(file)
#创建标签 并且添加到主窗口中
tkinter.Label(root,text='操作Mongodb数据库',font=("华文行楷", 17), fg="green")
# 创建frame框架
frame= tkinter.Frame(root,bd=1,relief="sunken")
frame.grid(row=0, column=0)
tkinter.Label(frame,text='操作Mongodb数据库',font=("华文行楷", 17), fg="green").grid(row=0,column=0)
# 将frame框架修改成
frame_l=tkinter.Frame(frame,bd=1,relief="sunken")
frame_l.grid(row=1,column=0)
tkinter.Label(frame_l,width=55,height=60).grid(row=1, column=0)
frame_r=tkinter.Frame(frame,bd=1,relief="sunken")
frame_r.grid(row=1,column=1)
tkinter.Label(frame_r,width=45,height=60).grid(row=1, column=0)
frame_l_t = tkinter.Frame(frame_l)
frame_l_t.grid(row=0,column=0)
global db,co,s,e,t
frame_l_t_l=tkinter.Frame(frame_l_t)
frame_l_t_l.grid(row=0,column=0)
tkinter.Label(frame_l_t_l,text="数据库名:").grid(row=0,column=0)
db = tkinter.Entry(frame_l_t_l,show=None,width=30)
db.focus_set()
db.grid(row=0,column=1)
tkinter.Label(frame_l_t_l,text="集合名:").grid(row=1,column=0)
co = tkinter.Entry(frame_l_t_l,show=None,width=30)
co.focus_set()
co.grid(row=1,column=1)
tkinter.Label(frame_l_t_l,text="查询结果如下:",font=("华文行楷", 10), fg="green").grid(row=4,column=0)
t = tkinter.Text(frame_l_t,width=45)
# t.configure(state='disabled')
t.grid(row=6,column=0)
frame_r_t = tkinter.Frame(frame_r)
frame_r_t.grid(row=0,column=0)
tkinter.Label(frame_r_t,text="请选择以下需要的进操作",font=("华文行楷", 10), fg="green").grid(row=5,column=0)
tkinter.Label(frame_r_t).grid(row=6,column=0)
tkinter.Label(frame_r_t).grid(row=7,column=0)
tkinter.Label(frame_r_t,text="查询数据库总数:").grid(row=8,column=0)
def getpicknum():
db_name = db.get()
co_name = co.get()
if db_name and co_name:
client = pymongo.MongoClient(host='localhost',port=27017)
database = client[str(db_name)]
collection = database[str(co_name)]
num = collection.find().count()
t.configure(state='normal')
t.insert("insert",datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+db_name+'\n')
t.insert("insert",datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+co_name+'\n')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"采集到数据:"+str(num)+'\n')
t.configure(state='disabled')
else:
t.configure(state='normal')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"请输入数据库名与集合"+'\n')
t.configure(state='disabled')
tkinter.Button(frame_r_t,text="确定",bd=1,width=8,command=getpicknum).grid(row=8,column=1)
tkinter.Label(frame_r_t).grid(row=9,column=0)
tkinter.Label(frame_r_t,text="查询当天采集量:").grid(row=10,column=0)
def getdaypicknum():
db_name = db.get()
co_name = co.get()
if db_name and co_name:
client = pymongo.MongoClient(host='localhost',port=27017)
database = client[str(db_name)]
collection = database[str(co_name)]
r = collection.count({"url_pickdate":{"$exists":True}})
if r!=0:
num = collection.find({"url_pickdate":{'$regex':datetime.datetime.now().strftime('%Y/%m/%d')}}).count()
t.configure(state='normal')
t.insert("insert",datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+db_name+'\n')
t.insert("insert",datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+co_name+'\n')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+datetime.datetime.now().strftime('%Y-%m-%d')+"采集到数据"+str(num)+'\n')
t.configure(state='disabled')
else:
t.configure(state='normal')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"不存在url_pickdate字段"+'\n')
t.configure(state='disabled')
else:
t.configure(state='normal')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"请输入数据库名与集合"+'\n')
t.configure(state='disabled')
tkinter.Button(frame_r_t,text="确定",bd=1,width=8,command=getdaypicknum).grid(row=10,column=1)
tkinter.Label(frame_r_t).grid(row=11,column=0)
tkinter.Label(frame_r_t,text="采集完成》待采集:").grid(row=12,column=0)
def setfinishstate():
db_name = db.get()
co_name = co.get()
if db_name and co_name:
client = pymongo.MongoClient(host='localhost',port=27017)
database = client[str(db_name)]
collection = database[str(co_name)]
r = collection.count({"url_pickstatus":{"$exists":True}})
if r!=0:
collection.update_many({"url_pickstatus":2},{"$set":{"url_pickstatus":0}})
t.configure(state='normal')
t.insert("insert",datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+db_name+'\n')
t.insert("insert",datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+co_name+'\n')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"修改采集完成》待采集状态成功!"+'\n')
t.configure(state='disabled')
else:
t.configure(state='normal')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"不存在url_pickstatus字段"+'\n')
t.configure(state='disabled')
else:
t.configure(state='normal')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"请输入数据库名与集合"+'\n')
t.configure(state='disabled')
tkinter.Button(frame_r_t,text="确定",bd=1,width=8,command=setfinishstate).grid(row=12,column=1)
tkinter.Label(frame_r_t).grid(row=13,column=0)
tkinter.Label(frame_r_t,text="正在采集》待采集:").grid(row=14,column=0)
def setcurrentstate():
db_name = db.get()
co_name = co.get()
if db_name and co_name:
client = pymongo.MongoClient(host='localhost',port=27017)
database = client[str(db_name)]
collection = database[str(co_name)]
r = collection.count({"url_pickstatus":{"$exists":True}})
if r!=0:
collection.update_many({"url_pickstatus":1},{"$set":{"url_pickstatus":0}})
t.configure(state='normal')
t.insert("insert",datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+db_name+'\n')
t.insert("insert",datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+co_name+'\n')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"修改正在采集》待采集状态成功!"+'\n')
t.configure(state='disabled')
else:
t.configure(state='normal')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"不存在url_pickstatus字段名"+'\n')
t.configure(state='disabled')
else:
t.configure(state='normal')
t.insert( 'insert',datetime.datetime.now().strftime( '%Y-%m-%d %H:%M:%S' ) + ':' + "请输入数据库名与集合" + '\n' )
t.configure(state='disabled')
tkinter.Button(frame_r_t,text="确定",bd=1,width=8,command=setcurrentstate).grid(row=14,column=1)
tkinter.Label(frame_r_t).grid(row=15,column=0)
tkinter.Label(frame_r_t,text="请输入查询日期:2020/11/2").grid(row=16,column=0)
tkinter.Label(frame_r_t,text="starttime:").grid(row=17,column=0)
s = tkinter.Entry(frame_r_t,show=None)
s.grid(row=17,column=1)
s.focus_set()
tkinter.Label(frame_r_t,text="endtime:").grid(row=18,column=0)
e = tkinter.Entry(frame_r_t,show=None)
e.grid(row=18,column=1)
e.focus_set()
def gettimevalue():
db_name = db.get()
co_name = co.get()
start = s.get()
end = e.get()
if db_name and co_name:
client = pymongo.MongoClient( host='localhost', port=27017 )
database = client[str( db_name )]
collection = database[str( co_name )]
r = collection.count( {"url_pickdate": {"$exists": True}} )
if r != 0:
if start and end:
num = collection.find({"url_pickdate": {"$gte": start, "$lt": end}} ).count()
t.configure(state='normal')
t.insert("insert",datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+db_name+'\n')
t.insert("insert",datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+co_name+'\n')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"开始日期:"+start+'\n')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"结束日期:"+end+'\n')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"采集到数据"+str(num)+'\n')
t.configure(state='disabled')
else:
t.configure(state='normal')
t.insert( 'insert',datetime.datetime.now().strftime( '%Y-%m-%d %H:%M:%S' ) + ':' + "请输入时间" + '\n' )
t.configure( state='disabled' )
else:
t.configure(state='normal')
t.insert( 'insert',datetime.datetime.now().strftime( '%Y-%m-%d %H:%M:%S' ) + ':' + "不存在url_pickdate字段" + '\n' )
t.configure( state='disabled' )
else:
t.configure(state='normal')
t.insert('insert',datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+':'+"请输入数据库名与集合"+'\n')
t.configure(state='disabled')
tkinter.Label(frame_r_t,text="开始查询:").grid(row=19,column=0)
tkinter.Button(frame_r_t,text="确定",bd=1,width=8,relief="ridge",command=gettimevalue).grid(row=19,column=1)
# 启动
root.mainloop()
def operat():
client = pymongo.MongoClient(host='localhost',port=27017)
database = client['rjmart']
collection = database['mart']
r = collection.count({"url_pickstatus":{"$exists":True}})
print(r)
if __name__ == "__main__":
operatesql()
# operat()
文章为本人原创,有不足之处,还望大佬指正(在数据量很大的情况下,会崩)。