Overwhelmed by Practicality


Once upon a time, I took a glance at web development and found it was “killer” — in more ways than one.  I then threw my heart and soul into….reinventing the wheel, basically.  Then time, age, distractions and responsibilities crept up on me. I have had to make some decisions about how to make the best use of my time: how much am I able and willing to devote to learning new skills, given my current responsibilities and priorities?

How much sense does it make to create an application that duplicates what not one, but two existing applications (Access and Base) already can do for me perfectly adequately?  Such an effort could be justified if it helped me learn skills that could be useful, and even profitable, in the future.   How useful would the ability to create desktop-only software actually be, in a 21st century world that is rapidly turning to the web as its platform of choice for ALL computing, not just browsing, social networking and online shopping?  Answer: Not very.

Now, two years almost to the day after I took the HTML course at Codecademy, I am embarking on a new quest: learn web development with Python.  The tools are right in front of me:  good old RealPython has not one, but two huge ebooks on web development, that have been gathering digital dust on my hard drive.   My goal is to work through it slowly, so that I understand the concepts.  I have already made progress in grasping virtualenv and command-line Git.  At the moment my brain is tied in knots by Python decorators, but then again, it was once tied in knots by Python importing, so I’m confident I’ll wrestle through it.  At least I was able to complete the Flask Blog exercise.  I even typed out all the code by hand instead of copying and pasting, just like Michael told me to!

I would like to eventually create a web-based finance application, just like I did in Access and Base.  That will take time, however, and in the meantime, I still need to pay bills and track expenses, so Base will do, for now. I would say one of the most useful accomplishments during this blog’s lifetime was to get my financial data from my Access *.accdb file into SQLite, making the live data 100% portable.

As I (slowly, I expect) progress through the two RealPython web development courses, feel free to stop by GitHub to check on my progress!

Oh, and one more thing I’ve learned since my last post: Cities: Skylines is an even cooler game than SimCity4.


Making Dates Behave

[UPDATE: 2016-07-05: Kudos to me for re-inventing the wheel..again!  Someone pointed out to me that there is already a module that does this, and it does it much better than me. So, ignore all the code below, and go get dateutil instead! ]

My financial management SQLite database, for which I hope to eventually design a Python/Qt GUI front end, relies on data downloaded from various financial institutions. My current focus is developing a Python application to automatically import and correctly format this data so that it can be appended to my database.

A huge annoyance in this process is that different institutions export dates in different formats. For example, I deal regularly with PayPal, Ebay and Amazon. PayPal’s downloaded transactions have their dates like “7/29/2015.” Ebay’s are “Jul-29-15” (why on earth is ANYONE still using two digit years???) Amazon’s are “Dec 9, 2015.” Appending dates to my database, however, requires them to be in the increasingly standard, reasonable, logical format, “2015-12-17”.

Python to the rescue! Here is my solution: a function called “date_fixer.” Give it a date in any of the above three formats, and it spits out the date as “YYYY-MM-DD”. Detailed documentation and explanation are in the comments. I’m sure it’s not coded as efficiently as it could be, but at this point, I only care that it works! Hope others find it useful as well. Cheers!

def date_fixer(datestring):
    Converts a date, 'datestring', to YYYY-MM-DD (ISO 8601) format.
    'datestring' must be in month, day, year order. The month, day and year
    can be separated by a space, "/" or "-".  The month can be either a number 
    from 1 to 12, the full month name or the firt 3 letters of the month name.   
    Day must be in the range 1 - 31. Year can be either two or four digits. 
    Examples: "12/17/2015", "December 17, 2015", "12-17-15", "12-17-2015",
    "1-2-12", "Dec 17 15", "Dec 17, 2015".  

    from datetime import date
    # dict for looking up month number by abbreviation
    months = {"JAN":1,"FEB":2,"MAR":3,"APR":4,"MAY":5,"JUN":6,
        # determine which character separates m, d, y
        if datestring.find(" ") != -1:
            delim = " "

        elif datestring.find("/") != -1:
            delim = "/"
        elif datestring.find("-") != -1:
            delim = "-"
            return "Error: '{}' is not a valid date string.".format(datestring)

        # Get the position of the first occurance of the delimiter    
        delim1 = datestring.find(delim)
        delim1 = datestring.find(delim)

        # Get the position of the 2nd occurance of the delimiter
        delim2 = datestring[delim1+1:].find(delim)
        delim2 = delim1 + delim2 + 1

        # the month is the segment of datestring that is from 
        # 0 to the 1st delimiter occurance.
        month = datestring[:delim1]

        # month is all or part of the month name. 
        if not month.isnumeric():
            # get the 1st three letters of 'month' & look up in 'months' dict.
            month_abr = month[0:3]
                # look up month abbreviation in months dict
                month = months[month_abr.upper()]
            except KeyError:
                return "Error: '{}' is not a valid month.".format(month)
        month = int(month)
        if month > 12 or month < 1:
            return "Error: '{}' is not a valid month.".format(str(month))
        # the day is the segment between the two occurances of the delimiter.
        day = datestring[delim1+1:delim2]

        # strip out the comma if there's one after the day.
        if day[len(day)-1] == ",":
            day = day[:len(day)-1]

        day = int(day)
        if day > 31:
            return "Error: '{}' is not a valid day.".format(str(day))
        year = ""
        # check for consective numerals in the segment after the 2nd delimiter.
        for char in range(delim2+1,len(datestring)):
            # adds each consective numeral to 'year.'
            # stops when a non-numeric character is encountred. 
            if datestring[char].isnumeric:  
                year = year + datestring[char]
        if int(year) < 100:  # year is 2 digits
            if int(year) <50:
                year = int(year) + 2000  # assume year is in 21st century
                year = int(year) + 1900 # assume year is in 20th century
        year = int(year)
        return date(year, month, day).isoformat()
    except ValueError:
        return ("Error: Unable to process datestring '{}'.".format(datestring))

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).


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!

