Thiiink: Ideas, Imagination, and Innovation in GIS

A new year and some new eats, trying to cut out those heavy snacks that make you feel bloated — remarkably similar feeling as working with some enterprise level databases that are poorly tuned…At one point in my career it was uber important for to be on top of all the latest and greatest technology available; that was in the days when web-time was being defined and it certainly made for some interesting reading, but the relevance was soon lost — the world of business does not move at the speed of technology change and rightly so, change is expensive (but can also be a good investment when properly handled).

Even though we have fully wired environment and hooked-in computing environment that allows us to work remotely, the convenience of this is soon lost when you spend a lot of time sitting on planes or are unable to get a reliable signal that’s strong enough to handle the data throughput.  For example, working a pet project a few weeks back while on the road saw that nearly 95% of the time spent waiting was for data transmission across the skinny wire 3000km away.  

This got me looking for an alternative…something that would be lightweight, easy to code against, worked with Python, and for which the code was easily transferable (i.e. SQL DML and DDL are very rigid to ANSI-SQL) by only needing to switch a connect string.

First path was to look at an ORM, SQLAlchemy is not a database engine but it does offer the convenience of providing a layer of abstraction between your code and the database.  Although SQLAlchemy is an awesome package, it was overkill for what was really needed.  Next thing, was looking at SQLite — wow, what a slick database engine and does it work with Python, for sure, built-in for 2.5 and available for 2.4.  Granted SQLite does not support all the capabilities of Oracle or SQL Server, it is remarkable close in its syntax and capabilities and far better than MS Access with respect to syntax and has a very tiny memory footprint.