Blog Entries all / by tag / by year / popular

Python tool for templating and managing dotfiles

I always told myself I would never release this code. For some time I considered the appearance of my desktop environment a form of creative expression, and by putting the tooling I wrote into the hands of others, I might dilute, somehow, the quality of what I had set out to create. As embarrassing as it is to admit this - after all, the purpose of this work was to create a desktop environment that was functional and aesthetically pleasing, and had nothing to do with anyone else's desktop - I hope that by stating my thought processes I can convey how much I had invested in this project.

In this post I'll describe the tooling I wrote to automate the process of adjusting the appearance of the various software I use on a daily basis. Below is a screenshot of my desktop at the time of writing, with a couple applications running:



Multi-threaded SQLite without the OperationalErrors

Sqlite Logo

SQLite's write lock and pysqlite's clunky transaction state-machine are a toxic combination for multi-threaded applications. Unless you are very diligent about keeping your write transactions as short as possible, you can easily wind up with one thread accidentally holding a write transaction open for an unnecessarily long time. Threads that are waiting to write will then have a much greater likelihood of timing out while waiting for the lock, giving the illusion of poor performance.

In this post I'd like to share a very effective technique for performing writes to a SQLite database from multiple threads.


Optimistic locking in Peewee ORM


In this post I'll share a simple code snippet you can use to perform optimistic locking when updating model instances. I've intentionally avoided providing an implementation for this in peewee, because I don't believe it will be easy to find a one-size-fits-all approach to versioning and conflict resolution. I've updated the documentation to include the sample implementation provided here, however.


Monospace Font Favorites

For the past six months or so, I've been experimenting with a variety of monospace fonts in a quest to find the perfect coding font. While I haven't found a clear winner, I have found a dozen nice-looking fonts and learned a lot about typefaces in general. I've also learned quite a bit about font rendering on Linux, which I hope to summarize in a separate post soon.

In this post I'd like to share some screenshots (or "swatches") of my favorite fonts.



Suffering for fashion: a glimpse into my Linux theming toolchain


My desktop at the time of writing.


Here it is a couple months later.

It's been over 2 years since I wrote about the tooling I use to theme my desktop, so I thought I'd post about my current scripts...


"For Humans" makes me cringe

for chodes

When Kenneth Reitz created the requests library, the Python community rushed to embrace the project, as it provided (finally) a clean, sane API for making HTTP requests. He subtitled his project "Python HTTP Requests for Humans", referring, I suppose, to the fact that his API provided developer-friendly APIs. If naming things "for humans" had stopped there, that would have been fine with me, but instead there's been a steady stream of new projects describing themselves as being "For Humans" and I have issues with that.


Measuring Nginx Cache Performance using Lua and Redis


Shortly after launching my Nginx-based cache + thumbnailing web-service, I realized I had no visibility into the performance of the service. I was curious what my hit-ratios were like, how much time was spent during a cache-miss, basic stuff like that. Nginx has monitoring tools, but it looks like they're only available to people who pay for Nginx Plus, so I decided to see if I could roll my own. In this post, I'll describe how I used Lua, cosockets, and Redis to extract real-time metrics from my thumbnail service.


Nginx: a caching, thumbnailing, reverse proxying image server?


A month or two ago, I decided to remove Varnish from my site and replace it with Nginx's built-in caching system. I was already using Nginx to proxy to my Python sites, so getting rid of Varnish meant one less thing to fiddle with. I spent a few days reading up on how to configure Nginx's cache and overhauling the various config files for my Python sites (so much for saving time). In the course of my reading I bookmarked a number of interesting Nginx modules to return to, among them the Image Filter module.


Examples of using Walrus, a lightweight Redis Toolkit


walrus is my go-to toolkit for working with Redis in Python, and hopefully this post will convince you that it can be your go-to as well. I've tried to include lots of high-level Python APIs built on Redis primitives and the result is quite a lot of functionality. In this post I'll take you on a tour of the library and show examples of how it might be useful in your next project.


Five reasons you should use SQLite in 2016

Sqlite Logo

If you haven't heard, SQLite is an amazing database capable of doing real work in real production environments. In this post, I'll outline 5 reasons why I think you should use SQLite in 2016.


Announcing sophy: fast Python bindings for Sophia Database


Sophia is a powerful key/value database with loads of features packed into a simple C API. In order to use this database in some upcoming projects I've got planned, I decided to write some Python bindings and the result is sophy. In this post, I'll describe the features of Sophia database, and then show example code using sophy, the Python wrapper.

