GHJAmra2

1# https://www.youtube.com/watch?v=YXPyB4XeYLA 
2from tkinter import Tk,Entry,Label,messagebox, Button,Toplevel,Frame,LabelFrame
3import os
4from PIL import ImageTk,Image
5import sqlite3
6#from tkinter import messagebox
7
8# get the paths for images, os independent! (I think)
9dirname = os.path.dirname(__file__)
10imagesFolder = os.path.join(dirname, "images")
11
12root = Tk()
13root.title("whats a database?")
14root.iconbitmap(os.path.join(imagesFolder,"icon.ico"))
15# Set the original window size
16#root.geometry("400x400")
17
18# Databases
19
20# Create a database or connect to one
21conn = sqlite3.connect("Address_Book.db")
22
23# create cursor
24cur = conn.cursor()
25
26# Create table
27cur.execute("""CREATE TABLE IF NOT EXISTS addresses (
28    first_name text,
29    last_name text,
30    address text, 
31    city text,
32    state text, 
33    zipcode integer
34    )""")
35
36# Editing an existing record. 
37def edit( oid ):
38    editor = Toplevel()
39    editor.title("Editing Record: " + str(oid))
40    editor.iconbitmap(os.path.join(imagesFolder,"icon.ico"))
41
42# Create Function to Delete a Record
43def delete( oid ):
44    global frame
45
46    # Display a confirmation box
47    response = messagebox.askyesno("Confirmation", "Are you sure you want to delete record ID: " + str(oid))
48    if response == 1:
49
50        # Create a database connection or connect to one
51        conn = sqlite3.connect("Address_Book.db")
52
53        # create cursor
54        cur = conn.cursor() 
55
56        cur.execute("DELETE FROM addresses  WHERE oid=:oid", { 'oid':oid })
57
58        # Commit Changes
59        conn.commit()
60
61        # Close Connection
62        conn.close()
63        
64        frame.grid_forget()
65        query()
66
67# Create submit function
68def submit():
69    # Create a database connection or connect to one
70    conn = sqlite3.connect("Address_Book.db")
71
72    # create cursor
73    cur = conn.cursor()
74
75    # Insert into table
76    cur.execute("INSERT INTO addresses VALUES (:f_name, :l_name, :address, :city, :state, :zipcode)",
77        {
78            'f_name':f_name.get(),
79            'l_name':l_name.get(),
80            'address':address.get(),
81            'city':city.get(),
82            'state':state.get(),
83            'zipcode':zipcode.get()
84        })
85
86    # Commit Changes
87    conn.commit()
88
89    # Close Connection
90    conn.close()
91
92    # Clear the text boxes
93    f_name.delete(0,END)
94    l_name.delete(0,END)
95    address.delete(0,END)
96    city.delete(0,END)
97    state.delete(0,END)
98    zipcode.delete(0,END)
99
100# Create a query function
101
102def query():
103    global frame
104    # Create a Frame to hold all the records. 
105    frame = LabelFrame( root, text="Results", padx=5, pady=5) # Optional: text="This is my frame..."
106    frame.grid(row=8, column=0, columnspan=2)
107
108    # Create a database connection or connect to one
109    conn = sqlite3.connect("Address_Book.db")
110
111    # create cursor
112    cur = conn.cursor()
113
114    # Select from table
115    cur.execute("SELECT *, oid FROM addresses") 
116    records = cur.fetchall()
117    #print(records) # Show in the terminal
118
119    # Declare the variable to print out
120    print_records = ""
121    counter = 0
122    # Loop through results
123    for record in records:
124        global record_id
125        #print_records += str(record) + "\n" # Prints the whole record
126        record_id = record[6]
127        print_records += str(record[0]) + " " +  str(record[1]) + " " + "\t ID: " + str(record_id) + "\t"
128        queryLabel = Label(frame, text=print_records)
129        
130        # Edit Button
131        edit_btn = Button(frame, text="Edit",command=lambda: edit(record_id))
132        
133        # Adding some space here
134        spacer = Label(frame, text="\t")
135
136        # Delete Button
137        del_btn = Button(frame, text="DELETE",command=lambda: delete(record_id))
138        
139        queryLabel.grid(row=counter, column=0)
140        edit_btn.grid(row=counter, column=1)
141        spacer.grid(row=counter, column=2)
142        del_btn.grid(row=counter, column=3)
143        # Incremnet the counter to put next record on a new line
144        counter += 1
145        # Clear out print_records for the next one
146        print_records = ""
147
148
149    # Commit Changes
150    conn.commit()
151
152    # Close Connection
153    conn.close()
154
155    return
156
157# Text boxes that correspond to the database
158f_name = Entry(root, width=30)
159f_name.grid(row=0, column=1, padx=20, pady=(10,0))
160
161l_name = Entry(root, width=30)
162l_name.grid(row=1, column=1)
163
164address = Entry(root, width=30)
165address.grid(row=2, column=1)
166
167city = Entry(root, width=30)
168city.grid(row=3, column=1)
169
170state = Entry(root, width=30)
171state.grid(row=4, column=1)
172
173zipcode = Entry(root, width=30)
174zipcode.grid(row=5, column=1)
175
176# Create text box labels
177f_name_label = Label(root, text="First Name: ")
178f_name_label.grid(row=0, column=0, pady=(10,0))
179
180l_name_label = Label(root, text="Last Name: ")
181l_name_label.grid(row=1, column=0)
182
183address_label = Label(root, text="Address: ")
184address_label.grid(row=2, column=0)
185
186city_label = Label(root, text="City: ")
187city_label.grid(row=3, column=0)
188
189state_label = Label(root, text="State: ")
190state_label.grid(row=4, column=0)
191
192zipcode_label = Label(root, text="Zipcode: ")
193zipcode_label.grid(row=5, column=0)
194
195# Create submit button
196submit_btn = Button(root, text="Add Record to Database", command=submit)
197submit_btn.grid(row=6, column=0, columnspan=2, pady=10, padx=10, ipadx=100)
198
199# Create a Query Button
200query_btn = Button(root, text="Show Records", command=query)
201query_btn.grid(row=7, column=0, columnspan=2, pady=10, padx=10,ipadx=137)
202
203# Commit Changes
204conn.commit()
205
206# Close Connection
207conn.close()
208
209# and as always... the mainloop
210root.mainloop()