from tkinter import *
from tkinter import ttk
import sqlite3
#建立主要frame -> root
root = Tk()
root.title('Treeview')
#root.iconbitmap('c:/gui/xxxx.ico')
root.geometry("1000x500")
# Add Data
'''data = [["Mary", "Smith", 2, "435 West Lookout", "Chicago", "IL", "60610"],
["Tim", "Tanaka", 3, "246 Main St.", "New York", "NY", "12345"],
["Erin", "Erinton", 4, "333 Top Way.", "Los Angeles", "CA", "90210"],
["Bob", "Bobberly", 5, "876 Left St.", "Memphis", "TN", "34321"],
["Steve", "Smith", 6, "1234 Main St.", "Miami", "FL", "12321"],
["Tina", "Browne", 7, "654 Street Ave.", "Chicago", "IL", "60611"],
["Mark", "Lane", 8, "12 East St.", "Nashville", "TN", "54345"],
["John", "Smith", 9, "678 North Ave.", "St. Louis", "MO", "67821"],
["Mary", "Todd", 10, "9 Elder Way.", "Dallas", "TX", "88948"],
["John", "Lincoln", 11, "123 Elder St.", "Las Vegas", "NV", "89137"],
["Mary", "Bush", 12, "435 West Lookout", "Chicago", "IL", "60610"],
["Tim", "Reagan", 13, "246 Main St.", "New York", "NY", "12345"],
["Erin", "Smith", 14, "333 Top Way.", "Los Angeles", "CA", "90210"],
["Bob", "Field", 15, "876 Left St.", "Memphis", "TN", "34321"],
["Steve", "Target", 16, "1234 Main St.", "Miami", "FL", "12321"],
["Tina", "Walton", 17, "654 Street Ave.", "Chicago", "IL", "60611"],
["Mark", "Erendale", 18, "12 East St.", "Nashville", "TN", "54345"],
["John", "Nowerton", 19, "678 North Ave.", "St. Louis", "MO", "67821"],
["Mary", "Hornblower", 20, "9 Elder Way.", "Dallas", "TX", "88948"]]
'''
# Create a database or connect to one that exists
conn = sqlite3.connect('tree_crm.db')
# Create a cursor instance
c = conn.cursor()
# Create Table
c.execute("""CREATE TABLE if not exists customers (
first_name text,
last_name text,
id integer,
address text,
city text,
state text,
zipcode text)
""")
# Add dummy data to table
'''
for record in data:
c.execute("INSERT INTO customers VALUES (:first_name, :last_name, :id, :address, :city, :state, :zipcode)",
{
'first_name': record[0],
'last_name': record[1],
'id': record[2],
'address': record[3],
'city': record[4],
'state': record[5],
'zipcode': record[6]
}
)
'''
# Commit changes
conn.commit()
# Close connection
conn.close()
def query_database():
# Create a database or connect to one that exists
conn = sqlite3.connect('tree_crm.db')
# Create a cursor instance
c = conn.cursor()
c.execute("SELECT rowid, * FROM customers")
records = c.fetchall() #database内每行数据捕获 "一行数据有7个，对应7个columns内每一个"
# Add our data to the screen
global count
count = 0
#将每行db内数据用insert来显示到treeview, 如果双数行用唔同既theme颜色显示, 请自己留意以下tag
for record in records:
if count % 2 == 0:
my_tree.insert(parent='', index='end', iid=count, text='', values=(record[1], record[2], record[0], record[4], record[5], record[6], record[7]), tags=('evenrow',))
else:
my_tree.insert(parent='', index='end', iid=count, text='', values=(record[1], record[2], record[0], record[4], record[5], record[6], record[7]), tags=('oddrow',))
count += 1
conn.commit()
conn.close()
# 添加 Style
style = ttk.Style()
# 选用default既 Theme
style.theme_use('default')
# Configure Treeview既颜色
style.configure("Treeview",
background="#D3D3D3",
foreground="black",
rowheight=25,
fieldbackground="#D3D3D3")
# 改变颜色
style.map('Treeview',
background=[('selected', "#347083")])
# 建立一个框框比 Treeview 放读入既database
tree_frame = Frame(root)
tree_frame.pack(pady=10)
# 整个靠右手边(Y)上下滑动既Scrollbar比Treeview
tree_scroll = Scrollbar(tree_frame)
tree_scroll.pack(side=RIGHT, fill=Y)
# 将建立好既Treeview 同scrollbar combine埋一齐
my_tree = ttk.Treeview(tree_frame, yscrollcommand=tree_scroll.set, selectmode="extended")
my_tree.pack()
# Configure the Scrollbar y view系上下滑动
tree_scroll.config(command=my_tree.yview)
# 定义你要显示既所有 Columns
my_tree['columns'] = ("First Name", "Last Name", "ID", "Address", "City", "State", "Zipcode")
# Format Columns 宽度， 下面第一个column head不显示，width=0, 唔比左右拖拉 stretch=NO
my_tree.column("#0", width=0, stretch=NO)
my_tree.column("First Name", anchor=W, width=140)
my_tree.column("Last Name", anchor=W, width=140)
my_tree.column("ID", anchor=CENTER, width=100)
my_tree.column("Address", anchor=CENTER, width=140)
my_tree.column("City", anchor=CENTER, width=140)
my_tree.column("State", anchor=CENTER, width=140)
my_tree.column("Zipcode", anchor=CENTER, width=140)
# 建立column名
my_tree.heading("#0", text="", anchor=W)
my_tree.heading("First Name", text="First Name", anchor=W)
my_tree.heading("Last Name", text="Last Name", anchor=W)
my_tree.heading("ID", text="ID", anchor=CENTER)
my_tree.heading("Address", text="Address", anchor=CENTER)
my_tree.heading("City", text="City", anchor=CENTER)
my_tree.heading("State", text="State", anchor=CENTER)
my_tree.heading("Zipcode", text="Zipcode", anchor=CENTER)
# 回应返上面Row Tags 个theme，双数行以蓝色显示，反之白色
my_tree.tag_configure('oddrow', background="white")
my_tree.tag_configure('evenrow', background="lightblue")
# Add Record Entry Boxes 加入输入文字既box
data_frame = LabelFrame(root, text="Record") #先整label既框架
data_frame.pack(fill="x", expand="yes", padx=20) #pack显示label框架
fn_label = Label(data_frame, text="First Name") #先整firstname 既label
fn_label.grid(row=0, column=0, padx=10, pady=10)
fn_entry = Entry(data_frame) #再整firstname 既输入box
fn_entry.grid(row=0, column=1, padx=10, pady=10) #grid显示firstname 既输入box
ln_label = Label(data_frame, text="Last Name")
ln_label.grid(row=0, column=2, padx=10, pady=10)
ln_entry = Entry(data_frame)
ln_entry.grid(row=0, column=3, padx=10, pady=10)
id_label = Label(data_frame, text="ID")
id_label.grid(row=0, column=4, padx=10, pady=10)
id_entry = Entry(data_frame)
id_entry.grid(row=0, column=5, padx=10, pady=10)
address_label = Label(data_frame, text="Address")
address_label.grid(row=1, column=0, padx=10, pady=10)
address_entry = Entry(data_frame)
address_entry.grid(row=1, column=1, padx=10, pady=10)
city_label = Label(data_frame, text="City")
city_label.grid(row=1, column=2, padx=10, pady=10)
city_entry = Entry(data_frame)
city_entry.grid(row=1, column=3, padx=10, pady=10)
state_label = Label(data_frame, text="State")
state_label.grid(row=1, column=4, padx=10, pady=10)
state_entry = Entry(data_frame)
state_entry.grid(row=1, column=5, padx=10, pady=10)
zipcode_label = Label(data_frame, text="Zipcode")
zipcode_label.grid(row=1, column=6, padx=10, pady=10)
zipcode_entry = Entry(data_frame)
zipcode_entry.grid(row=1, column=7, padx=10, pady=10)
# 添加资料到资料库
def add_record():
conn = sqlite3.connect('tree_crm.db')
c = conn.cursor()
c.execute("INSERT INTO customers VALUES (:first_name, :last_name, :id, :address, :city, :state, :zipcode)",
{
'first_name': fn_entry.get(),
'last_name': ln_entry.get(),
'address': address_entry.get(),
'city': city_entry.get(),
'state': state_entry.get(),
'zipcode': zipcode_entry.get(),
'id': id_entry.get(),
})
conn.commit()
conn.close()
#输入完数据后清空文字输入框
fn_entry.delete(0, END)
ln_entry.delete(0, END)
address_entry.delete(0, END)
city_entry.delete(0, END)
state_entry.delete(0, END)
zipcode_entry.delete(0, END)
id_entry.delete(0, END)
for record in my_tree.get_children():
my_tree.delete(record)
#refresh一次显示Treeview
query_database()
# Move Row Up
def up():
rows = my_tree.selection()
for row in rows:
my_tree.move(row, my_tree.parent(row), my_tree.index(row)-1)
# Move Rown Down
def down():
rows = my_tree.selection()
for row in reversed(rows):
my_tree.move(row, my_tree.parent(row), my_tree.index(row)+1)
# Remove one record
def remove_one():
x = my_tree.selection()[0]
my_tree.delete(x)
# Remove Many records
def remove_many():
x = my_tree.selection()
for record in x:
my_tree.delete(record)
# Remove all records
def remove_all():
for record in my_tree.get_children():
my_tree.delete(record)
# Clear entry boxes
def clear_entries():
# Clear entry boxes
fn_entry.delete(0, END)
ln_entry.delete(0, END)
id_entry.delete(0, END)
address_entry.delete(0, END)
city_entry.delete(0, END)
state_entry.delete(0, END)
zipcode_entry.delete(0, END)
# Select Record
def select_record(e):
# Clear entry boxes
fn_entry.delete(0, END)
ln_entry.delete(0, END)
id_entry.delete(0, END)
address_entry.delete(0, END)
city_entry.delete(0, END)
state_entry.delete(0, END)
zipcode_entry.delete(0, END)
# Grab record Number
selected = my_tree.focus()
# Grab record values
values = my_tree.item(selected, 'values')
# outpus to entry boxes
fn_entry.insert(0, values[0])
ln_entry.insert(0, values[1])
id_entry.insert(0, values[2])
address_entry.insert(0, values[3])
city_entry.insert(0, values[4])
state_entry.insert(0, values[5])
zipcode_entry.insert(0, values[6])
# Update record
def update_record():
# Grab the record number 滑鼠点击边行就读取边行既输入框内既数据
selected = my_tree.focus()
# Update record，利用entry.get()捕获输入框内数据
my_tree.item(selected, text="", values=(fn_entry.get(), ln_entry.get(), id_entry.get(), address_entry.get(), city_entry.get(), state_entry.get(), zipcode_entry.get(),))
# Update the database
# Create a database or connect to one that exists
conn = sqlite3.connect('tree_crm.db')
# Create a cursor instance
c = conn.cursor()
#oid 系automatic咁加id次序落去，而无需人手输入id number e.g 1,2,3......
c.execute("""UPDATE customers SET
first_name = :first,
last_name = :last,
address = :address,
city = :city,
state = :state,
zipcode = :zipcode
#将输入框内数据update到sqlite
WHERE oid = :oid""",
{
'first': fn_entry.get(),
'last': ln_entry.get(),
'address': address_entry.get(),
'city': city_entry.get(),
'state': state_entry.get(),
'zipcode': zipcode_entry.get(),
'oid': id_entry.get(),
})
conn.commit()
conn.close()
# 清空各个用来输入既entry boxes
fn_entry.delete(0, END)
ln_entry.delete(0, END)
id_entry.delete(0, END)
address_entry.delete(0, END)
city_entry.delete(0, END)
state_entry.delete(0, END)
zipcode_entry.delete(0, END)
# 添加buttons同对应既command
button_frame = LabelFrame(root, text="Commands")
button_frame.pack(fill="x", expand="yes", padx=20)
update_button = Button(button_frame, text="Update Record", command=update_record)
update_button.grid(row=0, column=0, padx=10, pady=10)
add_button = Button(button_frame, text="Add Record", command=add_record)
add_button.grid(row=0, column=1, padx=10, pady=10)
remove_all_button = Button(button_frame, text="Remove All Records", command=remove_all)
remove_all_button.grid(row=0, column=2, padx=10, pady=10)
remove_one_button = Button(button_frame, text="Remove One Selected", command=remove_one)
remove_one_button.grid(row=0, column=3, padx=10, pady=10)
remove_many_button = Button(button_frame, text="Remove Many Selected", command=remove_many)
remove_many_button.grid(row=0, column=4, padx=10, pady=10)
move_up_button = Button(button_frame, text="Move Up", command=up)
move_up_button.grid(row=0, column=5, padx=10, pady=10)
move_down_button = Button(button_frame, text="Move Down", command=down)
move_down_button.grid(row=0, column=6, padx=10, pady=10)
select_record_button = Button(button_frame, text="Clear Entry Boxes", command=clear_entries)
select_record_button.grid(row=0, column=7, padx=10, pady=10)
# Bind the treeview
my_tree.bind("<ButtonRelease-1>", select_record) #在顯示frame内，滑鼠放開執行 select_record
# Run to pull data from database on start
query_database()
root.mainloop()