Off “Base”

Several years ago I took a look at Base, the database component of OpenOffice and now, LibreOffice. It seemed extremely rudimentary compared to MS Access. For some reason, it came into my head to check it out again recently. Base seems to have come a long way since my first encounter. I noted that it has the ability to construct complicated SQL queries graphically, and build forms that include drop-down boxes and lookups. It meets my main qualifications for writing essential apps such as my current finance management project: it’s open source and cross platform. For a few days, it appeared so suited to what I’m trying to accomplish that it seemed this blog’s title would have to change to “My LibreOffice Base Adventure.” But it wasn’t meant to be. The main reason is that as I continued to experiment with Base, it soon became apparent that, in order to do what I want, I would need to go beyond using only tables, forms and queries, and learn to code in LibreOffice Basic — LibreOffice’s equivalent to Microsoft’s Visual Basic that allows more sophisticated programming of the behavior of Base’s database objects. Why, I asked myself, after coming as far as I have with Python, then PySide, would I want to set all that aside to learn yet ANOTHER language? To be honest, what I really needed to do was get back on track with what I’ve already built, and take it to the next level. Amazing how the thrill of learning something new can sometimes distract from persevering with what I already have began learning. So now, back to work.  I found this PySide tutorial this past week and hope to use it to brush up on what I’ve already learned, and then continue forward.

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:

…into this:


..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.



Unbounded by a binding

Once upon a time, fresh out of Code Academy, I Googled for a way to design a GUI in Python. I came across Qt Designer. Cool! It reminded me of Microsoft Access and Visual Basic. I could lay out windows, buttons, boxes and so on just the way I wanted, set their properties and add some code. I began learning about “slots” and “signals”, Qt’s system of connecting what a widget is supposed to do to what the user does to it. But before long, it hit me:

The code I was using to create this wonderful GUI was not Python; it was Qt.

Somewhere along the line I’d read that Qt could be used to make a GUI for use in Python, yet Qt and Python are completely separate languages, so how, I asked myself, could I possibly use a GUI created in Qt, in Python? It sounded about as likely as running a Mac OS application on my Android phone. For whatever reason at that time, I couldn’t find a clear answer to this, and other options such as wxPython and Tkinter drifted into my view, so Qt was quickly forgotten. wxPython looked cool but when I decided it would be best for me as a newbie to learn in Python 3, wxPython quickly dropped off my radar. I noted Tkinter is included with Python and has been around forever, so it seemed to be the best option of the three. Unfortunately, I found Tkinter’s documentation to be fragmented and in some cases, ancient. Trying to produce a simple table object gradually wore me down over a period of months, which taught me the hard way that Tkinter is apparently just fine for GUIs with simple buttons, text boxes and lists, but nearly useless for data objects any more complex than a listbox. The one or two times I’ve run across table-like objects for Tkinter, I found they were not compatible with Python 3.

Luckily, I was only one more frustrated Google search away from discovering an amazing concept that brings diverse languages together in harmony, letting them play ball joyously with one another and get along like BFFs:


I eventually discovered Pyside, which re-introduced me to its friend Qt, and now Qt and I are buddies again! The secret? Lay out, code and design the GUI of my dreams in Qt, and when done, it’s only one magical command-line incantation away form being transformed into a Python class object, ready to be called upon any time! Pyside changed the direction of my DataQ project and injected it, and my Python learning adventure, with new life. Before long I was able to finally create a real GUI widget for my app and even able to my data into it.

Next step: wrapping my brain around model-view programming.


My original mockup idea..


So close!

QTableView knocks Tkinter off the ‘table’



Have I got a deal for you! How about a GUI widget with over double the functionality, with less than half the code? Here it is:

import sys
from PySide.QtCore import *
from PySide.QtGui import *
from PySide.QtSql import *

# My function for assembling SQL queries for my 'transactions' table. I'll share the code another time.
from dq_sql import *

