I do not look favorably upon a certain brand of canned, processed meat product

As of today, I am no longer allowing comments or user registrations on this blog.

The image below sums up the reasons.  Out of the hundreds of “comments” (in quotes because they don’t even deserve to be called such) I have received, I can count on the fingers of one hand the number that are from actual readers of this blog who have something to contribute.  The same goes for “user” registrations.

I wish I could devote all my working hours to my Python learning and this blog, and if that were the case, perhaps I would think differently.  However, I have a full-time job that is not Python related, a wife, and numerous other family and social commitments that are important to me.  I simply don’t wish to devote the time to moderating “canned lunch meat”, and I certainly don’t want to spend even a penny on software to do it for me.

For those real humans out there who have been following this blog, especially those who have commented, thank you for your support and please accept my apologies. Feel free to contact me through Google Plus or Twitter if you have any feedback or suggestions regarding the blog.

I_d8dd4a_105979

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.

The twisting, turning path to PYTHONPATH

Wow. The learning process sure is messy, and at times embarrassing.

Here’s how it all started:

I have a folder called ‘tools’ which contains various modules I wrote that I want to be accessible for import to all my Python projects. One of these modules is called ‘DbSession’ — it contains a class for handling various Sqlite database tasks. Each Python project is in its own folder, with the ‘tools’ folder as a ‘sibling’ under the same parent directory, as in this example:

C:\MyPath\Dropbox\projects\tools
C:\MyPath\Dropbox\projects\ProjectA
C:\MyPath\Dropbox\projects\ProjectB

I use Dropbox to sync my projects between two computers, one running Windows XP and one running Windows 7. The path to the Dropbox folder is different on each PC. No matter which PC I’m using, I want the modules in ‘tools’ to be accessible for importing to all my projects.

As previously posted, my first solution was to do a sys.path.append() in my code, but afterward I learned this was not the best solution because it would involve changing my code every time I switched computers or re-arranged directories. I then decided I’d like to be able to set the PATH in each PC appropriately so the imports will work on both without changing my code. After much Googling and sifting through what seemed to be a multitude of possible answers, I found what I *thought* was the solution, but upon further testing, discovered I was mistaken. I thought making the imports work in my project directories was just a matter of adding my ‘tools’ directory to the PATH variable. Here’s what I did (Windows XP machine):

Added ‘tools’ directory to PATH variable and confirmed it is set correctly by typing “PATH” on a command line:

C:\>PATH
PATH=C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32
\WindowsPowerShell\v1.0\;C:\Python33;C:\Python33\Lib\site-packages;C:\Documents
and Settings\Rob\My Documents\Dropbox\projects\tools

The ‘tools’ directory contains a test ‘Hello World’ program, ‘hello.py’, which I can run from the C:\ directory just by typing the file name. This proves the PATH variable is set correctly to ‘tools’:

C:\hello.py
Hello, world!

..although if I try to run ‘hello.py’ from Python, Python can’t find it!

C:\python hello.py
python: can’t open file ‘hello.py’: [Errno 2] No such file or directory

Here’s my attempt at importing from the module ‘DbSession.py’, which resides in ‘tools.’ First, I ran Python from the C:\ directory. It can’t find the ‘DbSession’ module in ‘tools’, even though the above test with ‘hello.py’ at least showed the ‘tools’ directory was correctly set in PATH. I tried putting a blank ‘__init__.py’ file in ‘tools’, but it still didn’t work:

C:\python
Python 3.3.4 (v3.3.4:7ff62415e426, Feb 10 2014, 18:12:08) [MSC v.1600 32 bit (In
tel)] on win32
Type “help”, “copyright”, “credits” or “license” for more information.
>>>from DbSession import DbSession
Traceback (most recent call last):
File ““, line 1, in
ImportError: No module named ‘DbSession’

When I ran Python from the ‘tools’ directory the import worked fine, proving there’s no problem with the module itself:

c:\Documents and Settings\Rob\My Documents\Dropbox\projects\tools\python
Python 3.3.4 (v3.3.4:7ff62415e426, Feb 10 2014, 18:12:08) [MSC v.1600 32 bit (In
tel)] on win32
Type “help”, “copyright”, “credits” or “license” for more information.
>>>from DbSession import DbSession
>>>

So what’s up? I KNOW I set the PATH right…how come Python can’t import from the ‘tools’ directory into a project directory, when I can run the ‘hello.py’ code, which is also in ‘tools’, from any directory I want? This seemed so insane it tempted me to go back to using sys.path.append().

Until I ran across this one little nugget, in a 5-year-old article, on a site I’d never heard of:

“However, unlike PATH (which tells the operating system which directories to look for executable files in), PYTHONPATH is used by the Python interpreter to find out where to look for modules to import.”

BINGO! EUREKA! HALLELUJAH!

So, I need TWO SEPARATE PATH VARIABLES…the PATH variable to help Python find itself and its various packages:

path

..and the PYTHONPATH variable to help it find modules for importing!

pythonpath

I don’t know if it’s my misfiring brain, but somehow in all my recent travels, this simple truth eluded me. So, now my jolly good mates at Stereoplex have been blessed with a mention and a backlink. Cheers!

From knowledge import improvement

2014-04-02 UPDATE: The only reason ‘from tools.DbSession..’ (see below) worked was because ‘tools’ was a subdirectory of the directory I was in (‘projects’). Had NOTHING do to with the PATH variable. I FINALLY found the solution!

In my post about importing Python modules, I wrote that I preferred setting the PATH variable in my code instead of changing my computer’s PATH variable. With so much of my code being new, that did not seem like much of a burden. It was, however, pointed out to me that it could become a major hassle down the road, if I have tons of scripts set to a certain path using sys.path.append(). They would all need to be updated if used on a computer with a different directory structure, or even if I just moved my code around on my current computer. I had previously tried working with the PATH setting without success, so for the time being it was easier to specify it in code.

Now, I am happy to report, I have discovered the better way.

I have a class called ‘DbSession’ in the module ‘DbSession.py.’ I keep it, and other modules that I want to share among all my projects, in a directory called ‘tools.’ Project-specific code is kept in project folders which reside in the same parent directory as does ‘tools.’

Here’s how I figured it out how to make ‘DbSession’ available to all:

First,  I added the ‘tools’ directory to my system PATH variable and confirmed it was correct. Here is is displayed in a command prompt window, including my ‘tools’ directory:

echo %PATH%
C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;
C:\Python33;C:\Python33\Lib\site-packages;
C:\Documents and Settings\Rob\My Documents\Dropbox\projects\tools;

I run Python from the ‘tools’ directory and confirm the import works, without errors (that is, it does nothing but put me back to the Python prompt), from there:

>>>from DbSession import DbSession
>>>

I run Python from a different directory (‘projects’) and retry the import. It chokes:

>>>from DbSession import DbSession
Traceback (most recent call last):
File “”, line 1, in
ImportError: No module named ‘DbSession’

Huh? I thought I had the ‘tools’ directory in my PATH variable, so shouldn’t Python be able to see what’s in it?

Behold, the kicker:

>>>from tools.DbSession import DbSession
>>>

And there we have it! Apparently, when a directory for modules is specified in PATH, the name of that directory must also be specified in the import statement when attempting to access those modules.

Now, at last, I’ve found the right path to the right use of PATH.