Here is an overview of the features of the Sophia database:

  • Append-only MVCC database
  • ACID transactions
  • Consistent cursors
  • Compression
  • Ordered key/value store
  • Range searches
  • Prefix searches


Updated instructions for compiling BerkeleyDB with SQLite for use with Python


About three years ago I posted some instructions for building the Python SQLite driver for use with BerkeleyDB. While those instructions still work, they have the unfortunate consequence of stomping on any other SQLite builds you've installed in /usr/local. I haven't been able to build pysqlite with BerkeleyDB compiled in, because the source amalgamation generated by BerkeleyDB is invalid. So that leaves us with dynamically linking, and that requires that we use the BerkeleyDB libsqlite, which is exactly what the previous post described.

In this post I'll describe a better approach. Instead of building a modified version of libsqlite3, we'll modify pysqlite to use the BerkeleyDB libdb_sql library.


SQLite Spellfix Extension and Python

The SQLite source tree is full of wonders. There is the the lemon parser generator, a btree implementation (well, kind of not surprising), multiple search engines, a json library, and more. It looks like Dr. Hipp is also experimenting with integrating the LSM key/value store from SQLite4 as a standalone virtual table.

I've written about the json and full-text search extensions, the lsm key/value store, and the transitive closure extension (useful when querying hierarchical data). In this post I'll be covering another interesting extension, the spellfix1 extension (documentation).


SQLite Table-Valued Functions with Python

One of the benefits of running an embedded database like SQLite is that you can configure SQLite to call into your application's code. SQLite provides APIs that allow you to create your own scalar functions, aggregate functions, collations, and even your own virtual tables. In this post I'll describe how I used the virtual table APIs to expose a nice API for creating table-valued (or, multi-value) functions in Python. The project is called sqlite-vtfunc and is hosted on GitHub.


Advanced filtering and SQLite FTS5 with Scout search engine


I've been working on some new features for Scout and thought they might be worth a short blog post. The super-short version is that Scout now supports complex filtering on metadata, adding another layer of filtering besides the full-text search. Additionally, I've added support for SQLite FTS5, using it by default if it's available otherwise falling back to FTS4.


Using the SQLite JSON1 and FTS5 Extensions with Python

Back in September, word started getting around trendy programming circles about a new file that had appeared in the SQLite fossil repo named json1.c. I originally wrote up a post that contained some gross hacks in order to get pysqlite to compile and work with the new json1 extension. With the release of SQLite 3.9.0, those hacks are no longer necessary.

SQLite 3.9.0 is a fantastic release. In addition to the much anticipated json1 extension, there is a new version of the full-text search extension called fts5. fts5 improves performance of complex search queries and provides an out-of-the-box BM25 ranking implementation. You can also boost the significance of particular fields in the ranking. I suggest you check out the release notes for the full list of enhancements

This post will describe how to compile SQLite with support for json1 and fts5. We'll use the new SQLite library to compile a python driver so we can use the new features from python. Because I really like pysqlite and apsw, I've included instructions for building both of them. Finally, we'll use peewee ORM to run queries using the json1 and fts5 extensions.


My List of Python and SQLite Resources

Sqlite Logo

This post is going to be a greatest hits of my open-source libraries and blog posts concerning the use of SQLite with Python. I'll also share a list of some other neat SQLite projects that you may not have heard of before.


Using SQLite4's LSM Storage Engine as a Stand-alone NoSQL Database with Python


SQLite and Key/Value databases are two of my favorite topics to blog about. Today I get to write about both, because in this post I will be demonstrating a Python wrapper for SQLite4's log-structured merge-tree (LSM) key/value store.

I don't actively follow SQLite's releases, but the recent release of SQLite 3.8.11 drew quite a bit of attention as the release notes described massive performance improvements over 3.8.0. While reading the release notes I happened to see a blurb about a new, experimental full-text search extension (which I wrote about in a different post), and all this got me to wondering what was going on with SQLite4.

As I was reading about SQLite4, I saw that one of the design goals was to provide an interface for pluggable storage engines. At the time I'm writing this, SQLite4 has two built-in storage backends, one of which is an LSM key/value store. Over the past month or two I've been having fun with Cython, writing Python wrappers for the embedded key/value stores UnQLite and Vedis. I figured it would be cool to use Cython to write a Python interface for SQLite4's LSM storage engine.

