database.py (7719B)
1 #### database_tool.py 2 3 # Import libraries: 4 from tkinter import * 5 from tkinter.scrolledtext import * 6 import sqlite3 7 import os # needed to obtain OS specific methods for navigating file structures 8 9 # Global Variables 10 current_database = '' 11 databases = [] 12 textbox_colour = 'linen' 13 14 #### Utility Functions 15 def pretty_tables(info, body): 16 '''Called to handle SQL queries beginning with SELECT. 17 It then formats the output from sqlite3 into easy tables''' 18 column_widths = [] 19 headings = [] 20 21 # work hard to get headings and put them into a list 22 # set column widths to length of heading strings 23 for row in info: 24 headings.append(row[0]) 25 column_widths.append(len(row[0])) 26 27 # update column widths from body text 28 for row in body: 29 counter = 0 30 for item in row: 31 length = len(str(item)) 32 if length > column_widths[counter]: 33 column_widths[counter] = length 34 counter = counter+1 35 36 # format results with a lot of string manipulation 37 top_row = '+' 38 heading_text = '| ' 39 body_text = '| ' 40 for width in column_widths: 41 top_row = top_row + '-'*(width+3) 42 top_row = top_row[:-1] + '+\n' 43 44 counter = 0 45 for item in headings: 46 padding = ' '*(column_widths[counter] - len(str(item))) 47 heading_text = heading_text + item + padding + ' | ' 48 counter = counter+1 49 heading_text = heading_text + '\n' 50 51 for row in body: 52 counter = 0 53 for item in row: 54 padding = ' '*(column_widths[counter] - len(str(item))) 55 body_text = body_text + str(item) + padding + ' | ' 56 counter = counter+1 57 body_text = body_text + '\n| ' 58 body_text = body_text[:-2] 59 60 text = top_row + heading_text + top_row + body_text + top_row 61 return text 62 63 def update_databases(): 64 '''Looks through the folder where this script is 65 found looking for database files''' 66 number_of_dbs = 0 67 files = os.listdir() 68 for file in files: 69 # select files where the last three characters are .db 70 if file[-3:] == '.db': 71 databases.append(file) 72 73 #### Button functions 74 def build_database(): 75 '''Creates a new database and rebuilds the dropdown menu''' 76 global var 77 global current_database 78 global db_dropdown 79 db_name = db_name_textbox.get() + '.db' 80 if db_name == '.db': 81 output_textbox.insert(END, 'ERROR: Please enter a name for your new database.\n\n') 82 83 elif db_name in databases: 84 output_textbox.insert(END, 'ERROR: Database already exists.\n\n') 85 else: 86 try: 87 with sqlite3.connect(db_name) as db: 88 cursor = db.cursor() 89 except: 90 output_textbox.insert(END, 'ERROR: Please try again.\n\n') 91 else: 92 # If all OK add new name to databases list 93 databases.append(db_name) 94 95 # Now destroy current dropdown menu and rebuild it with new list. 96 db_dropdown.destroy() 97 98 var = StringVar() 99 var.set('Choose database:') 100 db_dropdown = OptionMenu(frame_buttons, var, *databases, command=choose_database) 101 db_dropdown.grid(row=0, column=0, sticky=NW) 102 103 def choose_database(value): 104 '''Takes the appropriate database passed from the dropdown menu 105 selection and updates the current_database global variable''' 106 global current_database 107 current_database = value 108 109 def clear_output(): 110 '''Empies the text in the Output textbox''' 111 output_textbox.delete(0.0, END) # clear output text box 112 113 def get_tables(): 114 '''Finds a list of tables in the database''' 115 query = '''SELECT name FROM sqlite_master 116 WHERE type='table' 117 ORDER BY name;''' 118 run_sql(query) 119 120 def quit_tool(): 121 '''closes the window and then quits''' 122 window.withdraw() 123 window.quit() 124 125 def run_query(): 126 '''Collects the query string from the SQL entry textbox and runs the query''' 127 # Check database selected: 128 if current_database == '': 129 output_textbox.insert(END, 'ERROR: Please choose a database\n\n') 130 else: 131 # Fetch users SQL 132 query = sql_textbox.get(0.0, END) 133 run_sql(query) 134 sql_textbox.delete(0.0, END) # clear output text box 135 print(query) 136 137 def run_sql(sql): 138 '''Executes any SQL code passed to it and displays the output in the 139 Output textbox''' 140 # Connect to the database and run the SQL passed to this function 141 with sqlite3.connect(current_database) as db: 142 cursor = db.cursor() 143 # the cursor is required to navigate the database 144 cursor.execute(sql) 145 db.commit() 146 147 # use fetchall() to collect result of queries 148 result = cursor.fetchall() 149 150 # if a SELECT query, process result into a table 151 if sql[:6].upper() == 'SELECT': 152 table_info = cursor.description 153 result = pretty_tables(table_info, result) 154 else: 155 result = str(result) 156 157 result = result + '\n\n' 158 # Display the value held in the variable result in the output textbox 159 if len(result) > 50: 160 clear_output() 161 output_textbox.insert(END, result) 162 163 ##### main: 164 window = Tk() 165 window.title('Python Database Tool') 166 167 update_databases() 168 169 # Import images 170 python_logo = PhotoImage(file='python-logo.gif') 171 sqlite_logo = PhotoImage(file='sqlite-logo.gif') 172 173 # Create frames 174 frame_new_db = Frame(window) 175 frame_new_db.grid(row=3, column=0, pady=20, sticky=SW) 176 177 frame_buttons = Frame(window) 178 frame_buttons.grid(row=0, column=0) 179 180 frame_query = Frame(window) 181 frame_query.grid(row=2, column=0, sticky=NW) 182 183 frame_output = Frame(window) 184 frame_output.grid(row=2, column=1, rowspan=2) 185 186 # Add labels 187 Label(frame_new_db, text='Create a new database:').grid(row=0, column=0, columnspan=2, sticky=NW) 188 Label(frame_new_db, text='Choose a name: ').grid(row=1, column=0, sticky=NW) 189 Label(frame_query, text='Add SQL:').grid(row=0, column=0, sticky=NW) 190 Label(frame_output, text='Output: ').grid(row=0, column=0, sticky=NW) 191 192 # Create database name box 193 db_name_textbox = Entry(frame_new_db, width=10) 194 db_name_textbox.grid(row=1, column=1, sticky=NW) 195 196 # Create sql entry box 197 sql_textbox = Text(frame_query, width=50, height=10, background=textbox_colour) 198 sql_textbox.grid(row=1, column=0, sticky=NW) 199 200 # Create scrolling textbox 201 output_textbox = ScrolledText(frame_output, width=65, height=20, background=textbox_colour) 202 output_textbox.grid(row=1, column=0, sticky=NW) 203 204 # Add images in label widgets 205 Label(window, image=python_logo).grid(row=1, column=0, sticky=NW) 206 Label(window, image=sqlite_logo).grid(row=1, column=1, sticky=NW) 207 208 # Add buttons 209 button_quit = Button(frame_buttons, text="Quit", width=10, command=quit_tool) 210 button_quit.grid(row=0, column=2, sticky=NE) 211 button_clear = Button(frame_output, text="Clear result box", command=clear_output) 212 button_clear.grid(row=2, column=0, sticky=NE) 213 button_run = Button(frame_query, text="Run SQL", width=10, command=run_query) 214 button_run.grid(row=2, column=0, sticky=NW) 215 button_tables = Button(frame_buttons, text="List Tables", width=15, command=get_tables) 216 button_tables.grid(row=0, column=1, sticky=NE) 217 button_submit = Button(frame_new_db, text="Build database", command=build_database) 218 button_submit.grid(row=2, column=0, sticky=NW) 219 220 # Add dropdown menu 221 var = StringVar() 222 var.set('Choose database:') 223 # builds dropdown from databases list but if empty (first use situation) handles this. 224 db_dropdown = OptionMenu(frame_buttons, var, *databases if databases else ['empty'], command=choose_database) 225 db_dropdown.grid(row=0, column=0, sticky=NW) 226 227 ##### Run mainloop 228 window.mainloop() 229