sqlite-helper.py

import contextlib
import sqlite3

import tkinter as tk
import tkinter.ttk as ttk

from tkinter.filedialog import askopenfilename, asksaveasfilename
from tkinter import messagebox
from tkinter.scrolledtext import ScrolledText


@contextlib.contextmanager
def _try_msgbox():
    try:
        yield
    except Exception as e:
        messagebox.showerror('An Error Occurred', str(e))


class SQLText(ScrolledText):
    
    @property
    def text(self):
        return self.get(1.0, tk.END)



class RecordView(ttk.Treeview):
    def __init__(self, master, **kwargs):
        # set up scroll bars. first, wrap in a frame
        self._frame   = tk.Frame(master)
        super().__init__(self._frame, **kwargs)
        self._scrolly = ttk.Scrollbar(self._frame, orient=tk.VERTICAL)
        self._scrollx = ttk.Scrollbar(self._frame, orient=tk.HORIZONTAL)
        
        self._scrolly.pack(side=tk.RIGHT, fill=tk.Y)
        self._scrollx.pack(side=tk.BOTTOM, fill=tk.X)
        self.pack(fill=tk.BOTH, expand=tk.YES)
        
        # link scrollbars with main widget
        self.config(
            yscrollcommand=self._scrolly.set,
            xscrollcommand=self._scrollx.set,
        )
        self._scrolly.config(command=self.yview)
        self._scrollx.config(command=self.xview)
        
        # then expose frame's pack method
        self.pack = self._frame.pack
        
        self.config(show='headings')
        self._col_count = 0
        self._row_ids = []
        

    
    
    def reset(self):
        self.delete(*self._row_ids)
        self._row_ids.clear()
        self._col_count
        
    
    
    def render_cursor(self, cursor):
        if len(self._row_ids) > 0:
            self.reset()
        
        if cursor.description is None:
            self.configure(columns=(0,))
            self._row_ids.append(
                self.insert('', 'end', values=('OK'))
            )
        else:
            headers = [h[0] for h in cursor.description]
            self._col_count = len(headers)
            self.configure(columns=tuple(range(self._col_count)))
            for i, h in enumerate(headers):
                self.heading(i, text=h)
            
            for row in cursor:
                self._row_ids.append(
                    self.insert('', 'end', values=row)
                )


    @property
    def ncolumns(self):
        return self._col_count



