Entries tagged with peewee

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

Managing Database Connections with Peewee

photos/p1423749536.32.png

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.

Read more...

Extending SQLite with Python

photos/sqlite-and-python.png

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.

Read more...

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

photos/fall-foliage.jpg

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.

Read more...

Dear Diary, an Encrypted Command-Line Diary with Python

photos/p1415049480.87.png

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!

Read more...

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.

Read more...

Encrypted SQLite Databases with Python and SQLCipher

photos/p1414470640.98.png

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.

Read more...

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:

photos/p1412692599.23.png

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:

https://gist.github.com/coleifer/d93d6c43e59698d149c0

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

https://gist.github.com/coleifer/948dd13f5aa98e2f1364

Read more...

Saturday morning hacks: DataSet for Peewee

Saturday morning hacks

I recently became acquainted with the dataset project while browsing a curated list of awesome SQLAlchemy resources. I was intrigued by the project's simplicity, and apparently I'm not the only one as the project has quite a few followers on GitHub. Since peewee has the ingredients required to provide a similar API (reflection, schema migrations), I decided it might be a fun project to add a DataSet-like module to the playhouse extension collection.

Taking a look at DataSet

I feel like the best way to explain DataSet is to show it in action, so let's take a look at what it can do.

>>> from playhouse.dataset import DataSet

>>> db = DataSet('sqlite:///:memory:')

>>> people = db['people']  # This will create the people table.

>>> people.insert(name='charlie', gender='M')
1
>>> people.insert(name='huey', gender='M', favorite_color='blue')
2

>>> list(people)
[{'favorite_color': None, 'gender': u'M', 'id': 1, 'name': u'charlie'},
 {'favorite_color': u'blue', 'gender': u'M', 'id': 2, 'name': u'huey'}]

>>> people.update(favorite_color='green', name='charlie', columns=['name'])
1

>>> people.find_one(name='charlie')['favorite_color']
u'green'

Read more...

Saturday morning hacks: Adding full-text search to the flask note-taking app

Saturday morning hacks

In preparation for the fourth and final installment in the "Flask Note-taking app" series, I found it necessary to improve the search feature of the note-taking app. In this post we will use SQLite's full-text search extension to improve the search feature.

To recap, the note-taking app provides a lightweight interface for storing markdown-formatted notes. Because I frequently find myself wanting to take notes on the spur of the moment, the note-taking app needed to be very mobile-friendly. By using twitter bootstrap and a hefty dose of JavaScript, we made an app that matches our spec and manages to look good doing it!

In part 2, we added email reminders and check-able task lists to the note-taking app. We also converted the backend to use flask-peewee's REST API extension, which made it easy to add pagination and search. And that is how I've left it for the last three months or so.

Below is a screenshot of the latest version of the notes app. The UI is much cleaner thanks to a stylesheet from bootswatch. The bootswatch stylesheet works as a drop-in replacement for the default bootstrap CSS file.

photos/p1412692599.23.png

All together, the note-taking app has the following features:

  • Flexible pinterest-style tiled layout that looks great on a variety of screen sizes.
  • Easy to create notes and reminders from the phone.
  • Notes support markdown and there is also a simple WYSIWYM markdown editing toolbar.
  • Links are converted to rich media objects where possible (e.g. a YouTube URL becomes an embedded player).
  • To-do lists (or task lists) can be embedded in notes.
  • Email reminders can be scheduled for a given note.
  • Simple full-text search.
  • Pagination.

You can browse or download the finished code from part 2 in this gist. If you're in a hurry, you can find all the code from this post in this gist.

In case you were curious, I've been using the notes app for things like:

  • Bookmarking interesting sites to read later.
  • Creating short to-do lists or writing down particular items to get from the store, etc.
  • Writing down interesting dreams or ideas I get in the middle of the night.
  • Appointment reminders, reminders to call people, etc.
  • Saving funny cat pics.
  • Writing down ideas for programming projects.
  • Saving code snippets or useful commands.

Read more...