After pulling down the SQLite4 source code and reading through the LSM header file (it's very small!), I started coding and the result is python-lsm-db (docs).

Read the rest of the post for examples of how to use the library.


Building the SQLite FTS5 Search Extension


SQLite contains a new, experimental version of the full-text search extension named FTS5. Reviewing the documentation for FTS5, I saw that it includes a couple cool enhancements, namely a more sophisticated query language, and built-in BM25 result ranking.

I decided to give it a try and thought I'd share my notes on compiling the extension in case anyone else is curious.


Introduction to the fast new UnQLite Python Bindings


About a year ago, I blogged about some Python bindings I wrote for the embedded NoSQL document store UnQLite. One year later I'm happy to announce that I've rewritten the library using Cython and operations are, in most cases, an order of magnitude faster.

This was my first real attempt at using Cython and the experience was just the right mix of challenging and rewarding. I bought the O'Reilly Cython Book which came in super handy, so if you're interested in getting started with Cython I recommend picking up a copy.

In this post I'll quickly touch on the features of UnQLite, then show you how to use the Python bindings. When you're done reading you should hopefully be ready to use UnQLite in your next Python project.


Connor Thomas Leifer



Alternative Redis-Like Databases with Python


Redis is one of the more unique NoSQL offerings to have become popular over the past five years. It seems that there is no limit to the use-cases one can find for Redis. It's fantastic as a cache, doubles as a task-queue, can provide fast type-ahead search, and much more. The idea that you can store data-structures instead of rows and columns, keys and values, or JSON documents strikes me as particularly innovative. A while back I released walrus, a collection of Python utilities I'd built to simplify some of these use-cases and provide Pythonic APIs for the data-structures Redis natively supports. If you're a Python developer you might check it out.

Recently I've learned about a few new Redis-like databases: Rlite, Vedis and LedisDB. Each of these projects offers a slightly different take on the data-structure server you find in Redis, so I thought that I'd take some time and see how they worked. In this post I'll share what I've learned, and also show you how to use these databases with Walrus, as I've added support for them in the latest 0.3.0 release.


Why I won't be switching to Disque

Disque's alpha release announcement generated some buzz on HackerNews. If you missed it, Disque is a distributed message broker from Salvatore Sanfilippo, the author of Redis.

In the Limitations section of the README, Salvatore has written:

Disque was designed a bit in astronaut mode, not triggered by an actual use case of mine, but more in response to what I was seeing people doing with Redis as a message queue and with other message queues.

This admission makes me wary of using Disque, even if it reaches a stable release, because of my own experience with similar projects I've created but never actually used. These projects are usually fun opportunities for learning, but when it comes to maintenance, my experience has shown me that they quickly become a burden. Usually the problem is masked by the fact that if I'm not using it usually nobody else is either, but in the rare case I do end up with users, then eventually those users are going to submit bug reports and feature requests.

For a problem as complex as a distribute message broker, I imagine that there are going to be a lot of bug reports, strange edge-cases, and feature requests to support exotic use-cases. I hope that, in addition to his work on Redis, Salvatore can find the time to support Disque!

The other reason I don't foresee using Disque is alluded to in the author's own comments. He observes that many people are using Redis as a message broker, and decides that maybe there is a need for a "Redis of messaging". I would say the opposite is true, and that instead of another message server, people want to use Redis!

Redis integrates very nicely into the stack for web-based projects. It can be used as a cache, for locking, as a primary data store, for write-heavy portions of the application, and yes, as a message broker.

Perhaps the reason people are using Redis as a message broker is because they don't want to use something else?


A Tour of Tagging Schemas: Many-to-many, Bitmaps and More


In this post I'll describe how to implement tagging with a relational database. What I mean by tagging are those little labels you see at the top of this blog post, which indicate how I've chosen to categorize the content. There are many ways to solve this problem, and I'll try to describe some of the more popular methods, as well as one unconventional approach using bitmaps. In each section I'll describe the database schema, try to list the benefits and drawbacks, and present example queries. I will use Peewee ORM for the example code, but hopefully these examples will easily translate to your tool-of-choice.


Meet Scout, a Search Server Powered by SQLite


In my continuing adventures with SQLite, I had the idea of writing a RESTful search server utilizing SQLite's full-text search extension. You might think of it as a poor man's ElasticSearch – a very, very poor man.

So what is this project? Well, the idea I had was that instead of building out separate search implementations for my various projects, I would build a single lightweight search service I could use everywhere. I really like SQLite (and have previously blogged about using SQLite's full-text search with Python), and the full-text search extension is quite good, so it didn't require much imagination to take the next leap and expose it as a web-service.

Read on for more details.


How to make a Flask blog in one hour or less


For fun, I thought I'd write a post describing how to build a blog using Flask, a Python web-framework. Building a blog seems like, along with writing a Twitter-clone, a quintessential experience when learning a new web framework. I remember when I was attending a five-day Django tutorial presented by Jacob Kaplan-Moss, one of my favorite projects we did was creating a blog. After setting up the core of the site, I spent a ton of time adding features and little tweaks here-and-there. My hope is that this post will give you the tools to build a blog, and that you have fun customizing the site and adding cool new features.

In this post we'll cover the basics to get a functional site, but leave lots of room for personalization and improvements so you can make it your own. The actual Python source code for the blog will be a very manageable 200 lines.

Who is this post for?

This post is intended for beginner to intermediate-level Python developers, or experienced developers looking to learn a bit more about Python and Flask. For the mother of all Flask tutorials, check out Miguel Grinberg's 18 part Flask mega-tutorial.

The spec

Here are the features:

  • Entries are formatted using markdown.
  • Entries support syntax highlighting, optionally using Github-style triple-backticks.
  • Automatic video / rich media embedding using OEmbed.
  • Very nice full-text search thanks to SQLite's FTS extension.
  • Pagination.
  • Draft posts.


Querying the top N objects per group with Peewee ORM


This post is a follow-up to my post about querying the top related item by group. In this post we'll go over ways to retrieve the top N related objects by group using the Peewee ORM. I've also presented the SQL and the underlying ideas behind the queries, so you can translate them to whatever ORM / query layer you are using.

Retrieving the top N per group is a pretty common task, for example:

  • Display my followers and their 10 most recent tweets.
  • In each of my inboxes, list the 5 most recent unread messages.
  • List the sections of the news site and the three latest stories in each.
  • List the five best sales in each department.

In this post we'll discuss the following types of solutions:

  • Solutions involving COUNT()
  • Solutions involving LIMIT
  • Window functions
  • Postgresql lateral joins


Querying the top item by group with peewee ORM


In this post I'd like to share some techniques for querying the top item by group using the Peewee ORM. For example,

  • List the most recent tweet by each of my followers.
  • List the highest severity open bug for each of my open source projects.
  • List the latest story in each section of a news site.

This is a common task, but one that can be a little tricky to implement in a single SQL query. To add a twist, we won't use window functions or other special SQL constructs, since they aren't supported by SQLite. If you're interested in finding the top N items per group, check out this follow-up post.


Managing Database Connections with Peewee


Recently I've been receiving a lot of questions about how to manage database connections with the peewee ORM. I thought I'd write a short post explaining the various ways peewee can help you manage your database connections, and outline some general best practices.


Naive Bayes Classifier using Python and Kyoto Cabinet


In this post I will describe how to build a simple naive bayes classifier with Python and the Kyoto Cabinet key/value database. I'll begin with a short description of how a probabilistic classifier works, then we will implement a simple classifier and put it to use by writing a spam detector. The training and test data will come from the Enron spam/ham corpora, which contains several thousand emails that have been pre-categorized as spam or ham.


Walrus: Lightweight Python utilities for working with Redis


A couple weekends ago I got it into my head that I would build a thin Python wrapper for working with Redis. Andy McCurdy's redis-py is a fantastic low-level client library with built-in support for connection-pooling and pipelining, but it does little more than provide an interface to Redis' built-in commands (and rightly so). I decided to build a project on top of redis-py that exposed pythonic containers for the Redis data-types. I went on to add a few extras, including a cache and a declarative model layer. The result is walrus.


Setting up ElasticSearch with Basic Auth and SSL for use with Python


I'm interested in learning to use ElasticSearch, so I thought I'd document how I set it up on my EC2 instance. Because I wanted to write code on my laptop, I needed to expose ElasticSearch over the public internet, which added a bit of extra complexity. Here is a rough outline of the process:

  • Install ElasticSearch on my EC2 instance.
  • Use supervisor to manage the ElasticSearch process.
  • Use Nginx to create a publicly-visible HTTP endpoint with:
    • Basic auth
    • Self-signed SSL cert
  • Install python locally and connect to my EC2 ElasticSearch server.


Pollywog: Fun with Regular Expressions


After writing about building a nicer regex API using operator overloading, I thought it would be fun to flesh out the code and release it as a library. I'm calling the result pollywog and you can find the code on GitHub.

To install pollywog, you can use pip or clone the GitHub repository:

$ pip install pollywog

In this post I'll show some examples of using pollywog.


Extending SQLite with Python


SQLite is an embedded database, which means that instead of running as a separate server process, the actual database engine resides within the application. This makes it possible for the database to call directly into the application when it would be beneficial to add some low-level, application-specific functionality. SQLite provides numerous hooks for inserting user code and callbacks, and, through virtual tables, it is even possible to construct a completely user-defined table. By extending the SQL language with Python, it is often possible to express things more elegantly than if we were to perform calculations after the fact.

In this post I'll describe how to extend SQLite with Python, adding functions and aggregates that will be callable directly from any SQL queries you execute. We'll wrap up by looking at SQLite's virtual table mechanism and seeing how to expose a SQL interface over external data sources.


Querying Tree Structures in SQLite using Python and the Transitive Closure Extension


I recently read a good write-up on tree structures in PostgreSQL. Hierarchical data is notoriously tricky to model in a relational database, and a variety of techniques have grown out of developers' attempts to optimize for certain types of queries.

In his post, Graeme describes several approaches to modeling trees, including:

  • Adjancency models, in which each node in the tree contains a foreign key to its parent row.
  • Materialized path model, in which each node stores its ancestral path in a denormalized column. Typically the path is stored as a string separated by a delimiter, e.g. "{root id}.{child id}.{grandchild id}".
  • Nested sets, in which each node defines an interval that encompasses a range of child nodes.
  • PostgreSQL arrays, in which the materialized path is stored in an array, and general inverted indexes are used to efficiently query the path.

In the comments, some users pointed out that the ltree extension could also be used to efficiently store and query materialized paths. LTrees support two powerful query languages (lquery and ltxtquery) for pattern-matching LTree labels and performing full-text searches on labels.

One technique that was not discussed in Graeme's post was the use of closure tables. A closure table is a many-to-many junction table storing all relationships between nodes in a tree. It is related to the adjacency model, in that each database row still stores a reference to its parent row. The closure table gets its name from the additional table, which stores each combination of ancestor/child nodes.


Web-based SQLite Database Browser, powered by Flask and Peewee


For the past week or two I've been spending some of my spare time working on a web-based SQLite database browser. I thought this would be a useful project, because I've switched all my personal projects over to SQLite and foresee using it for pretty much everything. It also dovetailed with some work I'd been doing lately on peewee regarding reflection and code generation. So it seemed like some pretty good bang/buck, especially given my perception that there weren't many SQLite browsers out there (it turns out there are quite a few, however). I'm sharing it in the hopes that other devs (and non-devs?) find it useful.


Dear Diary, an Encrypted Command-Line Diary with Python


In my last post, I wrote about how to work with encrypted SQLite databases with Python. As an example application of these libraries, I showed some code fragments for a fictional diary program. Because I was thinking the examples directory of the peewee repo was looking a little thin, I decided to flesh out the diary program and include it as an example.

In this post, I'll go over the diary code in the hopes that you may find it interesting or useful. The code shows how to use the peewee SQLCipher extension. I've also implemented a simple command-line menu loop. All told, the code is less than 100 lines!


Saturday morning hacks: Building an Analytics App with Flask

Saturday morning hacks

A couple years back I wrote about building an Analytics service with Cassandra. As fun as that project was to build, the reality was that Cassandra was completely unsuitable for my actual needs, so I decided to switch to something simpler. I'm happy to say the replacement app has been running without a hitch for the past 5 months taking up only about 20 MB of RAM! In this post I'll show how to build a lightweight Analytics service using Flask.


Encrypted SQLite Databases with Python and SQLCipher


SQLCipher, created by Zetetic, is an open-source library that provides transparent 256-bit AES encryption for your SQLite databases. SQLCipher is used by a large number of organizations, including Nasa, SalesForce, Xerox and more. The project is open-source and BSD licensed. Best of all, there are open-source python bindings.

A GitHub user known as The Dod was kind enough to contribute a sqlcipher playhouse module, making it a snap to use Peewee with SQLCipher.

In this post, I'll show how to compile SQLCipher and the pysqlcipher bindings, then use peewee ORM to work with an encrypted SQLite database.


Saturday morning hacks: Command-line client for the Flask note-taking app

saturday hacks huey and mickey

A while back I wrote about using Flask to create a note-taking app. The goal of that app was to make it really easy for me to save little notes from my phone or computer. In the first follow-up post, I showed how to transition the JSON views to a full-featured RESTful API. I also showed how to add email reminders and to-do lists with checkable items to the notes app. Then in the most recent post, I showed how to leverage SQLite's full-text search extension to make the notes searchable. In this, the final installment, I'll show how I built a command-line client for the note-taking app.

Here is how the notes app looked when we left off at the end of part 3:


Note-taking app code

If you'd like to follow along, you can find the source code for the most recent version of the note-taking app in this gist:

If you'd rather skip the post and get straight to the code, here is the code for the updated version: