LibreOffice Base as a Transition and Learning Tool

In the year that has flown past me since my last post, here’s what has transpired…

I became more preoccupied with transitioning all of my essential computing tasks to open source, especially financial management, than with continuing my Python GUI project. As I wrote in my last post, LibreOffice Base has drawbacks that, for me, make it a deal-breaker as a permanent solution for my database needs. After further research and experimentation, I found it workable as a transition phase in my ever-lengthening goal of a GUI-based Python database application. Here’s what I’ve accomplished with Base so far: 1) I moved nearly three years of financial data from tables in Microsoft Access to a SQLite database. 2) I learned how to connect LibreOffice to my SQLite database, courtesy of this precious little ODBC driver. 3) I learned how to design a rudimentary GUI using Base’s forms and widgets that does almost everything my MS Access database did, enabling me to stop using Access altogether. 4) I set up an experimental desktop PC with Linux Mint, to confirm my Base database functions just as well in Linux as in Windows (it does). 5) I got better at SQL, since LibreOffice Base depends on it much more than Access for basic tasks. Example: Base doesn’t allow the construction of INSERT, DELETE or UPDATE queries like Access does; these functions must be done by issuing SQL commands directly. For repeated tasks, such as recurring transactions (example: payroll), I took to saving the SQL commands in text files and pasting them into Base’s SQL window. This showed me, among other things, that I needed to get much more proficient with SQL before I could hope to take on the complicated task of making my own graphical database front end.

Once I got my finances into Base, my motivation to continue with my Python database project waned. I love learning new skills, but unless they have an immediate usefulness to me, they can easily fall by the wayside. Last year Python was new and cool and fun, but this year it became clear how much I still have to learn in order to make it do the kinds of things I REALLY want to do, and that there are perfectly acceptable ways to do those things right now (such as using Base for my finances). I’m not a young man anymore, so time is getting shorter with each passing year. I have a wife, a social life and a full time job that all, rightfully, remain my priorities. There’s also…Minecraft!(along with two nephews and a couple of friends’ kids who love to play it with me).

However…

I’ve always believed that the reason a cliché becomes a cliché is because it’s true. It’s long been said that necessity is the mother of invention. In my case, exasperation with a gaping hole in LibreOffice Base’s functionality is the mother of a Python-based solution! Details (and some Python code!) to follow…in my next post. Stay tuned!

SIMply Distracting

Sometime in the last month, I ran across one of my all-time favorite PC games: SimCity 4. I was quickly pulled back into the wonderful (for me, at least) world of creating cities and watching them grow, carefully managing resources and the economy so as to attract as many “Sim citizens” as possible. Heck, the thought even crossed my mind to write a Python app to calculate the optimal procedure for growing a huge SimCity 4 metropolis with skyscrapers! One little problem: I’m absolutely clueless as to how to even begin such a task, or even think about it. Besides, I haven’t finished my little data management project yet. I like to think of myself as a firm believer in finishing one project before moving on to the next, though I adhere to this principle less than perfectly.

West Oceanside-Mar. 24, 1341404263254

America’s next great city?

Now back to Python land. As recounted in my last post, about a month ago I started dabbling in the sorcery known as ‘Qt’, and in doing so discovered the QSqlRelationalTableModel, an object that lets me query multiple Sqlite tables, complete with combo boxes providing lookup values from the “foreign” tables, without writing one single line of SQL. Next goal: drop a TableView based on a QSqlRelationalTableModel into my lovingly designed, Microsoft Access mimicking main form.

In other words, I want to get this:
TransactionsForm

…into this:

Capture

..and code the button and drop down boxes to insert, filter and sort transactions. The view based on the QSqlRelationalTableModel will go in the rectangular box. I just need to plan some time away from my responsibilities as Mayor so I can re-focus on my coding. Target date: Saturday, July 12. Nothing like a public commitment to help me focus on a goal! Wish me luck. I’ll have an update no matter what happens (or does not happen).

Marvelous MVC with QSRTM

UPDATE 2014-06-01: Added links to code examples that helped me learn how to do this.

