Entries tagged with peewee

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.


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


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.


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.


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.