Walrus: Lightweight Python utilities for working with Redis

january 11, 2015 07:49pm / nosql python redis walrus / 5 comments

photos/walrus-logo.png

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.

Read more...


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

january 06, 2015 11:04pm / elasticsearch python / 7 comments

photos/p1420602336.52.jpg

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:

Read more...


Pollywog: Fun with Regular Expressions

december 30, 2014 05:03pm / pollywog python regex / 0 comments

photos/p1419822415.19.png

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.

Read more...


Extending SQLite with Python

december 02, 2014 09:33pm / peewee python sqlite / 1 comments

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

november 22, 2014 08:52pm / peewee python sqlite / 0 comments

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:

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


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

november 13, 2014 08:28pm / python sqlite / 1 comments

photos/sqlite-browser.png

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.

Read more...


Dear Diary, an Encrypted Command-Line Diary with Python

november 03, 2014 03:20pm / peewee python sqlcipher sqlite / 2 comments

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


Encrypted SQLite Databases with Python and SQLCipher

october 27, 2014 11:20pm / peewee python sqlcipher sqlite / 5 comments

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

october 22, 2014 08:44pm / flask peewee python saturday-morning-hacks / 0 comments

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

october 17, 2014 05:39pm / peewee python saturday-morning-hacks / 0 comments

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