Here it is, people…brought to you by the magic of the QSqlRelationalTableModel: my editable transactions display widget, complete with combo boxes for selecting categories and accounts from the ‘accounts’ table, coded in elegant, model-view-controller style and in less than 70 lines of code, counting the blank lines added for readability. I’m viewing data from multiple Sqlite tables, even editing, without a single line of SQL. All I have to do is tell ‘QSRTM’ (my affectionate nickname for my new friend, QSqlRelationalTableModel) the name of the table and what to display, and he obediently gets what I want the way I want it. Meanwhile Tkinter is throwing a tempter tantrum in the corner when I try to ask it nicely to please synchronize row selection across a set of list boxes.

While documentation such as Pyside’s is excellent and thorough, I find I need examples of how to use a class. I experiment with altering example code to fit what I am trying to accomplish, and when I feel like I have a grasp of the class’s basics, I go back to the documentation to get a full appreciation of all its capabilities. Two table model examples in particular were essential to me figuring out how to use the ‘QSRTM’: this one which demonstrates general use of a table model, and this one which is a ‘QSRTM’ example.

Screenshot below, and my code is here.

.
TransactionsForm

 

Becoming an SQL squirrel

The first chapter of my quest was the hunt for a suitable GUI element to display data in columns. I eventually settled on the Multilistbox, a custom widget I found that was created using the standard Tkinter Listbox widget. I then enthusiastically dove into learning all I could about Tkinter. This revealed the need to thoroughly test my Multilistbox with real-world data..of which I had none. Then I focused on methods of getting data into my DataQ app, which led to experimenting with code to parse data from text and CSV files. Before I knew it I was off on multiple, simultaneous rabbit trails, including trying to figure out how to parse data from Evernote notes. Before I knew it, I was wandering aimlessly and making little progress.

Now I’ve decided to focus back on the original goal: financial management. This would be, by far, the most useful function for me, something I would use on a daily basis. It will also be instructive for other possible uses for DataQ. I therefore have now imported all 3000+ of my financial transactions from my Microsoft Access database to Sqlite. I’m laying aside the GUI for now and focusing on the heart and soul of DataQ which is data! I need to learn how to get the data I want using queries, something I’ve had passing acquaintance with through MS Access, but don’t know thoroughly because Access lets me build queries graphically. That’s great for getting results quickly, not so great for learning.

I have two tables: ‘transactions’ and ‘accounts’, with the following structure:

    transactions

TransactionID * Integer primary key
Date
PayeeOrName
TransactionDescription
Category * Income or expense category. In correct bookkeeping terminology, also an ‘account.’
Income * Amount
Expense * Amount
Account * Asset or liability account, ex: checking, savings, credit card

    accounts

AccountNum * Integer primary key
AccountDescription
AccountType * Asset, liability, income, expense
Clear * 1 = transaction has cleared the bank, 0 = not cleared.

Both the ‘Category’ and ‘Account’ fields contain an ‘AccountNum’ from ‘accounts.’ Every transaction query includes a calculated field called ‘Net’ which subtracts the ‘Expense’ amount from the ‘Income’ amount, giving the net effect on the account balance. A typical SQL query will look like the following, which displays all transactions in descending order from most recent:

SELECT transactions.Date, transactions.PayeeOrName, transactions.TransactionDescription, accounts.AccountDescription AS Category, transactions.Income, transactions.Expense, (transactions.Income - transactions.Expense) AS Net, accounts_1.AccountDescription AS Account, transactions.Clear
FROM (transactions INNER JOIN accounts AS accounts_1 ON transactions.Account = accounts_1.AccountNum)
INNER JOIN accounts ON transactions.Category = accounts.AccountNum ORDER BY transactions.Date DESC, PayeeOrName ASC

The data source for the Multilistbox will be obtained by queries such as this, so I think it’s time I get to know Sqlite better.

A Quest Begins

I recently stumbled across two little applications that, along with my recent advances in figuring out Tkinter, have finally given a much needed boost of inspiration — and a goal to strive for.

First there was this:

phoneapp

This is a step-by-step tutorial for a simple phone list using Tkinter and SQLite.  The user selects a name from the list and presses the “Load” button to load it into the form at the top.  The form is used to make changes to the data; the list is just for viewing and selecting.  This is what I have wanted to accomplish, boiled down to its absolute simplest level.  This little app also answered a big question for me; how do I get data from an SQLite database table into a Tkinter form for viewing and editing?  The short answer: the Tkinter Listbox widget, which has an INSERT method.

