Python TKinter data entry window GUI for SQLITE3 table

Question

I would like to offer for review a simple tkinter GUI window in Python 3 to be used for data entry into an SQLite database table. As a noob, this is my first attempt at OOP and my first experience with tkinter, therefore, I was hoping for a thoughough review before I got too carried away.

The script below builds a simple table bar in a sqlite database foo.sqlite. This is for demo purposes. In reality, the database schema would be built elsewhere.

An instance of the class EntryWindow provides the GUI for the window. Upon initialization, the window will read the column names of the given table (bar) from the sqlite database (foo.sqlite) and the build TK labels from the column names as well as entry fields for each column. Finally, there is a button added which, when pressed will get the values from the entry fields and add a row to the database.

There is obviously a lot of additional logic which needs to be added (i.e. some data validation, disabling fields for PK's where the database should be assigning the next value, etc.) but I am at a place where I feel like I need to check in before I start adding complexity to the design. Here's the code:

"""
A simple TKinter GUI to enter data into a given table in a database.

This program will build a small sample table into a given database
and then build a simple TKinter window with label and entry widgets
for each column in the table.
"""
import sqlite3
import tkinter as tk
from tkinter import N, S, E, W
from tkinter import TOP, BOTTOM, LEFT, RIGHT, END, ALL


def main():
    """Main function for demo."""
    # define some variables for demo only.
    # In full app, these will come programmatically.
    db = 'foo.sqlite'
    tbl = 'bar'
    columns = 'ID integer primary key', 'bizz text', 'bam text'
    create_table(db, tbl, *columns)

    root = tk.Tk()
    demo_window = EntryWindow(root, *[db, tbl])
    root.mainloop()


# create a sample table for demo purposes only.
# in full app the database schema would be created elsewhere.
def create_table(database, table, *col_defs):
    """
    Insert a simple table into sqlite3 database.

    Args:
        database (string):
        Name of database function will be creating table in.

        table (string):
        Name of table to be created in given database.

        *col_defs (tuple of strings):
        A tuple of strings containing the SQL column definitions for the
        given table being created in given database.
    """
    stmnt = (('create table {}('+('{},'*len(col_defs))[:-1]+');')
             .format(table, *col_defs))
    with sqlite3.connect(database) as conn:
        c = conn.cursor()
        c.execute('drop table if exists {};'.format(table))
        c.execute(stmnt)
        conn.commit()


class EntryWindow(tk.Frame):

    """
    Provides a simple data entry window for a given table in given database.

    Automatically generates labels and entry fields based on the column
    headers for the given table.  Provides a button to submit the row of data
    into the table and a button to close window.
    """

    def __init__(self, master=None, *args):
        tk.Frame.__init__(self, master)
        self.master = master
        self.database = args[0]
        self.table = args[1]
        self.init_window()

    def init_window(self):
        """Build the entry window."""
        self.master.title('Enter data into {}'.format(self.table.upper()))
        self.grid(column=0, row=0, sticky=(N, W, E, S), padx=10, pady=5)
        self.grid_columnconfigure(0, weight=1)
        self.grid_rowconfigure(0, weight=1)

        def get_col_names(self):
            """Retrieve column names for given table in database."""
            with sqlite3.connect(self.database) as conn:
                c = conn.cursor()
                c.execute("PRAGMA table_info('{}')".format(self.table))
                # Currently only uses one value from returned tuple.
                # TODO: Add logic to utilize type, not null and PK fields.
                self.col_names = [x[1] for x in c.fetchall()]
            return self.col_names

        self.column_names = get_col_names(self)

        # Add a label and entry box for each column in table.
        # TODO: Add functionality to gray out primary key fields where the
        #       database should be assigning the next value.
        # TODO: Add some validation logic.
        self.item_entry = []
        for item in self.column_names:
            num = len(self.item_entry)
            # print(num, ' --> '+item)
            tk.Label(self, text=item).grid(row=num, column=0,
                                           pady=1, sticky=E)
            self.item_entry.append(tk.Entry(self))
            self.item_entry[num].grid(row=num, column=1, pady=1, padx=5)

        def add_item(self):
            """Get entries from input fields and insert into database table."""
            entries = [e.get() for e in self.item_entry]
            # Build the SQL statement
            stmnt = ('insert into {0}({1}) values ({2})'
                     .format(self.table, ','.join(self.column_names),
                             ':'+',:'.join(self.column_names)))
            # print(stmnt, entries)
            with sqlite3.connect(self.database) as conn:
                c = conn.cursor()
                c.execute(stmnt, entries)
                conn.commit()
            clear_fields(self)

        def clear_fields(self):
            """Clear fields of entry windo and return focus to first field."""
            for e in self.item_entry:
                e.delete(0, END)
            self.item_entry[0].focus()

        # Add button to submit user inputs into database.
        submit_button = tk.Button(self, text='Add Item', width=8,
                                  command=lambda: add_item(self))
        submit_button.grid(row=3, column=0, sticky=E, pady=10, padx=1)

        # Add a cancel button which closes window.
        quit_button = tk.Button(self, text='Cancel', width=8,
                                command=self.quit)
        quit_button.grid(row=3, column=1, sticky=W, pady=10, padx=1)

if __name__ == '__main__':
    main()

Some specific questions I have are:

• Is my class structure proper? As I mentioned above, this is my first venture into OOP, and I was a little unclear as to what functionality should be in the EntryWindow class and what should be upper level functions.
• I have read in multiple places on SE (like here) that caution against classes engaging in IO, which obvioulsy (I think?) doesn't apply to a gui class, but got me thinking, should the database interactions (like the get_col_names method) be in a seperate database class?
• Something is screwey with the way I have the args defined for the __init__ method. I don't think I should be using *args notation here as: def __init__(self, master=None, *args): and instantiating like demo_window = EntryWindow(root, *[db, tbl]), but when I change them to explicit like def __init__(self, database, table, master=None): and instantiate like demo_window = EntryWindow(root, db, tbl) (which seems proper to me), I get an error AttributeError: 'str' object has no attribute 'tk'

Answer 1

There are a few things that need looking at.

There seems to be an error in the def add_item method. The 'Build the SQL' section joins sections for table, column names and new values from the entry boxes but the last two items in the .format method BOTH insert 'column_names' whereas I would expect the last one to be 'entries'.

You seem to have gotten confused with the use of *args.

In 'main' you have

create_table(db, tbl, *columns)

and

demo_window = EntryWindow(root, *[db, tbl])

The asterisks (i.e. *) is used in the method DEFINITION as a place holder for the arguments that may not appear in the method call. If they do appear then they will be accessible as a tuple in the body of the method definition.

So

create_table(db, tbl, columns)  # no *

would pass the columns list as the first item of the *col_defs in the create_table method and be accessible as

col_defs[0].

Also

demo_window = EntryWindow(root, [db, tbl])  # no *

would pass the database and table list as the first item of the *args in the EntryWindow method to be accessible as

args[0][0] 

and

args[0][1]. 

Note the double indices. The first accesses the first item in the *args tuple (even if there is only one!) and the second accesses the item within your list.

You could change the call to

demo_window = EntryWindow(root, db, tbl)  # no list and still no *

and then use

args[0] 

and

args[1]

as you have done but to access the items within the *args tuple not items within a list.