zono note

 

zono note

日々の学び、気付きを大切に綴りたい

連載8:pythonで利用者情報入力を作る

最終掲載です!
今までの連載を合わせて、最終型を作ります
長くなりますがよろしくお願いします

# -*- coding: utf-8 -*-
#	dbname			= 	kihon.db
#	table			= 	uesr
#	id				= 	entry1
#	na1	(姓)  		= 	entry2
#	na2	(名)		=	 entry3
#	na3	(セイ)		= 	entry4
#	ne4	(メイ)		= 	entry5
#	ci (市町村)		= 	box1			
#	se1	(西暦年)		= 	box2			
#	se2 (月)			=	 box3		
#	se3	(日)			= 	box4
#	si	(支給量)		= 	entry8
#	kou	(工賃単価)  	=	 entry9
#	schema	[id:int],[姓],[名],[セイ],[メイ],[市町村],[契約日],[支給量:int],[工賃:int]


import tkinter as tk
import tkinter.ttk as ttk
import sqlite3
import tkinter.messagebox as msg

# 空のデータベース、テーブルを作成し接続
dbname = "kihon.db"
c = sqlite3.connect(dbname)
def select_bottun():
	entry1.delete(0,tk.END)
	entry2.delete(0,tk.END)
	entry3.delete(0,tk.END)
	entry4.delete(0,tk.END)
	entry5.delete(0,tk.END)
	box1.delete(0,tk.END)
	box2.delete(0,tk.END)
	box3.delete(0,tk.END)
	box4.delete(0,tk.END)
	entry8.delete(0,tk.END)
	entry9.delete(0,tk.END)
	entry14.delete(0,tk.END)
	entry18.delete(0,tk.END)
	entry19.delete(0,tk.END)
	entry20.delete(0,tk.END)
	create_sql
def quit_button():
	root.destroy()
def create_sql():
	c = sqlite3.connect("aozora.db")
	na1 = entry2.get() 
	na2 = entry3.get()
	na3 = entry4.get()
	na4 = entry5.get()
	ci = box1.get()
	se1 = box2.get()
	se2 = box3.get()
	se3 = box4.get()
	si = entry8.get()
	kou = entry9.get()
	se4 = (se1+se2+se3)
	print (na1+na2+na3+na4+ci+se4+si+kou)
	
	try:
		user = """
		CREARE TABLE user
		(
		na1 TEXT NOT NULL, 
		na2 TEXT NOT NULL,
		na3 TEXT NOT NULL,
		na4 TEXT NOT NULL,
		ci TEXT NOT NULL,
		se1 TEXT NOT NULL,
		se2 TEXT NOT NULL,
		se3 TEXT NOT NULL,
		si INT NOT NULL,
		kou INT NOT NULL
		)
		"""
		c.execute(user)
		c.execute("COMMIT")
	except:
		pass

	try:
		c.execute("""
		INSERT INTO 
		user(姓,名,セイ,メイ,市町村,契約日,支給量,工賃単価)
		VALUES('{}','{}','{}','{}','{}','{}',{},{});""".format(na1,na2,na3,na4,ci,se4,si,kou))
		c.execute("COMMIT;")
		text=("一件登録されました!")
		tk.messagebox.showinfo("info",text)
	except:
		text=("エラーにより登録できませんでした")
		tk.messagebox.showinfo("info",text)
#------------------------------------------------------------------------------------------------------------
#	""" リストボックスの選択を読み取る関数,その結果を入力ウィジェットに入れる"""
#------------------------------------------------------------------------------------------------------------
def get_list(nanikanyuuryoku):
	cur = c.cursor()
	index = mylist.curselection()[0]		
	user_id2 = (user_id[index], )
	cur.execute('select * from user where id = ?', user_id2)
	user_id3 = cur.fetchone()
	entry1.delete(0,50)	
	entry1.insert(0, user_id3[0])	
	entry2.delete(0,50)
	entry2.insert(0, user_id3[1])
	entry3.delete(0,50)
	entry3.insert(0, user_id3[2])
	entry4.delete(0,50)
	entry4.insert(0, user_id3[3])
	entry5.delete(0,50)
	entry5.insert(0, user_id3[4])
	entry14.delete(0,50)
	entry14.insert(0, user_id3[5])
	entry18.delete(0,50)
	entry18.insert(0, user_id3[6][0:4])
	entry19.delete(0,50)
	entry19.insert(0, user_id3[6][4:6])
	entry20.delete(0,50)
	entry20.insert(0, user_id3[6][6:8])
	entry8.delete(0,50)
	entry8.insert(0, user_id3[7])
	entry9.delete(0,50)
	entry9.insert(0, user_id3[8])

