LdxPJpug

1from tkinter import *
2import sqlite3
3
4root = Tk()
5root.title("Perpustakaan Berbasis Databases")
6root.geometry("400x600")
7
8# Databases
9
10# create a database or connect to one
11conn = sqlite3.connect('menu.db')
12
13# Create cursor
14c = conn.cursor()
15
16# Create table
17
18c.execute("""CREATE TABLE IF NOT EXISTS addresses (
19        first_name TEXT,
20        last_name TEXT,
21        address TEXT,
22        city TEXT,
23        state TEXT,
24        zipcode INTEGER
25        )""")
26
27
28# Create Edit Function to Update a record
29def update():
30    # create a database or connect to one
31    conn = sqlite3.connect('menu.db')
32    # Create cursor
33    c = conn.cursor()
34
35    # Commit changes
36    conn.commit()
37    # Close connection
38    conn.close()
39
40def edit():
41    editor = Tk()
42    editor.title("Update A Record")
43    editor.geometry('400x400')
44
45    # create a database or connect to one
46    conn = sqlite3.connect('menu.db')
47    # Create cursor
48    c = conn.cursor()
49
50    record_id = delete_box.get()
51    # Query the database
52    c.execute("SELECT * FROM addresses WHERE oid = " + record_id)
53    records = c.fetchall()
54
55    # Create Text Boxes
56    f_name_editor = Entry(editor, width=30)
57    f_name_editor.grid(row=0, column=1, padx=20, pady=(10, 0))
58    l_name_editor = Entry(editor, width=30)
59    l_name_editor.grid(row=1, column=1)
60    address_editor = Entry(editor, width=30)
61    address_editor.grid(row=2, column=1)
62    city_editor = Entry(editor, width=30)
63    city_editor.grid(row=3, column=1)
64    state_editor = Entry(editor, width=30)
65    state_editor.grid(row=4, column=1)
66    zipcode_editor = Entry(editor, width=30)
67    zipcode_editor.grid(row=5, column=1)
68
69    # Create Text Box Labels
70    f_name_lbl = Label(editor, text="First Name")
71    f_name_lbl.grid(row=0, column=0, pady=(10,0))
72    l_name_lbl = Label(editor, text="Last Name")
73    l_name_lbl.grid(row=1, column=0)
74    address_lbl = Label(editor, text="Address")
75    address_lbl.grid(row=2, column=0)
76    city_lbl = Label(editor, text="City")
77    city_lbl.grid(row=3, column=0)
78    state_lbl = Label(editor, text="State")
79    state_lbl.grid(row=4, column=0)
80    zipcode_lbl = Label(editor, text="Zipcode")
81    zipcode_lbl.grid(row=5, column=0)
82
83    # Loop thru result
84    for record in records:
85        f_name_editor.insert(0, record[0])
86        l_name_editor.insert(0, record[1])
87        address_editor.insert(0, record[2])
88        city_editor.insert(0, record[3])
89        state_editor.insert(0, record[4])
90        zipcode_editor.insert(0, record[5])
91
92    # Create an Save Button To Save edited record
93    edit_btn = Button(editor, text="Save Records", command=update)
94    edit_btn.grid(row=6, column=0, columnspan=2, pady=10, padx=10, ipadx=143)
95
96
97# Create Function to Delete A Record
98def delete():
99    # create a database or connect to one
100    conn = sqlite3.connect("menu.db")
101    # Create cursor
102    c = conn.cursor()
103
104    # Delete a record
105    c.execute("DELETE FROM addresses WHERE oid = " + delete_box.get())
106
107    
108
109    # Commit changes
110    conn.commit()
111    # Close connection
112    conn.close()
113
114
115# Create submit function for database
116def submit():
117    # create a database or connect to one
118    conn = sqlite3.connect("menu.db")
119    # Create cursor
120    c = conn.cursor()
121    # Insert Into Table
122    c.execute("INSERT INTO addresses VALUES (:f_name, :l_name, :address, :city, :state, :zipcode)",
123            {
124                'f_name': f_name.get(),
125                'l_name': l_name.get(),
126                'address': address.get(),
127                'city': city.get(),
128                'state': state.get(),
129                'zipcode': zipcode.get()
130            })
131    # Commit changes
132    conn.commit()
133    # Close connection
134    conn.close()
135
136    # Clear The Text Boxes
137    f_name.delete(0, END)
138    l_name.delete(0, END)
139    address.delete(0, END)
140    city.delete(0, END)
141    state.delete(0, END)
142    zipcode.delete(0, END)
143
144# Create Query function
145def query():
146    # create a database or connect to one
147    conn = sqlite3.connect("menu.db")
148    # Create cursor
149    c = conn.cursor()
150
151    # Query the database
152    c.execute("SELECT *, oid FROM addresses")
153    records = c.fetchall()
154    #print(records)
155
156    # loop thur Result
157    print_records = ''
158    for record in records:
159        print_records += str(record[0]) + " " + str(record[1]) + " " + "\t" +str(record[6]) + "\n"
160
161    query_lbl= Label(root, text=print_records)
162    query_lbl.grid(row=12, column=0, columnspan=2)
163
164    # Commit changes
165    conn.commit()
166    # Close connection
167    conn.close()
168
169# Create Text Boxes
170f_name = Entry(root, width=30)
171f_name.grid(row=0, column=1, padx=20, pady=(10,0))
172l_name = Entry(root, width=30)
173l_name.grid(row=1, column=1)
174address = Entry(root, width=30)
175address.grid(row=2, column=1)
176city = Entry(root, width=30)
177city.grid(row=3, column=1)
178state = Entry(root, width=30)
179state.grid(row=4, column=1)
180zipcode = Entry(root, width=30)
181zipcode.grid(row=5, column=1)
182delete_box = Entry(root, width=30)
183delete_box.grid(row=9, column=1, pady=5)
184
185# Create Text Box Labels
186f_name_lbl = Label(root, text="First Name")
187f_name_lbl.grid(row=0, column=0, pady=(10,0))
188l_name_lbl = Label(root, text="Last Name")
189l_name_lbl.grid(row=1, column=0)
190address_lbl = Label(root, text="Address")
191address_lbl.grid(row=2, column=0)
192city_lbl = Label(root, text="City")
193city_lbl.grid(row=3, column=0)
194state_lbl = Label(root, text="State")
195state_lbl.grid(row=4, column=0)
196zipcode_lbl = Label(root, text="Zipcode")
197zipcode_lbl.grid(row=5, column=0)
198delete_box_lbl = Label(root, text="Select ID")
199delete_box_lbl.grid(row=9, column=0, pady=5)
200
201# Create submit Button
202submit_btn = Button(root, text="Add Record To Database", command=submit)
203submit_btn.grid(row=6, column=0, columnspan=2, pady=10, padx=10, ipadx=100)
204
205# Create a Query Button
206query_btn = Button(root, text="Show Records", command=query)
207query_btn.grid(row=7, column=0, columnspan=2, pady=10, padx=10, ipadx=137)
208
209# Create a Delete Button
210delete_btn = Button(root, text="Delete Records", command=delete)
211delete_btn.grid(row=10, column=0, columnspan=2, pady=10, padx=10, ipadx=136)
212
213# Create an Update Button
214edit_btn = Button(root, text="Edit Records", command=edit)
215edit_btn.grid(row=11, column=0, columnspan=2, pady=10, padx=10, ipadx=143)
216
217
218# Commit changes
219conn.commit()
220
221# Close connection
222conn.close()
223
224# Main loop
225root.mainloop()