path: root/comp/work/14/sql/database.py

#### database_tool.py

# Import libraries:
from tkinter import *
from tkinter.scrolledtext import *
import sqlite3
import os # needed to obtain OS specific methods for navigating file structures

# Global Variables
current_database = ''
databases = []
textbox_colour = 'linen'

#### Utility Functions
def pretty_tables(info, body):
    '''Called to handle SQL queries beginning with SELECT.
       It then formats the output from sqlite3 into easy tables'''
    column_widths = []
    headings = []
    
    # work hard to get headings and put them into a list
    # set column widths to length of heading strings
    for row in info:
        headings.append(row[0])
        column_widths.append(len(row[0]))

    # update column widths from body text
    for row in body:
        counter = 0
        for item in row:
            length = len(str(item))
            if length > column_widths[counter]:
                column_widths[counter] = length
            counter = counter+1
        
    # format results with a lot of string manipulation
    top_row = '+'
    heading_text = '| '
    body_text = '| '
    for width in column_widths:
        top_row = top_row + '-'*(width+3)
    top_row = top_row[:-1] + '+\n'
    
    counter = 0
    for item in headings:
        padding = ' '*(column_widths[counter] - len(str(item)))
        heading_text = heading_text + item + padding + ' | '
        counter = counter+1
    heading_text = heading_text + '\n'

    for row in body:
        counter = 0
        for item in row:
            padding = ' '*(column_widths[counter] - len(str(item)))
            body_text = body_text + str(item) + padding + ' | '
            counter = counter+1
        body_text = body_text + '\n| '
    body_text = body_text[:-2]
        
    text = top_row + heading_text + top_row + body_text + top_row
    return text

def update_databases():
    '''Looks through the folder where this script is
       found looking for database files'''
    number_of_dbs = 0
    files = os.listdir()
    for file in files:
        # select files where the last three characters are .db
        if file[-3:] == '.db':
            databases.append(file)

#### Button functions
def build_database():
    '''Creates a new database and rebuilds the dropdown menu'''
    global var
    global current_database
    global db_dropdown
    db_name = db_name_textbox.get() + '.db'
    if db_name == '.db':
        output_textbox.insert(END, 'ERROR: Please enter a name for your new database.\n\n')

    elif db_name in databases:
        output_textbox.insert(END, 'ERROR: Database already exists.\n\n')
    else:
        try:
            with sqlite3.connect(db_name) as db:
                cursor = db.cursor()
        except:
            output_textbox.insert(END, 'ERROR: Please try again.\n\n')
        else:
            # If all OK add new name to databases list
            databases.append(db_name)

            # Now destroy current dropdown menu and rebuild it with new list.
            db_dropdown.destroy()

            var = StringVar()
            var.set('Choose database:')
            db_dropdown = OptionMenu(frame_buttons, var, *databases, command=choose_database)
            db_dropdown.grid(row=0, column=0, sticky=NW)
 
def choose_database(value):
    '''Takes the appropriate database passed from the dropdown menu
       selection and updates the current_database global variable'''
    global current_database
    current_database = value
    
def clear_output():
    '''Empies the text in the Output textbox'''
    output_textbox.delete(0.0, END)  # clear output text box

def get_tables():
    '''Finds a list of tables in the database'''
    query = '''SELECT name FROM sqlite_master
             WHERE type='table'
             ORDER BY name;'''
    run_sql(query)

def quit_tool():
    '''closes the window and then quits'''
    window.withdraw()
    window.quit()

def run_query():
    '''Collects the query string from the SQL entry textbox and runs the query'''
    # Check database selected:
    if current_database == '':
        output_textbox.insert(END, 'ERROR: Please choose a database\n\n')
    else:
        # Fetch users SQL
        query = sql_textbox.get(0.0, END)
        run_sql(query)
        sql_textbox.delete(0.0, END)  # clear output text box
        print(query)

def run_sql(sql):
    '''Executes any SQL code passed to it and displays the output in the
       Output textbox'''
    # Connect to the database and run the SQL passed to this function
    with sqlite3.connect(current_database) as db:
        cursor = db.cursor()
        # the cursor is required to navigate the database
        cursor.execute(sql)
        db.commit()
        
        # use fetchall() to collect result of queries
        result = cursor.fetchall()

        # if a SELECT query, process result into a table
        if sql[:6].upper() == 'SELECT':
            table_info = cursor.description
            result = pretty_tables(table_info, result)
        else:
            result = str(result)
            
        result = result + '\n\n'
        # Display the value held in the variable result in the output textbox
        if len(result) > 50:  
            clear_output()
        output_textbox.insert(END, result)
   
##### main:
window = Tk()
window.title('Python Database Tool')

update_databases()

# Import images
python_logo = PhotoImage(file='python-logo.gif')
sqlite_logo = PhotoImage(file='sqlite-logo.gif')

# Create frames
frame_new_db = Frame(window)
frame_new_db.grid(row=3, column=0, pady=20, sticky=SW)

frame_buttons = Frame(window)
frame_buttons.grid(row=0, column=0)

frame_query = Frame(window)
frame_query.grid(row=2, column=0, sticky=NW)

frame_output = Frame(window)
frame_output.grid(row=2, column=1, rowspan=2)

# Add labels
Label(frame_new_db, text='Create a new database:').grid(row=0, column=0, columnspan=2, sticky=NW)
Label(frame_new_db, text='Choose a name: ').grid(row=1, column=0, sticky=NW)
Label(frame_query, text='Add SQL:').grid(row=0, column=0, sticky=NW)
Label(frame_output, text='Output: ').grid(row=0, column=0, sticky=NW)

# Create database name box
db_name_textbox = Entry(frame_new_db, width=10)
db_name_textbox.grid(row=1, column=1, sticky=NW)

# Create sql entry box
sql_textbox = Text(frame_query, width=50, height=10, background=textbox_colour)
sql_textbox.grid(row=1, column=0, sticky=NW)

# Create scrolling textbox
output_textbox = ScrolledText(frame_output, width=65, height=20, background=textbox_colour)
output_textbox.grid(row=1, column=0, sticky=NW)

# Add images in label widgets
Label(window, image=python_logo).grid(row=1, column=0, sticky=NW)
Label(window, image=sqlite_logo).grid(row=1, column=1, sticky=NW)

# Add buttons
button_quit = Button(frame_buttons, text="Quit", width=10, command=quit_tool)
button_quit.grid(row=0, column=2, sticky=NE)
button_clear = Button(frame_output, text="Clear result box", command=clear_output)
button_clear.grid(row=2, column=0, sticky=NE)
button_run = Button(frame_query, text="Run SQL", width=10, command=run_query)
button_run.grid(row=2, column=0, sticky=NW)
button_tables = Button(frame_buttons, text="List Tables", width=15, command=get_tables)
button_tables.grid(row=0, column=1, sticky=NE)
button_submit = Button(frame_new_db, text="Build database", command=build_database)
button_submit.grid(row=2, column=0, sticky=NW)

# Add dropdown menu
var = StringVar()
var.set('Choose database:')
# builds dropdown from databases list but if empty (first use situation) handles this.
db_dropdown = OptionMenu(frame_buttons, var, *databases if databases else ['empty'], command=choose_database)
db_dropdown.grid(row=0, column=0, sticky=NW)

##### Run mainloop
window.mainloop()