Even better, I found this:

inventory

Now we’re talking! Even though it’s a bit buggy, it’s the closest thing I have yet seen to what I have wanted to accomplish. It’s got a system of relational tables: customers, invoices and items. It has forms that are called when the user wants to add or change data. But its most intriguing item is a custom widget called a MultiListbox:
multilist

I found this object fascinating, because it opened my eyes to the power of Tkinter. It’s actually possible to make custom widgets out of the basic ones provided. The MultiListbox is a set of regular Listboxes squashed together in a frame and synchronized. It lines up each data field in its own column. It accepts data input in the form of tuples, for example: (‘Date’,’Item Number’,’Quantity’,’Price’). Each tuple is a row of data in the MultiListbox.

So, now, at last, I’ve found my quest!

holygrail

I plan to build an app around two basic objects: what I call a “TableForm”, for filtering, viewing and selecting data….
TableForm

..and what I have named a “RecordForm”, which will be used to edit, add, delete or update a record…
RecordForm

These two base objects could be duplicated for each table in the underlying SQLite database.

So here’s the short version of my plan. I’m starting with the coding of the TableForm, since it’s really the heart of my app. I have broken the Table Form down into component classes, starting with the MultiListbox (the most complex part). I plan to code and test each object separately, then piece them all together to make the TableForm class. I’ll then repeat this process with the RecordForm. This rudimentary data management setup could be applied to an endless array of applications, from personal finance to getting data from the Web to tracking my running

In true Python spirit, I wanted to name my app “DataQuest”, but found out there is a company called Dataquest. I didn’t want to get in trouble with them or cause confusion, so I’ve decided to call my app “DataQ.” I can already see the most time-consuming part of development is just figuring out how stuff works. When I first saw the code for the MultiListbox, it might as well have been in Chinese. It has taken some in-depth study of my Tkinter links and a lot of trial-and-error experiments with the code to get it deciphered. I feel like one of the great seafaring explorers of the 15th and 16th centuries, crossing a turbulent, uncharted ocean to a new world, or a biologist trying to decode DNA sequences.

See below for what I have so far in my version of the MultiListbox base class, along with comments I wrote in the code that explain the various parts as I was able to figure out what they did.

# A custom widget consisting of multiple Listbox widgets, with each listbox containing a column of data.
# Based on MultiListBox code used by suhailvs@gmail.com (https://sourceforge.net/projects/pyinvoice/)
# The code I was able to figure out is explained in the comments.
# Functions with comment ???? are ones I haven't figured out yet.

