In this video I’ll show you how to delete everything from our Database Table with the DROP TABLE command.
The fastest and easiest way to delete all the records from our database table is to use the Drop Table Command.
Watch out though, this will permanently delete everything in the table, so we’ll make a backup first!
After we delete all the records and drop the table, we’ll need to recreate the table structure so that we can continue to add new customers in the future.
We’ll also create a popup box that asks the user if they’re sure they want to delete everything from the table.
Python Code: treebase.py
(Github Code)
from tkinter import * from tkinter import ttk from tkinter import messagebox import sqlite3 root = Tk() root.title('Codemy.com - TreeBase') root.iconbitmap('c:/gui/codemy.ico') root.geometry("1000x500") # Add Fake Data ''' data = [ ["John", "Elder", 1, "123 Elder St.", "Las Vegas", "NV", "89137"], ["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"] ] ''' # Do some database stuff # 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 our 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() # Add our data to the screen global count count = 0 #for record in records: # print(record) 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',)) # increment counter count += 1 # Commit changes conn.commit() # Close our connection conn.close() # Add Some Style style = ttk.Style() # Pick A Theme style.theme_use('default') # Configure the Treeview Colors style.configure("Treeview", background="#D3D3D3", foreground="black", rowheight=25, fieldbackground="#D3D3D3") # Change Selected Color style.map('Treeview', background=[('selected', "#347083")]) # Create a Treeview Frame tree_frame = Frame(root) tree_frame.pack(pady=10) # Create a Treeview Scrollbar tree_scroll = Scrollbar(tree_frame) tree_scroll.pack(side=RIGHT, fill=Y) # Create The Treeview my_tree = ttk.Treeview(tree_frame, yscrollcommand=tree_scroll.set, selectmode="extended") my_tree.pack() # Configure the Scrollbar tree_scroll.config(command=my_tree.yview) # Define Our Columns my_tree['columns'] = ("First Name", "Last Name", "ID", "Address", "City", "State", "Zipcode") # Format Our Columns 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) # Create Headings 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) # Create Striped Row Tags my_tree.tag_configure('oddrow', background="white") my_tree.tag_configure('evenrow', background="lightblue") # Add Record Entry Boxes data_frame = LabelFrame(root, text="Record") data_frame.pack(fill="x", expand="yes", padx=20) fn_label = Label(data_frame, text="First Name") fn_label.grid(row=0, column=0, padx=10, pady=10) fn_entry = Entry(data_frame) fn_entry.grid(row=0, column=1, padx=10, pady=10) 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) # 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) # Create a database or connect to one that exists conn = sqlite3.connect('tree_crm.db') # Create a cursor instance c = conn.cursor() # Delete From Database c.execute("DELETE from customers WHERE oid=" + id_entry.get()) # Commit changes conn.commit() # Close our connection conn.close() # Clear The Entry Boxes clear_entries() # Add a little message box for fun messagebox.showinfo("Deleted!", "Your Record Has Been Deleted!") # Remove Many records def remove_many(): x = my_tree.selection() for record in x: my_tree.delete(record) # Remove all records def remove_all(): # Add a little message box for fun response = messagebox.askyesno("WOAH!!!!", "This Will Delete EVERYTHING From The Table\nAre You Sure?!") #Add logic for message box if response == 1: # Clear the Treeview for record in my_tree.get_children(): my_tree.delete(record) # Create a database or connect to one that exists conn = sqlite3.connect('tree_crm.db') # Create a cursor instance c = conn.cursor() # Delete Everything From The Table c.execute("DROP TABLE customers") # Commit changes conn.commit() # Close our connection conn.close() # Clear entry boxes if filled clear_entries() # Recreate The Table create_table_again() # 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 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() c.execute("""UPDATE customers SET first_name = :first, last_name = :last, address = :address, city = :city, state = :state, zipcode = :zipcode 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(), }) # Commit changes conn.commit() # Close our connection conn.close() # 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) # add new record to database def add_record(): # 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() # Add New Record c.execute("INSERT INTO customers VALUES (:first, :last, :id, :address, :city, :state, :zipcode)", { 'first': fn_entry.get(), 'last': ln_entry.get(), 'id': id_entry.get(), 'address': address_entry.get(), 'city': city_entry.get(), 'state': state_entry.get(), 'zipcode': zipcode_entry.get(), }) # Commit changes conn.commit() # Close our connection conn.close() # 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) # Clear The Treeview Table my_tree.delete(*my_tree.get_children()) # Run to pull data from database on start query_database() def create_table_again(): # 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) """) # Commit changes conn.commit() # Close our connection conn.close() # Add Buttons 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("", select_record) # Run to pull data from database on start query_database() root.mainloop()
Add comment