root = tk.Tk()
root.resizable(0,0)
root.title("あおぞら業務入力支援")
root.geometry("640x640")

status = tk.Label(root, text="from : zono屋・・・ ",borderwidth=2,relief="ridge")
status.pack(side=tk.BOTTOM, fill=tk.X)
#	1th < scrollbar > cd 
frame1 = tk.LabelFrame(root,bd=1,relief="sunken",text=" name ",height=600,foreground="purple")
frame1.pack(side="left",anchor="nw",fill="x",padx=8,pady=2)

scrollbar = tk.Scrollbar(frame1,bd=5,relief="sunken")
scrollbar.pack(side="right",fill="y")
#	2th " menu "  < 利用者ID > 
frame2 = tk.LabelFrame(root,bd=1,relief="sunken",text=" manu ",width=600,height=100,foreground="purple")
frame2.pack(side="top",anchor="n",fill="x",ipady=5,padx=2)
label1 = tk.Label(frame2,text="利用者 ID",width=10,height=2,bd=1,relief="sunken",bg="ghost white")
label1.grid(row=1,column=1,padx=2,pady=2)
entry1 = tk.Entry(frame2,text="",width=15,bg="light grey")
entry1.grid(row=1,column=2,padx=2,ipady=5)
#	3th " start date of use "  < 西暦年 > <  月  > <  日  > 
frame3 = tk.LabelFrame(root,bd=1,relief="sunken",text=" start date of use ",width=600,height=100,foreground="purple")
frame3.pack(side="top",fill="x",pady=5,padx=2)
label9 = tk.Label(frame3,text="確認表示欄",width=10,height=2,bd=1,relief="sunken",bg="ghost white")
label9.grid(row=2,column=1,padx=2,pady=5)
entry18= tk.Entry(frame3,text="",width=10,bg="light grey")
entry18.grid(row=2,column=2,padx=2,ipady=5)
label18= tk.Label(frame3,text="年",width=7,height=2,bd=1,relief="sunken",bg="ghost white")
label18.grid(row=2,column=3,padx=2,pady=5)
entry19= tk.Entry(frame3,text="",width=10,bg="light grey")
entry19.grid(row=2,column=4,padx=2,ipady=5)
label19= tk.Label(frame3,text="月",width=7,height=2,bd=1,relief="sunken",bg="ghost white")
label19.grid(row=2,column=5,padx=2,pady=5)
entry20= tk.Entry(frame3,text="",width=10,bg="light grey")
entry20.grid(row=2,column=6,padx=2,ipady=5)
label20= tk.Label(frame3,text="日",width=7,height=2,bd=1,relief="sunken",bg="ghost white")
label20.grid(row=2,column=7,padx=2,pady=5)
label10 = tk.Label(frame3,text="利用開始日",width=7,height=2,bd=1,relief="sunken",bg="ghost white")
label10.grid(row=1,column=1,ipadx=10,pady=5)

val = tk.StringVar()
box2 = ttk.Combobox(frame3, values = ("  ","2015","2016","2017","2018","2019","2020","2021"), textvariable=val, state='readonly',width=7,height=2)
box2.current(0) #初期値を'(index=0)'に設定
box2.grid(row=1,column=2,padx=2,ipady=5)
label12= tk.Label(frame3,text="年",width=7,height=2,bd=1,relief="sunken",bg="ghost white")
label12.grid(row=1,column=3,padx=2,pady=5)
val = tk.StringVar()
box3 = ttk.Combobox(frame3, values = ("  ","01","02","03","04","05","06","07","08","09","10","11","12"), textvariable=val, state='readonly',width=7,height=2)
box3.current(0) #初期値を'(index=0)'に設定
box3.grid(row=1,column=4,padx=2,ipady=5)
label14= tk.Label(frame3,text="月",width=7,height=2,bd=1,relief="sunken",bg="ghost white")
label14.grid(row=1,column=5,padx=2,pady=5)

