For a small hack project, I built out a small web app with a Postgresql DB on the backend, which is a fairly interesting route to take as I haven’t really touched meaningful DB setup with it before, as my previous Java project used HBase, which is basically free in Spring and outside of that it was wither Mongo or Google HRD.
The data used was a listing of cities with population greater than 1000, which can be located on this website describing the data sets.
Since I was going to keep it small, and I had some vague notion that Flask was a decent enough, I did some research around what normal people use as an ORM. You pretty much run into SQLAlchemy left and right, so I figured, why not go with it?
One of the interesting things about the project itself was playing with geo data. I had done that previously with the school locator project, but this was the first time I’d be actually searching within the data as a DB query. Quickly skipping around, I started on Postgres’s GiST and ended up at GeoAlchemy2, which gave me a reasonable interface for working with geo data. Since PostGIS uses great circle distance measurements for its measurements, I didn’t have to do any interesting gymnastics in order to get the k-closest points for a given location.
As a sanity check for interacting with the API, I used flask-restful-swagger, which has a fairly
clean decorator-based method for describing route which I enjoy, but I spent two days thinking it
was broken because it hides the entire API by default when you navigate to the UI, which is wildly
unintuitive. C’est la vie I suppose.
All in all it’s pretty cute. I made a Flask app, which is about a nice as I expected, and the tools around working with Postgresql in Python are solid enough. The one downside is I’m not totally sure if I’m leaking DB connections as part of handling the requests, so I’ll need to dig into that a little deeper to see if that’s the case. Otherwise the next thing to do would be to play a little bit more with the full-text search capabilities to see how it behaves for relatively simple searches that don’t involve sexy term weighting.