from tkinter import *
class MultiListbox(Frame):
    '''MultiListbox made by Labels as table header and Listbox as table columns'''
    def __init__(self, master, lists):
        Frame.__init__(self, master)
        self.lists = [] # A list of listboxes
        self.footers = [] # List of listbox footers
        for l,w in lists: # l=label, w=width for each listbox
            # Each listbox gets its own frame
            frame = Frame(self); frame.pack(side=LEFT, expand=YES, fill=BOTH)

            # Label = listbox name
            Label(frame, text=l, borderwidth=1, relief=RAISED).pack(fill=X)

            # Create each listbox
            lb = Listbox(frame, width=w, borderwidth=0, selectborderwidth=0,
                 relief=FLAT, exportselection=FALSE)
            lb.pack(expand=YES, fill=BOTH)

            # Add a footer for each listbox. Available for calculated totals.
            footer = Label(frame)
            footer.pack(fill=X)

            # Lists = list of numerical references to each listbox (ex .43982632.44021632.44021744.44022080)
            # Keeps track of listboxes after they are created.
            self.lists.append(lb)
            self.footers.append(footer)

            # Each listbox gets these bindings

            # left mouse button pressed. Required so left click selects entire line.
            lb.bind('', lambda e, s=self: s._select(e.y))

            # double click will eventually be what brings up the transaction edit form.
            lb.bind('', lambda e, s=self: s.double_click())

            # These came with the original MLB code but don't seem to do anything.
                #lb.bind('', lambda e, s=self: s._select(e.y)) # left mouse button pressed while moving
                #lb.bind('', lambda e: 'break') # mouse pointer leaves the widget
                #lb.bind('', lambda e, s=self: s._b2motion(e.x, e.y))
                #lb.bind('', lambda e, s=self: s._button2(e.x, e.y)) #right mouse button pressed

        # The frame that will contain the individual list boxes, each with its own frame (see above).
        frame = Frame(self); frame.pack(side=LEFT, fill=Y)
        Label(frame, borderwidth=1, relief=RAISED).pack(fill=X)
        sb = Scrollbar(frame, orient=VERTICAL, command=self._scroll)
        sb.pack(expand=YES, fill=Y)

        # lists[0] = the first (left most) list box.
        self.lists[0]['yscrollcommand']=sb.set

    def double_click(self):
        print(self.item_selected)

    def _select(self, y):
        row = self.lists[0].nearest(y) # integer index of selected row

        # Calls the selection_clear function to un-select whatever row was selected before left button was clicked.
        self.selection_clear(0, END)

        # Calls the selection_set function to select the line clicked on.
        self.selection_set(row)
        return 'break'

    def _button2(self, x, y): #????
        for l in self.lists: l.scan_mark(x, y)
        return 'break'

    def _b2motion(self, x, y): #????
        for l in self.lists: l.scan_dragto(x, y)
        return 'break'

    def _scroll(self, *args): #????
        for l in self.lists:
            l.yview(*args)

    def curselection(self): #????
        return self.lists[0].curselection()

    def delete(self, first, last=None):
        for l in self.lists:
            l.delete(first, last)

    def get(self, first, last=None):
        result = []
        for l in self.lists:
            result.append(l.get(first,last))
        if last: return list(map(*[None] + result))
        return result

    def index(self, index):
        self.lists[0].index(index)

    def insert(self, index, *elements): #Elements = the fields in a row.

        # Loop through each field in the row
        for e in elements:

            # Each field will have an index number. Ex: e[0] = ID, e[1] = Date, etc.
            i = 0
            for l in self.lists:
                # Insert each field in its applicable list box
                # Insert a blank if the field is None, otherwise boxes won't align.
                if e[i] == None:
                    l.insert(index, " ")
                else:
                    l.insert(index, e[i])
                i = i + 1

    def size(self):
        return self.lists[0].size()

    def see(self, index):
        for l in self.lists:
            l.see(index)

    def selection_anchor(self, index):
        for l in self.lists:
            l.selection_anchor(index)

    def selection_clear(self, first, last=None):
        # Unselects the entire line by looping over each of the list boxes that make up the MLB.
        for l in self.lists:
            l.selection_clear(first, last)

    def selection_includes(self, index):
        return self.lists[0].selection_includes(index)

    def selection_set(self, first, last=None):
        # first = the index of the selected row

        # self.item_selected = a list whose 1st item is the index of the row, followed by the entire row.
        # Example:
        # [6,134,'2014-02-03','Publix','',188,'$0.00','$3.48',197,1]

        self.item_selected=[first,]+self.get(first)

        for l in self.lists:
            # Sets the selection to the same row in all list boxes
            l.selection_set(first, last)

    def not_focus(self):
        for l in self.lists:
            l['takefocus']=False

    def calc_total(self,column):
        # Returns the total of the selected column number. Column must contain numeric values only.
        total = 0
        for n in range(0,self.lists[column].size()):
            total = total + float(self.lists[column].get(n))
        return total

    def footer_val(self,column,value):
        # Sets the text to be displayed in the footer of the column specified.
        v = StringVar()
        self.footers[column].config(textvariable=v)
        v.set(value)

If you scrolled all the way through that huge block of code to get here, I’ll bet you are the kind of person who sits in a movie theater and waits for all the credits to finish, hoping for something fun or entertaining at the end, or a sneak peak at a new movie. So as to not disappoint you, here’s an relevant item:
Monty-Python-and-the-Holy-Grail-monty-python-and-the-holy-grail-4968359-845-468

Wrestling Text

One thing I have learned about learning is that it’s often a random, messy process, full of unexpected twists and turns. I set out toward a particular goal, intent on learning every single step required to get to that goal, but then some unexpected obstacle trips me up on my way and I become tempted to give up in frustration. Then, to my surprise and wonder, the obstacle itself becomes a learning experience, and I attain valuable knowledge I wouldn’t have discovered if it had been 100% up to me to find it. This new knowledge may cause me to reconsider my goal and alter it, or even scrap it altogether and set a new one. Those who have followed this blog have seen this process first hand. With that in mind, a funny thing happened on my way to completion of my latest cool app. I found my quest unexpectedly distracted by that noisy, demanding child whose name is Immediate Practical Need.

At my job, every week I have to upload a very precisely formatted text file to our third-party retirement plan administrator. The administrator tracks this data for us to ensure compliance with federal regulations and reporting requirements. The text file includes weekly payroll information, such as employee names, gross pay and retirement plan contribution amounts. Each field must be an exact number of spaces and aligned just right in order to upload correctly.

Currently, I produce this file using Microsoft Access 2010. Access has an option called “Saved Exports” that I use to export the payroll data to a text file. Back when I set this process up I didn’t know much about MS Access’s built-in programming language (Visual Basic for Applications, aka VBA), so all the precise spacing required for the text column is “hard wired” into the report object. Should the plan administrator ever change their file format, I’d have to change the report manually. Another problem is that Access’s “Saved Exports” can’t be edited without some high-end hacking, so if the directory where I need to save the file changes, or I have to move my applications and data to a new PC, I’d have to re-do the whole “Saved Export.” I could just re-do this whole procedure using VBA code, but I decided I’d prefer to use this as an opportunity to learn more Python! Result: “CsvChamp.”

“CsvChamp” is a tool set of Python functions I designed to import, format, display and export data from a comma-separated (CSV) file. At first glance, it’s obvious none of these require Python to accomplish. The same results could be produced using a spreadsheet app. Their power, like everything in Python, lies in their programmability and flexibility. The heart of this tool set is the function GetCsvData. It reads content from a CSV file into a Python dictionary. It adds a unique integer row ID to each row read from the file, and these row IDs become the keys in the dictionary and the rows of data are stored as list object values. Once the data is in a Python dictionary, the sky is the limit, as each and every item can be accessed using code. In this way, code can be written to sort, display, add, delete, summarize and so on. Not only does this have an immediate practical use to me on the job, I can also see this applying to my database management goals. Here’s a summary of what each tool does. See the comments in the code for more details.

CsvFilePicker uses good old Tkinter to prompt the user to select a CSV file.

GetCsvData reads the CSV data into a dictionary, so a line like this:
Doe,John,456 Any St.,Anywhere,TX,45678,(997) 777-7878,john@doeadeer.com