val = tk.StringVar()
box4 = ttk.Combobox(frame3, values = ("  ","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31"), textvariable=val, state='readonly',width=7,height=2)
box4.current(0)
box4.grid(row=1,column=6,padx=2,ipady=5)
label16= tk.Label(frame3,text="日",width=7,height=2,bd=1,relief="sunken",bg="ghost white")
label16.grid(row=1,column=7,padx=2,pady=5)
#	4th "name Confirm input"  <  姓  >  <  名  > < セ イ > < メ イ > 
frame4 = tk.LabelFrame(root,bd=1,relief="sunken",text=" name Confirm input ",width=600,height=200,foreground="purple")
frame4.pack(side="top",fill="x",pady=5,padx=2)
label3 = tk.Label(frame4,text=" 姓 (*)",width=10,height=2,bd=1,relief="sunken",bg="ghost white")
label3.grid(row=2,column=1,padx=2,pady=5)
entry2 = tk.Entry(frame4,width=15,text="")
entry2.grid(row=2,column=2,padx=2,ipady=5)
label4 = tk.Label(frame4,text=" 名 (*)",width=10,height=2,bd=1,relief="sunken",bg="ghost white")
label4.grid(row=2,column=3,padx=2,pady=5)
entry3 = tk.Entry(frame4,text="",width=15)
entry3.grid(row=2,column=4,padx=2,ipady=5)
label5 = tk.Label(frame4,text=" セイ (*)",width=10,height=2,bd=1,relief="sunken",bg="ghost white")
label5.grid(row=3,column=1,padx=2,pady=5)
entry4 = tk.Entry(frame4,text="",width=15)
entry4.grid(row=3,column=2,padx=2,ipady=5)
label6 = tk.Label(frame4,text=" メイ (*)",width=10,height=2,bd=1,relief="sunken",bg="ghost white")
label6.grid(row=3,column=3,padx=2,pady=5)
entry5 = tk.Entry(frame4,text="",width=15)
entry5.grid(row=3,column=4,padx=2,ipady=5)
#	5th " other "  < 市町村名 > < 支給量 > < 工賃単価 >
frame5 = tk.LabelFrame(root,bd=1,relief="sunken",text=" other ",width=600,height=100,foreground="purple")
frame5.pack(side="top",fill="x",pady=5,padx=2)
label17 = tk.Label(frame5,text=" 市町村名 ",width=10,height=2,bd=1,relief="sunken",bg="ghost white")
label17.grid(row=1,column=1,padx=2,pady=5)
val = tk.StringVar()
box1 = ttk.Combobox(frame5,values = ("  ","鹿児島市","熊本市"), textvariable=val, state='readonly',width=12,height=2)
box1.current(0)
box1.grid(row=1,column=2,padx=5,ipady=5)
label17 = tk.Label(frame5,text="確認表示欄 ",width=10,height=2,bd=1,relief="sunken",bg="ghost white")
label17.grid(row=1,column=3,padx=2,pady=5)
entry14 = tk.Entry(frame5,text="",width=15,bg="light grey")
entry14.grid(row=1,column=4,padx=2,ipady=5)
label17 = tk.Label(frame5,text=" 支給量 ",width=10,height=2,bd=1,relief="sunken",bg="ghost white")
label17.grid(row=2,column=1,padx=2,pady=5)
entry8 = tk.Entry(frame5,text="",width=15)
entry8.grid(row=2,column=2,padx=2,ipady=5)
label17 = tk.Label(frame5,text=" 工賃単価 ",width=10,height=2,bd=1,relief="sunken",bg="ghost white")
label17.grid(row=3,column=1,padx=2,pady=5)
entry9 = tk.Entry(frame5,text="",width=15)
entry9.grid(row=3,column=2,padx=2,ipady=5)
#	7th " edit " 	【追 加】 	【更 新】
frame7 = tk.LabelFrame(root,bd=1,relief="sunken",text=" edit ",width=600,height=100,foreground="purple")
frame7.pack(side="top",fill="x",pady=5,padx=5)
button2 = tk.Button(frame7,text="追 加",width=10,height=2,bg="lightgray",command=select_bottun)	
button2.pack(side="left",padx=2,pady=5)
button3 = tk.Button(frame7,text="更 新",width=10,height=2,bg="lightgray",command=create_sql)	
button3.pack(side="left",padx=2,pady=5)

mylist = tk.Listbox(frame1,yscrollcommand = scrollbar.set,height=600)
mylist.insert(0)										# リストボックス内の項目をクリックする #
mylist.pack(side="left",fill="both")
scrollbar.config(command = mylist.yview)
#	8th " end " 	【終 了】 	
frame8 = tk.LabelFrame(root,bd=1,relief="sunken",text=" end ",width=600,height=100,foreground="purple")
frame8.pack(side="top",fill="x",pady=5,padx=5)
label18 = tk.Label(frame8,text=" 項目 (*) は 必須 !",width=20,height=2,bd=1)
label18.pack(side="left",padx=5,pady=5)
button4 = tk.Button(frame8,text="終 了",width=10,height=2,bg="lightgray",command=quit_button)	
button4.pack(side="right",padx=5,pady=5)
sql = """
	SELECT *
	FROM user order by セイ
	"""

user_id = []
for r in c.execute(sql):
	mylist.insert(tk.END,r[1] + "  " + r[2])
	user_id.append(r[0])

mylist.bind("<ButtonRelease-1>",get_list)
root.mainloop()

完成図

tkinterで作成したGUIです