app = QApplication(sys.argv)

class DqTableForm(QWidget):
    def __init__(self, parent = None):


        db = QSqlDatabase.addDatabase("QSQLITE")

        self.DqModel = QSqlQueryModel()

# My TransQry function takes parameters and returns a SQL query using the parameters as criteria for filtering my 'transactions' table.      

self.DqModel.setQuery(TransQry("2014-03-01","2014-03-31","DESC","","","202",""), db)

        self.DqTable = QTableView()

        grid = QGridLayout()

form = DqTableForm()

See? Less than 45 lines. Sure beats the nearly 100-line, marginally useful alternative, which I have been sweating and struggling under for months. Lesson learned: there’s a very fuzzy boundary between challenging myself toward productive learning, and finding the most efficient solution to a need. In this instance, given the complex data filtering and sorting I’m trying to accomplish, Qt’s QTableView soundly beats anything Tkinter is even remotely capable of without astronomically more code. Shout out to Bo’s Qt GUI tutorials for introducing me to Qt, the documentation for the PySide Python Qt bindings for helping me figure out QTableView, and to my friend and constant companion Google, for helping me evolve.

Revolution! (tossing out Tkinter)


Wow, a real, live Python visual app designer!

In the last few weeks I realized I have a long way to go to figure out how the MultiListbox code works…it seems like even the simple function of selecting a line of data requires torturous hand-coding. Almost two months after setting out on my quest for a Python data management application, I am still tripping and stumbling over a single GUI element. And yet all it does is display columns of data, something Microsoft Access does in its sleep, and the MultiListbox isn’t even editable. I’ve become distracted and discouraged. There are functions in the MLB class that I’m not even close to understanding, and there is no detailed explanation of the code anywhere. Lately I’ve thinking, there’s got to be a better way. There’s got to be a way where I can focus my coding on what I want the GUI widgets to DO, instead of struggling just to bring them into functional existence.

So, this afternoon, I once again Googled “Python GUI builder” or something like this and found this series of videos on building Python GUI applications with the Qt framework. https://www.youtube.com/user/Deusdies2.

The first series of videos walk the user through how to download and install a Python interface to the Qt development framework, in this case PySide. The author of this video series uses PyCharm on Windows 7, so his desktop and mine are nearly identical, meaning it couldn’t be easier to follow along with him.  The first few videos walk through the creation of simple Qt-based GUI applications such as a calculator. Eventually it moves on to QtDesigner, which, wonder of wonders, is a “drag and drop” GUI designer very much like Microsoft Access and Visual Basic. Where Tkinter requires 100% hand-coding of just about every step of a widget’s function, even PySide coding by itself without the graphical Qt Designer seems much simpler and less labor intensive.

Yes, I have spent many months on Tkinter. Yes, it seemed like I was on the right track when I discovered the MultiListbox. I enjoyed, for a time, the challenge of trying to figure it out. But somewhere along the line, it wasn’t fun anymore. It was a chore. This made it all too easy to become distracted with other projects. That was the signal that something was wrong. I like a challenge and I have loved learning Python, but when it’s no longer enjoyable, it’s no longer worth doing.  It’s a little intimidating to think of learning a completely new GUI builder.  However once I saw that drag and drop designer, that shimmering promise of instantly knowing my app will look the way I want it to without all the trial and error, and each item I drop into my app already knowing how to do what it’s supposed to and all I have to to is give it parameters,  I thought, “This could make Python fun again.” So off I go on my next, unexpected Python path. I’m going spend the next phase of my Python adventure getting as much as I can out of Mr. Milanovic’s videos, and of course, sharing the results.

A slow but sure learning process

It would be easy to grab a chunk of someone else’s code, modify it only as necessary, and through a lot of trial and error get it to do what I want.  That is what I originally set out to do when I discovered the MultiListbox custom widget in this project. Using this method I might be able to patch together something functional, without ever learning in depth about how the code actually works. This course of action is a continual temptation to my impatient self, especially now as I am grappling with the most complex GUI object in my DataQ application.

But that is not my goal. I don’t just want to learn how to use Python for specific tasks; I want to learn how Python WORKS. The MultiListbox fascinated me because it’s not just any old Tkinter widget; it’s a custom widget made out of other widgets — in this case, a series of Listboxes put together to display rows of data. In examining the MultiLisbox code, I realized I knew very little how even a single Listbox works, let alone a group of them squashed together and synchronized. I therefore decided to learn about and experiment with Listboxes, learning how they work from the ground up. I have found it hard to stay focused. Sometimes I get away from Python completely for days at time because my brain just feels fried by it.

Despite the challenges, I’m happy to report that I’m making progress. I’ve even started designing my very own MultiListbox class (mostly) without even looking at the original code! My version makes each individual column in the MultiListbox its own class, and I use the .grid() geometry manager instead of the often unpredictable .pack(). Here is the ‘rough draft’: Rob’s MultiListbox.