Blog Entries all / by tag / by year / popular

Updated instructions for compiling BerkeleyDB with SQLite for use with Python

photos/sqlite-bdb.png

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.

Read more...

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.

Read more...

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

photos/lsm.png

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

Read more...

Connor Thomas Leifer

photos/p1435774453.7.jpg

Read more...

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?

Read more...

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

photos/p1428119319.19.jpg

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.

Read more...

Meet Scout, a Search Server Powered by SQLite

photos/scout.png

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.

Read more...

How to make a Flask blog in one hour or less

photos/p1425775019.9.png

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.

Read more...

Querying the top N objects per group with Peewee ORM

photos/p1425417194.66.png

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

Read more...

Querying the top item by group with peewee ORM

photos/kitties-and-toys.jpg

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.

Read more...