is stored like this:
{1:[‘Doe’,’John’,’456 Any St.’,’Anywhere’,’TX’,’45678′,'(997) 777-7878′,’john@doeadeer.com’]}

GetCsvCols reads the column names from the first line of the CSV file and stores them as a list, so this:
Last,First,Address,City,State,Postal Code,Phone,Email

is stored like this:
[‘Last’,’First’,’Address’,’City’,’State’,’Postal Code’,’Phone’,’Email’]

CreateColSpec examines each column in a dictionary produced by Get CsvData to determine how to align and space the columns for display or export. It adds one space to the maximum with of each column, and defaults to left-alignment of each column.

FormatCsvCol takes a column of data and adds spaces before and/or after it — depending on the specifications read from the dictionary produced by CreateColSpec — so it will align correctly when displayed or output.

Here’s some sample input and output:

Messy, ugly, mean CSV file:
CsvChampBefore

Clean, pretty, friendly output file:
CsvChampAfter

Of course, all of this is 100% flexible. Column specifications can be find-tuned by individual column, so some could be left-aligned and others right-aligned. We don’t have to use the column names in the CSV file, nor do we even have to have the CSV file provide column names at all; we could easily pick our own. We could change the display options so that only selected columns are displayed. The surface has barely been scratched! Feel free to try it yourself. Enjoy!

CSV champ code below. Sorry for the long lines. I haven’t been able to get wrapping to work right yet.

def CsvFilePicker():
    """Prompts user to select a file, using the GUI file dialog. Returns file path.
    """
    # Use csv & the Tkinter graphical file dialog.
    import csv
    import Tkinter, tkFileDialog

    # Asks the user to select a file to load
    root = Tkinter.Tk()
    root.withdraw()
    file_path = tkFileDialog.askopenfilename()

    return file_path

def GetCsvData(file_path):
    """Reads data from a CSV file and returns a dictionary of the file's data, assigning each row a unique integer row ID.
    """
    import csv
    # Copy the contents of the file into a dictionary.
    with open(file_path,"rb") as csvData:

        FileReader = csv.reader(csvData)

        # FileData = the dictionary to hold the data.
        FileData = {}

        # Counts number of rows in file, to use for row IDs.
        row_count = 1
        for row in FileReader:
            row_id = row_count

            # Add the row to the dictionary, assigning the
            # row ID as its key.
            FileData[row_id] = row

            row_count = row_count+1

    return FileData

def GetCsvCols(FileData):
    """Reads column names from the first value of a dictionary created by GetCSVdata.
    """
    col_names = []
    for item in FileData[1]:
        col_names.append(item)

    return col_names

def CreateColSpec(FileData):
    col_spec = {}

    #Iterate through each column
    for c in range(0, len(FileData[1])):

        #Set the maximum with to the column name
        max = len(file[1])

        #Check each row in the col
        for r in FileData:

            #If the current row in the column > the
            #current maximum, store it.
            if len(FileData[r]) > max:
                max = len(FileData[r])

        #Specify the width of the column to be 1
        #space more than the width of its longest item.
        col_spec[FileData[1]] = [max+1,"L"]

    return col_spec

def ListCsvCols(col_names):
    """List and count the columns."""
    for item in range(1, len(col_names)+1):
        print str(item)+": " + col_names[item-1]

def FormatCsvCol(width,align,cont):
    """Returns a column of data with content aligned
    according to these specifications:

    width = total number of characters allotted to the column including leading and/or trailing spaces.

    align = "L" (left), "C" {center} or "R" (right).

    cont = content (the actual data to be displayed).

    """

    # The total number of empty spaces in the column.
    spaces = width - len(cont)

    if align == "L":
        # Place all the spaces after the content
        column = cont + (spaces * " ")

    elif align == "R":
        # Place all the spaces before the content.
        column = spaces * " " + cont

    elif align == "C":
        """Divide the number of spaces by 2 and round down to the nearest integer. Put the content between this number of spaces, and the rest.
        """
        sp_before = int(spaces/2) * " "
        sp_after = (spaces - sp_before) * " "
        column = sp_before + cont + sp_after

    return column

def DisplayCsvCols(FileData, col_spec):
    """Uses FormatCsvCol to display the data in FileData.
    Requires col_spec.
    """
     #Get the column names from the 1st row.
    columns = GetCsvCols(FileData)

    #Step through FileData in row ID order.
    for row_id in range(1,len(FileData)+1):
        disp_row = ""

        #Step through each column in a row
        for col_num in range(0,len(columns)):
            disp_row = disp_row + FormatCsvCol(col_spec[columns[col_num]][0],col_spec[columns[col_num]][1],FileData[row_id][col_num])

        print disp_row

def OutputCsv(FileData, output_spec):
    """Uses FormatCsvCol to output the data in FileData to a space-delimited text file. output_spec = a dictionary providing column output specifications.
    """
    import os

    # Edit path and name as needed.
    output_path = "D:\Dropbox\projects\CsvChamp"

    file_name = "output.txt"

	#Get the column names from the 1st row.
    columns = GetCsvCols(FileData)

    with open(os.path.join(output_path,file_name),"w") as OutputFile:

        #Step through FileData in row ID order.
            for row_id in range(1,len(FileData)+1):
                    disp_row = ""

                    #Step through each column in a row
                    for col_num in range(0,len(columns)):
                            disp_row = disp_row + FormatCsvCol(col_spec[columns[col_num]][0],col_spec[columns[col_num]][1],FileData[row_id][col_num])

                    disp_row = disp_row + "\n"

                    OutputFile.writelines(disp_row)

"""Sample CsvChamp usage. We need to grab data from a messy, mean, ugly CSV file and export it as a nice, neat, clean, friendly text file.
"""

#Prompt the user to select a file.
file = GetCsvData(CsvFilePicker())

#Determine column width and alignment for output.
col_spec = CreateColSpec(file)

#Create the output file.
OutputCsv(file, col_spec)

The Quest for the Silver Mystery Table

As I have shared previously, I have worked for many years with Microsoft Access, using it both at home and at work for tracking just about everything in tables linked together in queries and forms. At home I was able to put together a (sort of) application for tracking our finances and inventory. The core of this app is a form that displays transactions and includes text and combo boxes for entering criteria for sorting, filtering and totaling, like this:

MS Access Transactions

I can view transactions by bank account, expense category, month, year, and so on. I can also enter transactions using this same form. Such functionality is a no-brainer in Microsoft Access. Behind all the boxes are string variables that are assembled together to create a SQL statement that gets me what I want to see from the data. Sorting and filtering lists of stuff like is 90% of what do in Access both at home and at work.

My primary goal in Python coding is to reproduce something like the above, using Python code, a GUI tool such as Tkinter and a SQLite database. If I could discover the secret of doing this, the possibilities for data-centric app building would become endless for me. To use a truly Pythonic analogy, this is my own, personal Holy Grail of coding. I only hope my quest meets with better success than poor King Arthur, Sir Robin and the gang

So off I went into Google-land, almost hearing the cloppety-clop of those coconuts following close behind me, and after many dead ends, false starts and Frenchmen farting in my general direction, I beheld this:
The Silver Mystery Table

Impressive!

But..how do I get it to work?

First thing I noticed: It appears to require an add-on called “pmw,” which I later found out stands for Python Mega Widgets. Sounds like an appropriately awesome name for an appropriately awesome add on, yes?

(Attention gamers: believe it or not, as I was writing this I accidentally typed “pwn” instead of “pmw!” As in, “I hope I don’t get pwnd by pmw!”)

Pmw seemed to install easily with the good old command line: python setup.py install. This time I didn’t have to summon ez_install or pip. I’m starting to think of those two as my own personal goons, ready to beat up on uncooperative toolkits who refuse to install when I ask them politely with setup.py. “Oh, so you don’t want to install, eh? We have ways of MAKING you install….”

I have learned that whenever I install a new toolkit like this, I can immediately find out if it installed correctly, right from a Python command prompt, like so:

>>> import Pmw
>>>

Success! Ah, the delightful silence of a “>>>” prompt, without lines of angry red text! Now, to copy, paste, and run. Oops..no go…got an error: ‘Table’ is not defined.” Well, obviously something is missing here, obviously. Better Google the exact name of the table thingy from the above link: “PmwContribD.” OK, now I see the ‘Table’ is part of another set of widgets called “PmwContribD”, that’s based on the set of widgets (“pmw”) that I just installed. No problem. Found it: http://ostatic.com/pmwcontribd. Thankfully, another hassle-free install. Next, I need to make sure I include the correct “import” statements in my code. According to the PmwContribD documentation for the ‘Table’ widget, they should be these. So I add these statements, and past in the code from the first link above:

from Tkinter import *
import Canvas
from CanvasGroup import CanvasGroup
import Pmw
from ShadowBox import ShadowBox
import UserList
import math
import string

self.table = Table(self.interior(), cellpadding=pad, xfreeze=1)
self.table.addcolumn( RowLabelColumn(
    title='',
    datasource=TestDataSource(),
    width=50,
    rowheight=rowheight,
    cellalignment='se',
    cellpadding=pad,
    ))
for i in range(10):
    self.table.addcolumn( Column(
        title='Col %d' % i,
        datasource=TestDataSource(base=10**i),
        #width=50 + (10 * i),
        width=0,
        rowheight=rowheight,
        cellalignment='se',
        cellpadding=pad,
        ) )
    self.table.pack(fill=Tkinter.BOTH, expand=Tkinter.YES)

So I ran it and…still getting the ‘Table’ is not defined’ error. I am now officially stuck. In looking over the code again, I suspect the problem is in the “self” statements. As I recall what I learned about classes, now that I think of it, shouldn’t there be a “class” definition before a “self?” Such as “class MyTable, or something like that? Perhaps a Python professional is out there laughing his head off at this. Nevertheless, for me, this object remains, for the time being, a mystery. So from know on I will refer to it as “The Silver Mystery Table.” Yes, I know it’s actually grayish, but silver sounds a lot cooler, like a precious, elusive treasure. Or a sword, that has cut me. But that’s OK, because I will be back. I will hone my Tkinter skills and live to fight another day!

Besides, it’s only a flesh wound.