2ZyBjm0m

1"""
2A simple TKinter GUI to enter data into a given table in a database.
3
4This program will build a small sample table into a given database
5and then build a simple TKinter window with label and entry widgets
6for each column in the table.
7"""
8import sqlite3
9import tkinter as tk
10from tkinter import N, S, E, W
11from tkinter import TOP, BOTTOM, LEFT, RIGHT, END, ALL
12
13
14def main():
15    """Main function for demo."""
16    # define some variables for demo only.
17    # In full app, these will come programmatically.
18    db = 'foo.sqlite'
19    tbl = 'bar'
20    columns = 'ID integer primary key', 'bizz text', 'bam text'
21    create_table(db, tbl, *columns)
22
23    root = tk.Tk()
24    demo_window = EntryWindow(root, *[db, tbl])
25    root.mainloop()
26
27
28# create a sample table for demo purposes only.
29# in full app the database schema would be created elsewhere.
30def create_table(database, table, *col_defs):
31    """
32    Insert a simple table into sqlite3 database.
33
34    Args:
35        database (string):
36        Name of database function will be creating table in.
37
38        table (string):
39        Name of table to be created in given database.
40
41        *col_defs (tuple of strings):
42        A tuple of strings containing the SQL column definitions for the
43        given table being created in given database.
44    """
45    stmnt = (('create table {}('+('{},'*len(col_defs))[:-1]+');')
46             .format(table, *col_defs))
47    with sqlite3.connect(database) as conn:
48        c = conn.cursor()
49        c.execute('drop table if exists {};'.format(table))
50        c.execute(stmnt)
51        conn.commit()
52
53
54class EntryWindow(tk.Frame):
55
56    """
57    Provides a simple data entry window for a given table in given database.
58
59    Automatically generates labels and entry fields based on the column
60    headers for the given table.  Provides a button to submit the row of data
61    into the table and a button to close window.
62    """
63
64    def __init__(self, master=None, *args):
65        tk.Frame.__init__(self, master)
66        self.master = master
67        self.database = args[0]
68        self.table = args[1]
69        self.init_window()
70
71    def init_window(self):
72        """Build the entry window."""
73        self.master.title('Enter data into {}'.format(self.table.upper()))
74        self.grid(column=0, row=0, sticky=(N, W, E, S), padx=10, pady=5)
75        self.grid_columnconfigure(0, weight=1)
76        self.grid_rowconfigure(0, weight=1)
77
78        def get_col_names(self):
79            """Retrieve column names for given table in database."""
80            with sqlite3.connect(self.database) as conn:
81                c = conn.cursor()
82                c.execute("PRAGMA table_info('{}')".format(self.table))
83                # Currently only uses one value from returned tuple.
84                # TODO: Add logic to utilize type, not null and PK fields.
85                self.col_names = [x[1] for x in c.fetchall()]
86            return self.col_names
87
88        self.column_names = get_col_names(self)
89
90        # Add a label and entry box for each column in table.
91        # TODO: Add functionality to gray out primary key fields where the
92        #       database should be assigning the next value.
93        # TODO: Add some validation logic.
94        self.item_entry = []
95        for item in self.column_names:
96            num = len(self.item_entry)
97            # print(num, ' --> '+item)
98            tk.Label(self, text=item).grid(row=num, column=0,
99                                           pady=1, sticky=E)
100            self.item_entry.append(tk.Entry(self))
101            self.item_entry[num].grid(row=num, column=1, pady=1, padx=5)
102
103        def add_item(self):
104            """Get entries from input fields and insert into database table."""
105            entries = [e.get() for e in self.item_entry]
106            # Build the SQL statement
107            stmnt = ('insert into {0}({1}) values ({2})'
108                     .format(self.table, ','.join(self.column_names),
109                             ':'+',:'.join(self.column_names)))
110            # print(stmnt, entries)
111            with sqlite3.connect(self.database) as conn:
112                c = conn.cursor()
113                c.execute(stmnt, entries)
114                conn.commit()
115            clear_fields(self)
116
117        def clear_fields(self):
118            """Clear fields of entry windo and return focus to first field."""
119            for e in self.item_entry:
120                e.delete(0, END)
121            self.item_entry[0].focus()
122
123        # Add button to submit user inputs into database.
124        submit_button = tk.Button(self, text='Add Item', width=8,
125                                  command=lambda: add_item(self))
126        submit_button.grid(row=3, column=0, sticky=E, pady=10, padx=1)
127
128        # Add a cancel button which closes window.
129        quit_button = tk.Button(self, text='Cancel', width=8,
130                                command=self.quit)
131        quit_button.grid(row=3, column=1, sticky=W, pady=10, padx=1)
132
133if __name__ == '__main__':
134    main()