class SQLApp(ttk.PanedWindow):
    def __init__(self, master, **kwargs):
        kwargs.update(orient=tk.VERTICAL)
        super().__init__(master, **kwargs)
        
        # configure panes
        #self.config(orient=tk.VERTICAL, bg='grey')
        self.sql_area = SQLText(self)
        self.sql_area.insert('1.0', (
            'Ctrl + O -> Open a database\n'
            'Ctrl + N -> Create a new database\n'
            'Crtl + R -> Reset column widths\n'
            'Ctrl + ? -> Open full text help\n'
            'F5       -> Run query'
        ))
        self.output_table = RecordView(self)
        self.add(self.sql_area)
        self.add(self.output_table._frame)
        
        self.db_connection = sqlite3.connect(':memory:')
        
        # connect keyboard shortcuts
        self._root().bind('<F5>', lambda *_: self.run_query())
        self._root().bind('<Control-O>', lambda *_: self.ask_openfile())
        self._root().bind('<Control-o>', lambda *_: self.ask_openfile())
        self._root().bind('<Control-N>', lambda *_: self.ask_createfile())
        self._root().bind('<Control-n>', lambda *_: self.ask_createfile())
        self._root().bind('<Control-R>', lambda *_: self.reset_column_widths())
        self._root().bind('<Control-r>', lambda *_: self.reset_column_widths())
        self._root().bind('<Control-?>', lambda *_: self.show_help())
        self._root().bind('<Control-/>', lambda *_: self.show_help())
        
        # connect events for viewing cell contents
        self.output_table.bind('<Double-Button-1>', self._view_cell)
        
        
    
    
    def _view_cell(self, e):
        if self.output_table.identify_region(e.x, e.y) != 'cell':
            return
        focus = self.output_table.focus()
        if not focus:
            return
        values = self.output_table.item(focus)['values']
        
        i = int(self.output_table.identify_column(e.x)[1:]) - 1
        self.popup_textbox(values[i])
        
    
    
    def reset_column_widths(self):
        for n in range(self.output_table.ncolumns):
            # 200 is the tk default according to the docs
            self.output_table.column(n, width=200)  
            
    
    
    def run_query(self, query=None):
        query = query or self.sql_area.text
        
        with _try_msgbox():
            c = self.db_connection.execute(query)
            self.output_table.render_cursor(c)
    
    
    def ask_openfile(self):
        db_file = askopenfilename(
            title='Select an SQLite3 database to connect to',
            filetypes=(
                ('SQLite3 Databases', '*.sqlite *.sqlite3 *.db'),
                ('All files', '*.*'),
            )
        )
        if not db_file:
            return
        
        self.connect_to_db(db_file)
        
    
    def ask_createfile(self):
        db_file = asksaveasfilename(
            title='Create a new sqlite3 database',
            confirmoverwrite=True,
            defaultextension='.sqlite',
            filetypes=(
                ('SQLite3 Databases', '*.sqlite *.sqlite3 *.db'),
                ('All files', '*.*'),
            )
        )
        if not db_file:
            return
        
        self.connect_to_db(db_file)
        
    
    def connect_to_db(self, db_file):
        with _try_msgbox():
            con = sqlite3.connect(db_file)
            self.db_connection.close()
            self.db_connection = con
            self.run_query("select * from sqlite_master order by type desc")
    
    
    def show_help(self):
        self.popup_textbox((
            'Ctrl + O -> Open a database\n'
            'Ctrl + N -> Create a new database\n'
            'Crtl + R -> Reset column widths\n'
            'Ctrl + ? -> Open full text help\n'
            'F5       -> Run query'
        ))
    
    
    def popup_textbox(self, text, *, title=''):
        t = tk.Toplevel(self._root())
        t.bind('<Escape>', lambda *e: t.destroy())
        t.title(title or 'View Text')
        s = ScrolledText(t) 
        s.pack(expand=tk.YES, fill=tk.BOTH)
        s.insert('1.0', text)
        s.config(state=tk.DISABLED)  # read-only
        
    

class StatusBar(tk.Frame):
    def __init__(self, master, mode='text'):
        super().__init__(master)
        #self._progress = ttk.ProgressBar(self)  # TODO
        self._txt = ttk.Label(self)
        self._mode = None
        self.mode = mode
    
    def status(self, text):
        self._txt.config(text=text)
        
    
    @property
    def mode(self):
        return self._mode
    
    @mode.setter
    def mode(self, mode):
        mode = mode.lower().strip()
        if mode == 'text':
            #self._progress.pack_forget()
            self._txt.pack(fill=tk.X)
        #elif mode == 'progress':
            #self._txt.pack_forget()
            #self.progress.pack( fill=tk.X)
        else:
            raise ValueError('mode not supported:', mode)
    


class Root(tk.Tk):
    def __init__(self, master=None, **kwargs):
        super().__init__(master, **kwargs)
        height = 500
        width = 700
        
        # disable auto-resize
        self.geometry(f'{width}x{height}')
        self.title('SQLite Query Helper')
        self._app = SQLApp(self)
        self._app.pack(expand=tk.YES, fill=tk.BOTH)
        
        # split the pane in half.  This has to happen
        # after the widget has finished drawing, which 
        # happens some time before 30 ticks... (~30ms)
        self.after(30, lambda:self._app.sashpos(0, height//2))
                
        #self._status_bar = StatusBar(self)
        #self._status_bar.pack(side=tk.BOTTOM, fill=tk.X)
        
        # ttk style stuff
        self.style = ttk.Style(self)
        self.style.configure( 
            'TPanedwindow', 
                background='grey',        
        )
        
        

def main():
    Root().mainloop()
    
    

if __name__ == '__main__':
    main()