Playhouse, extensions to Peewee

Peewee comes with numerous extension modules which are collected under the playhouse namespace. Despite the silly name, there are some very useful extensions, particularly those that expose vendor-specific database features like the Sqlite Extensions and Postgresql Extensions extensions.

Below you will find a loosely organized listing of the various modules that make up the playhouse.

Database drivers / vendor-specific database functionality

• Sqlite Extensions
• SqliteQ
• Sqlite User-Defined Functions
• apsw, an advanced sqlite driver
• BerkeleyDB backend
• Sqlcipher backend
• Postgresql Extensions

High-level features

• Fields
• Shortcuts
• Hybrid Attributes
• Signal support
• DataSet
• Key/Value Store
• Generic foreign keys
• CSV Utils

Database management and framework integration

• pwiz, a model generator
• Schema Migrations
• Connection pool
• Reflection
• Database URL
• Read Slaves
• Test Utils
• pskel
• Flask Utils
• Django Integration

Sqlite Extensions

The SQLite extensions module provides support for some interesting sqlite-only features:

• Define custom aggregates, collations and functions.
• Support for FTS3/4 (sqlite full-text search) with BM25 ranking.
• C extension providing fast implementations of ranking and other utility functions.
• Support for the new FTS5 search extension.
• Specify isolation level in transactions.
• Support for virtual tables and SQLite C extensions.
• Support for the closure table extension, which allows efficient querying of heirarchical tables.

sqlite_ext API notes

class SqliteExtDatabase(database, pragmas=(), c_extensions=True, \*kwargs*)

Subclass of the SqliteDatabase that provides some advanced features only offered by Sqlite.

• Register custom aggregates, collations and functions
• Support for SQLite virtual tables and C extensions
• Specify a row factory

• Advanced transactions (specify isolation level)

• aggregate([name=None[, num_params=-1]])

  Class-decorator for registering custom aggregation functions.

  Parameters:	name – string name for the aggregate, defaults to the name of the class. num_params – integer representing number of parameters the aggregate function accepts. The default value, -1, indicates the aggregate can accept any number of parameters.

  @db.aggregate('product', 1)
  class Product(object):
      """Like sum, except calculate the product of a series of numbers."""
      def __init__(self):
          self.product = 1
      def step(self, value):
          self.product *= value
      def finalize(self):
          return self.product
  # To use this aggregate:
  product = (Score
             .select(fn.product(Score.value))
             .scalar())

• unregister_aggregate(name):

  Unregister the given aggregate function.

• collation([name])

  Function decorator for registering a custom collation.

  Parameters:	name – string name to use for this collation.

  @db.collation()
  def collate_reverse(s1, s2):
      return -cmp(s1, s2)
  # To use this collation:
  Book.select().order_by(collate_reverse.collation(Book.title))

  As you might have noticed, the original collate_reverse function has a special attribute called collation attached to it. This extra attribute provides a shorthand way to generate the SQL necessary to use our custom collation.

• unregister_collation(name):

  Unregister the given collation function.

• func([name[, num_params]])

  Function decorator for registering user-defined functions.

  Parameters:	name – name to use for this function. num_params – number of parameters this function accepts. If not provided, peewee will introspect the function for you.

  @db.func()
  def title_case(s):
      return s.title()
  # Use in the select clause...
  titled_books = Book.select(fn.title_case(Book.title))
  @db.func()
  def sha1(s):
      return hashlib.sha1(s).hexdigest()
  # Use in the where clause...
  user = User.select().where(
      (User.username == username) &
      (fn.sha1(User.password) == password_hash)).get()

• unregister_function(name):

  Unregister the given user-defiend function.

• load_extension(extension)

  Load the given C extension. If a connection is currently open in the calling thread, then the extension will be loaded for that connection as well as all subsequent connections.

  For example, if you’ve compiled the closure table extension and wish to use it in your application, you might write:

  db = SqliteExtDatabase('my_app.db')
  db.load_extension('closure')

• unload_extension(name):

  Unload the given SQLite extension.

class VirtualModel

Subclass of Model that signifies the model operates using a virtual table provided by a sqlite extension.

Creating a virtual model is easy, simply subclass VirtualModel and specify the extension module and any options:

class MyVirtualModel(VirtualModel):
    class Meta:
        database = db
        extension_module = 'nextchar'
        extension_options = {}

• Meta.extension_module = 'name of sqlite extension'

• Meta.extension_options = {'tokenize': 'porter', etc}

  SQLite virtual tables often support configuration via arbitrary key/value options which are included in the CREATE TABLE statement. To configure a virtual table, you can specify options like this:

  class SearchIndex(FTSModel):
      content = SearchField()
      metadata = SearchField()
      class Meta:
          database = my_db
          extension_options = {
              'prefix': [2, 3],
              'tokenize': 'porter',
          }

class FTSModel

Model class that provides support for Sqlite’s full-text search extension. Models should be defined normally, however there are a couple caveats:

• Unique constraints, not null constraints, check constraints and foreign keys are not supported.
• Indexes on fields and multi-column indexes are ignored completely
• Sqlite will treat all column types as TEXT (although you can store other data types, Sqlite will treat them as text).
• FTS models contain a docid field which is automatically created and managed by SQLite (unless you choose to explicitly set it during model creation). Lookups on this column are performant.

sqlite_ext provides a SearchField field class which should be used on FTSModel implementations instead of the regular peewee field types. This will help prevent you accidentally creating invalid column constraints.

Because of the lack of secondary indexes, it usually makes sense to use the docid primary key as a pointer to a row in a regular table. For example:

class Document(Model):
    author = ForeignKeyField(User, related_name='documents')
    title = TextField(null=False, unique=True)
    content = TextField(null=False)
    timestamp = DateTimeField()
    class Meta:
        database = db
class DocumentIndex(FTSModel):
    title = SearchField()
    content = SearchField()
    class Meta:
        database = db
        # Use the porter stemming algorithm to tokenize content.
        extension_options = {'tokenize': 'porter'}

To store a document in the document index, we will INSERT a row into the DocumentIndex table, manually setting the docid:

def store_document(document):
    DocumentIndex.insert({
        DocumentIndex.docid: document.id,
        DocumentIndex.title: document.title,
        DocumentIndex.content: document.content}).execute()

To perform a search and return ranked results, we can query the Document table and join on the DocumentIndex:

def search(phrase):
    # Query the search index and join the corresponding Document
    # object on each search result.
    return (Document
            .select()
            .join(
                DocumentIndex,
                on=(Document.id == DocumentIndex.docid))
            .where(DocumentIndex.match(phrase))
            .order_by(DocumentIndex.bm25()))

Warning

All SQL queries on FTSModel classes will be slow except full-text searches and docid lookups.

Continued examples:

# Use the "match" operation for FTS queries.
matching_docs = (DocumentIndex
                 .select()
                 .where(DocumentIndex.match('some query')))
# To sort by best match, use the custom "rank" function.
best = (DocumentIndex
        .select()
        .where(DocumentIndex.match('some query'))
        .order_by(DocumentIndex.rank()))
# Or use the shortcut method:
best = DocumentIndex.search('some phrase')
# Peewee allows you to specify weights for columns.
# Matches in the title will be 2x more valuable than matches
# in the content field:
best = DocumentIndex.search(
    'some phrase',
    weights=[2.0, 1.0],
)

Examples using the BM25 ranking algorithm:

# you can also use the BM25 algorithm to rank documents:
best = (DocumentIndex
        .select()
        .where(DocumentIndex.match('some query'))
        .order_by(DocumentIndex.bm25()))
# There is a shortcut method for bm25 as well:
best_bm25 = DocumentIndex.search_bm25('some phrase')
# BM25 allows you to specify weights for columns.
# Matches in the title will be 2x more valuable than matches
# in the content field:
best_bm25 = DocumentIndex.search_bm25(
    'some phrase',
    weights=[2.0, 1.0],
)

If the primary source of the content you are indexing exists in a separate table, you can save some disk space by instructing SQLite to not store an additional copy of the search index content. SQLite will still create the metadata and data-structures needed to perform searches on the content, but the content itself will not be stored in the search index.

To accomplish this, you can specify a table or column using the content option. The FTS4 documentation has more information.

Here is a short code snippet illustrating how to implement this with peewee:

class Blog(Model):
    title = CharField()
    pub_date = DateTimeField()
    content = TextField()  # we want to search this.
    class Meta:
        database = db
class BlogIndex(FTSModel):
    content = SearchField()
    class Meta:
        database = db
        extension_options = {'content': Blog.content}
db.create_tables([Blog, BlogIndex])
# Now, we can manage content in the FTSBlog.  To populate it with
# content:
BlogIndex.rebuild()
# Optimize the index.
BlogIndex.optimize()

The content option accepts either a single Field or a Model and can reduce the amount of storage used. However, content will need to be manually moved to/from the associated FTSModel.

FTSModel API methods:

• classmethod create_table([fail_silently=False[, \*options*]])

  Parameters:	fail_silently (boolean) – do not re-create if table already exists. options – options passed along when creating the table, e.g. content.

• classmethod match(term)

  Shorthand for generating a MATCH expression for the given term(s).

  query = (DocumentIndex
           .select()
           .where(DocumentIndex.match('search phrase')))
  for doc in query:
      print 'match: ', doc.title

• classmethod search(term[, weights=None[, with_score=False[, score_alias=’score’]]])

  Shorthand way of searching for a term and sorting results by the quality of the match. This is equivalent to the rank() example code presented below.

  Parameters:

  term (str) – Search term to use. weights – A list of weights for the columns, ordered with respect to the column’s position in the table. Or, a dictionary keyed by the field or field name and mapped to a value. with_score – Whether the score should be returned as part of the SELECT statement. score_alias (str) – Alias to use for the calculated rank score. This is the attribute you will use to access the score if with_score=True.

  # Simple search.
  docs = DocumentIndex.search('search term')
  for result in docs:
      print result.title
  # More complete example.
  docs = DocumentIndex.search(
      'search term',
      weights={'title': 2.0, 'content': 1.0},
      with_score=True,
      score_alias='search_score')
  for result in docs:
      print result.title, result.search_score

• classmethod rank([col1_weight, col2_weight…coln_weight])

  Generate an expression that will calculate and return the quality of the search match. This rank can be used to sort the search results. The lower the rank, the better the match.

  The rank function accepts optional parameters that allow you to specify weights for the various columns. If no weights are specified, all columns are considered of equal importance.

  query = (DocumentIndex
           .select(
               DocumentIndex,
               DocumentIndex.rank().alias('score'))
           .where(DocumentIndex.match('search phrase'))
           .order_by(DocumentIndex.rank()))
  for search_result in query:
      print search_result.title, search_result.score

• classmethod search_bm25(term[, weights=None[, with_score=False[, score_alias=’score’]]])

  Shorthand way of searching for a term and sorting results by the quality of the match, as determined by the BM25 algorithm. This is equivalent to the bm25() example code presented below.

  Parameters:

  term (str) – Search term to use. weights – A list of weights for the columns, ordered with respect to the column’s position in the table. Or, a dictionary keyed by the field or field name and mapped to a value. with_score – Whether the score should be returned as part of the SELECT statement. score_alias (str) – Alias to use for the calculated rank score. This is the attribute you will use to access the score if with_score=True.

  # Simple search.
  docs = DocumentIndex.search('search term')
  for result in docs:
      print result.title
  # More complete example.
  docs = DocumentIndex.search(
      'search term',
      weights={'title': 2.0, 'content': 1.0},
      with_score=True,
      score_alias='search_score')
  for result in docs:
      print result.title, result.search_score

• classmethod bm25([col1_weight, col2_weight…coln_weight])

  Generate an expression that will calculate and return the quality of the search match using the BM25 algorithm. This value can be used to sort the search results, and the lower the value the better the match.

  The bm25 function accepts optional parameters that allow you to specify weights for the various columns. If no weights are specified, all columns are considered of equal importance.

  query = (DocumentIndex
           .select(
               DocumentIndex,
               DocumentIndex.bm25().alias('score'))
           .where(DocumentIndex.match('search phrase'))
           .order_by(DocumentIndex.bm25()))
  for search_result in query:
      print search_result.title, search_result.score

• classmethod rebuild()

  Rebuild the search index – this only works when the content option was specified during table creation.

• classmethod optimize()

  Optimize the search index.

class SearchField([unindexed=False[, db_column=None[, coerce=None]]])

class JSONField

Field class suitable for working with JSON stored and manipulated using the JSON1 extension.

Most functions that operate on JSON fields take a path argument. The JSON documents specify that the path should begin with '$' followed by zero or more instances of '.objectlabel' or '[arrayindex]'. Peewee simplifies this by allowing you to omit the '$' character and just specify the path you need or None for an empty path:

• path='' –> '$'
• path='tags' –> '$.tags'
• path='[0][1].bar' –> '$[0][1].bar'
• path='metadata[0]' –> '$.metadata[0]'

• path='user.data.email' –> '$.user.data.email'

• length([path=None])

  Return the number of items in a JSON array at the given path. If the path is omitted, then return the number of items in the top-level array.

  SQLite documentation.

• extract(path)

  Return the value at the given path. If the value is a JSON object or array, it will be decoded into a dict or list. If the value is a scalar type, string or null then it will be returned as the appropriate Python type.

  SQLite documentation.

  Example:

  # data looks like {'post': {'title': 'post 1', 'body': '...'}, ...}
  query = (Post
           .select(Post.data.json_extract('post.title'))
           .tuples())
  # Only the `title` value is extracted from the JSON data.
  for title, in query:
      print title

• set(path, value[, path2, value2…])

  Set values stored in the input JSON string using the given path/value pairs. The set function returns a new JSON string formed by updating the input JSON with the given path/value pairs.

  If the path does not exist, it will be created.

  Similarly, if the path does exist, it will be overwritten.

  SQLite documentation.

  Example:

  PostAlias = Post.alias()
  set_query = (PostAlias
               .select(PostAlias.data.set(
                   'title', 'New title',
                   'tags', ['list', 'of', 'new', 'tags'],
                   'totally.new.field', 3,
                   'status.published', True))
               .where(PostAlias.id == Post.id))
  # Update multiple fields at one time on the Post
  # with the title "Old title".
  query = (Post
           .update(data=set_query)
           .where(Post.data.extract('title') == 'Old title'))
  query.execute()
  post = (Post
          .select()
          .where(Post.data.extract('title') == 'New title')
          .get())
  # Our new data has been added, even nested objects that did not
  # exist before. Any pre-existing data has also been preserved,
  # provided it was not over-written.
  assert post.data == {
      'title': 'New title',
      'tags': ['list', 'of', 'new', 'tags'],
      'totally': {'new': {'field: 3}},
      'status': {'published': True, 'draft': False},
      'other-field': ['this', 'was', 'here', 'before'],
      'another-old-field': 'etc, etc'}

• insert(path, value[, path2, value2…])

  Insert the given path/value pairs into the JSON string stored in the field. The insert function returns a new JSON string formed by updating the input JSON with the given path/value pairs.

  If the path already exists, it will not be overwritten.

  SQLite documentation.

• replace(path, value[, path2, value2…])

  Replace values stored in the input JSON string using the given path/value pairs. The replace function returns a new JSON string formed by updating the input JSON with the given path/value pairs.

  If the path does not exist, it will not be created.

  SQLite documentation.

• remove(\paths*)

  Remove values referenced by the given path(s). The remove function returns a new JSON string formed by removing the specified paths from the input JSON string.

  The process for removing fields from a JSON column is similar to the way you set() them. For a code example, see updating JSON data.

  SQLite documentation.

• json_type([path=None])

  Return a string indicating the type of object stored in the field. You can optionally supply a path to specify a sub-item. The types of objects are:

  • object
  • array
  • integer
  • real
  • true
  • false
  • text
  • null <– the string “null” means an actual NULL value
  • NULL <– an actual NULL value means the path was not found

  SQLite documentation.

• children([path=None])

  The children function corresponds to json_each, a table-valued function that walks the JSON value provided and returns the immediate children of the top-level array or object. If a path is specified, then that path is treated as the top-most element.

  The rows returned by calls to children() have the following attributes:

  • key: the key of the current element relative to its parent.
  • value: the value of the current element.
  • type: one of the data-types (see json_type()).
  • atom: the scalar value for primitive types, NULL for arrays and objects.
  • id: a unique ID referencing the current node in the tree.
  • parent: the ID of the containing node.
  • fullkey: the full path describing the current element.
  • path: the path to the container of the current row.

  For examples, see my blog post on JSON1.

  SQLite documentation.

• tree([path=None])

  The tree function corresponds to json_tree, a table-valued function that walks the JSON value provided and recursively returns all descendants of the given root node. If a path is specified, then that path is treated as the root node element.

  The rows returned by calls to tree() have the same attributes as rows returned by calls to children().

  For examples, see my blog post on JSON1.

  SQLite documentation.

class PrimaryKeyAutoIncrementField

Subclass of PrimaryKeyField that uses a monotonically-increasing value for the primary key. This differs from the default SQLite primary key, which simply uses the “max + 1” approach to determining the next ID.

class RowIDField

Subclass of PrimaryKeyField that provides access to the underlying rowid field used internally by SQLite.

Note

When added to a Model, this field will act as the primary key. However, this field will not be included by default when selecting rows from the table.

class DocIDField

Subclass of PrimaryKeyField that provides access to the underlying docid field used internally by SQLite’s FTS3/4 virtual tables.

Note

This field should not be created manually, as it is only needed on FTSModel classes, which include it already.

match(lhs, rhs)

Generate a SQLite MATCH expression for use in full-text searches.

Document.select().where(match(Document.content, 'search term'))

class FTS5Model

Model class that should be used to implement virtual tables using the FTS5 extension. Documentation on the FTS5 extension can be found here. This extension behaves very similarly to the FTS3 and FTS4 extensions, and the FTS5Model supports many of the same APIs as FTSModel.

The FTS5 extension is more strict in enforcing that no column define any type or constraints. For this reason, only SearchField objects can be used with FTS5Model implementations.

Additionally, FTS5 comes with a built-in implementation of the BM25 ranking function. Therefore, the search and search_bm25 methods have been overridden to use the builtin ranking functions rather than user-defined functions.

• classmethod fts5_installed()

  Return a boolean indicating whether the FTS5 extension is installed. If it is not installed, an attempt will be made to load the extension.

• classmethod search(term[, weights=None[, with_score=False[, score_alias=’score’]]])

  Shorthand way of searching for a term and sorting results by the quality of the match. This is equivalent to the built-in rank value provided by the FTS5 extension.

  Parameters:

  term (str) – Search term to use. weights – A list of weights for the columns, ordered with respect to the column’s position in the table. Or, a dictionary keyed by the field or field name and mapped to a value. with_score – Whether the score should be returned as part of the SELECT statement. score_alias (str) – Alias to use for the calculated rank score. This is the attribute you will use to access the score if with_score=True.

  # Simple search.
  docs = DocumentIndex.search('search term')
  for result in docs:
      print result.title
  # More complete example.
  docs = DocumentIndex.search(
      'search term',
      weights={'title': 2.0, 'content': 1.0},
      with_score=True,
      score_alias='search_score')
  for result in docs:
      print result.title, result.search_score

• classmethod search_bm25(term[, weights=None[, with_score=False[, score_alias=’score’]]])

  With FTS5, the search_bm25 method is the same as the FTS5Model.search() method.

• classmethod VocabModel([table_type=’row’|’col’[, table_name=None]])

  Parameters:	table_type – Either ‘row’ or ‘col’. table_name – Name for the vocab table. If not specified, will be “fts5tablename_v”.

ClosureTable(model_class[, foreign_key=None[, referencing_class=None, referencing_key=None]])

Factory function for creating a model class suitable for working with a transitive closure table. Closure tables are VirtualModel subclasses that work with the transitive closure SQLite extension. These special tables are designed to make it easy to efficiently query heirarchical data. The SQLite extension manages an AVL tree behind-the-scenes, transparently updating the tree when your table changes and making it easy to perform common queries on heirarchical data.

To use the closure table extension in your project, you need:

1. A copy of the SQLite extension. The source code can be found in the SQLite code repository or by cloning this gist:

   $ git clone https://gist.github.com/coleifer/7f3593c5c2a645913b92 closure
   $ cd closure/

2. Compile the extension as a shared library, e.g.

   $ gcc -g -fPIC -shared closure.c -o closure.so

3. Create a model for your hierarchical data. The only requirement here is that the model has an integer primary key and a self-referential foreign key. Any additional fields are fine.

   class Category(Model):
       name = CharField()
       metadata = TextField()
       parent = ForeignKeyField('self', index=True, null=True)  # Required.
   # Generate a model for the closure virtual table.
   CategoryClosure = ClosureTable(Category)

   The self-referentiality can also be achieved via an intermediate table (for a many-to-many relation).

   class User(Model):
       name = CharField()
   class UserRelations(Model):
       user = ForeignKeyField(User)
       knows = ForeignKeyField(User, related_name='_known_by')
       class Meta:
           primary_key = CompositeKey('user', 'knows') # Alternatively, a unique index on both columns.
   # Generate a model for the closure virtual table, specifying the UserRelations as the referencing table
   UserClosure = ClosureTable(
       User,
       referencing_class=UserRelations,
       foreign_key=UserRelations.knows,
       referencing_key=UserRelations.user)

4. In your application code, make sure you load the extension when you instantiate your Database object. This is done by passing the path to the shared library to the load_extension() method.

   db = SqliteExtDatabase('my_database.db')
   db.load_extension('/path/to/closure')

Warning

There are two caveats you should be aware of when using the transitive_closure extension. First, it requires that your source model have an integer primary key. Second, it is strongly recommended that you create an index on the self-referential foreign key.

Example code:

db = SqliteExtDatabase('my_database.db')
db.load_extension('/path/to/closure')
class Category(Model):
    name = CharField()
    parent = ForiegnKeyField('self', index=True, null=True)  # Required.
    class Meta:
        database = db
CategoryClosure = ClosureTable(Category)
# Create the tables if they do not exist.
db.create_tables([Category, CategoryClosure], True)

It is now possible to perform interesting queries using the data from the closure table:

# Get all ancestors for a particular node.
laptops = Category.get(Category.name == 'Laptops')
for parent in Closure.ancestors(laptops):
    print parent.name
# Computer Hardware
# Computers
# Electronics
# All products
# Get all descendants for a particular node.
hardware = Category.get(Category.name == 'Computer Hardware')
for node in Closure.descendants(hardware):
    print node.name
# Laptops
# Desktops
# Hard-drives
# Monitors
# LCD Monitors
# LED Monitors

The VirtualTable returned by this function contains a handful of interesting methods. The model will be a subclass of BaseClosureTable.

• class BaseClosureTable

  • id

    A field for the primary key of the given node.

  • depth

    A field representing the relative depth of the given node.

  • root

    A field representing the relative root node.

  • descendants(node[, depth=None[, include_node=False]])

    Retrieve all descendants of the given node. If a depth is specified, only nodes at that depth (relative to the given node) will be returned.

    node = Category.get(Category.name == 'Electronics')
    # Direct child categories.
    children = CategoryClosure.descendants(node, depth=1)
    # Grand-child categories.
    children = CategoryClosure.descendants(node, depth=2)
    # Descendants at all depths.
    all_descendants = CategoryClosure.descendants(node)

  • ancestors(node[, depth=None[, include_node=False]])

    Retrieve all ancestors of the given node. If a depth is specified, only nodes at that depth (relative to the given node) will be returned.

    node = Category.get(Category.name == 'Laptops')
    # All ancestors.
    all_ancestors = CategoryClosure.ancestors(node)
    # Grand-parent category.
    grandparent = CategoryClosure.ancestores(node, depth=2)

  • siblings(node[, include_node=False])

    Retrieve all nodes that are children of the specified node’s parent.

Note

For an in-depth discussion of the SQLite transitive closure extension, check out this blog post, Querying Tree Structures in SQLite using Python and the Transitive Closure Extension.

SqliteQ

The playhouse.sqliteq module provides a subclass of SqliteExtDatabase, that will serialize concurrent writes to a SQLite database. SqliteQueueDatabase can be used as a drop-in replacement for the regular SqliteDatabase if you want simple read and write access to a SQLite database from multiple threads.

SQLite only allows one connection to write to the database at any given time. As a result, if you have a multi-threaded application (like a web-server, for example) that needs to write to the database, you may see occasional errors when one or more of the threads attempting to write cannot acquire the lock.

SqliteQueueDatabase is designed to simplify things by sending all write queries through a single, long-lived connection. The benefit is that you get the appearance of multiple threads writing to the database without conflicts or timeouts. The downside, however, is that you cannot issue write transactions that encompass multiple queries – all writes run in autocommit mode, essentially.

Note

The module gets its name from the fact that all write queries get put into a thread-safe queue. A single worker thread listens to the queue and executes all queries that are sent to it.

Transactions

Because all queries are serialized and executed by a single worker thread, it is possible for transactional SQL from separate threads to be executed out-of-order. In the example below, the transaction started by thread “B” is rolled back by thread “A” (with bad consequences!):

• Thread A: UPDATE transplants SET organ=’liver’, …;
• Thread B: BEGIN TRANSACTION;
• Thread B: UPDATE life_support_system SET timer += 60 …;
• Thread A: ROLLBACK; – Oh no….

Since there is a potential for queries from separate transactions to be interleaved, the transaction() and atomic() methods are disabled on SqliteQueueDatabase.

For cases when you wish to temporarily write to the database from a different thread, you can use the pause() and unpause() methods. These methods block the caller until the writer thread is finished with its current workload. The writer then disconnects and the caller takes over until unpause is called.

The stop(), start(), and is_stopped() methods can also be used to control the writer thread.

Note

Take a look at SQLite’s isolation documentation for more information about how SQLite handles concurrent connections.

Code sample

Creating a database instance does not require any special handling. The SqliteQueueDatabase accepts some special parameters which you should be aware of, though. If you are using gevent, you must specify use_gevent=True when instantiating your database – this way Peewee will know to use the appropriate objects for handling queueing, thread creation, and locking.

from playhouse.sqliteq import SqliteQueueDatabase
db = SqliteQueueDatabase(
    'my_app.db',
    use_gevent=False,  # Use the standard library "threading" module.
    autostart=False,  # The worker thread now must be started manually.
    queue_max_size=64,  # Max. # of pending writes that can accumulate.
    results_timeout=5.0)  # Max. time to wait for query to be executed.

If autostart=False, as in the above example, you will need to call start() to bring up the worker threads that will do the actual write query execution.

@app.before_first_request
def _start_worker_threads():
    db.start()

If you plan on performing SELECT queries or generally wanting to access the database, you will need to call connect() and close() as you would with any other database instance.

When your application is ready to terminate, use the stop() method to shut down the worker thread. If there was a backlog of work, then this method will block until all pending work is finished (though no new work is allowed).

import atexit
@atexit.register
def _stop_worker_threads():
    db.stop()

Lastly, the is_stopped() method can be used to determine whether the database writer is up and running.

Sqlite User-Defined Functions

The sqlite_udf playhouse module contains a number of user-defined functions, aggregates, and table-valued functions, which you may find useful. The functions are grouped in collections and you can register these user-defined extensions individually, by collection, or register everything.

Scalar functions are functions which take a number of parameters and return a single value. For example, converting a string to upper-case, or calculating the MD5 hex digest.

Aggregate functions are like scalar functions that operate on multiple rows of data, producing a single result. For example, calculating the sum of a list of integers, or finding the smallest value in a particular column.

Table-valued functions are simply functions that can return multiple rows of data. For example, a regular-expression search function that returns all the matches in a given string, or a function that accepts two dates and generates all the intervening days.

Note

To use table-valued functions, you will need to install the vtfunc module. The vtfunc module is available on GitHub or can be installed using pip.

Functions, listed by collection name

Scalar functions are indicated by (f), aggregate functions by (a), and table-valued functions by (t).

• CONTROL_FLOW * if_then_else() (f)
• DATE * strip_tz() (f) * human_delta() (f) * mintdiff() (a) * avgtdiff() (a) * duration() (a) * date_series() (t)
• FILE * file_ext() (f) * file_read() (f)
• HELPER * gzip() (f) * gunzip() (f) * hostname() (f) * toggle() (f) * setting() (f) * clear_toggles() (f) * clear_settings() (f)
• MATH * randomrange() (f) * gauss_distribution() (f) * sqrt() (f) * tonumber() (f) * mode() (a) * minrange() (a) * avgrange() (a) * range() (a) * median() (a) (requires cython)
• STRING * substr_count() (f) * strip_chars() (f) * md5() (f) * sha1() (f) * sha256() (f) * sha512() (f) * adler32() (f) * crc32() (f) * damerau_levenshtein_dist() (f) (requires cython) * levenshtein_dist() (f) (requires cython) * str_dist() (f) (requires cython) * regex_search() (t)

apsw, an advanced sqlite driver

The apsw_ext module contains a database class suitable for use with the apsw sqlite driver.

APSW Project page: https://github.com/rogerbinns/apsw

APSW is a really neat library that provides a thin wrapper on top of SQLite’s C interface, making it possible to use all of SQLite’s advanced features.

Here are just a few reasons to use APSW, taken from the documentation:

• APSW gives all functionality of SQLite, including virtual tables, virtual file system, blob i/o, backups and file control.
• Connections can be shared across threads without any additional locking.
• Transactions are managed explicitly by your code.
• APSW can handle nested transactions.
• Unicode is handled correctly.
• APSW is faster.

For more information on the differences between apsw and pysqlite, check the apsw docs.

How to use the APSWDatabase

from apsw_ext import *
db = APSWDatabase(':memory:')
class BaseModel(Model):
    class Meta:
        database = db
class SomeModel(BaseModel):
    col1 = CharField()
    col2 = DateTimeField()

apsw_ext API notes

APSWDatabase extends the SqliteExtDatabase and inherits its advanced features.

class APSWDatabase(database, \*connect_kwargs*)

• register_module(mod_name, mod_inst)

  Provides a way of globally registering a module. For more information, see the documentation on virtual tables.

  Parameters:	mod_name (string) – name to use for module mod_inst (object) – an object implementing the Virtual Table interface

• unregister_module(mod_name)

  Unregister a module.

  Parameters:	mod_name (string) – name to use for module

Note

Be sure to use the Field subclasses defined in the apsw_ext module, as they will properly handle adapting the data types for storage.

For example, instead of using peewee.DateTimeField, be sure you are importing and using playhouse.apsw_ext.DateTimeField.

BerkeleyDB backend

BerkeleyDB provides a SQLite-compatible API. BerkeleyDB’s SQL API has many advantages over SQLite:

• Higher transactions-per-second in multi-threaded environments.
• Built-in replication and hot backup.
• Fewer system calls, less resource utilization.
• Multi-version concurrency control.

For more details, Oracle has published a short technical overview.

In order to use peewee with BerkeleyDB, you need to compile BerkeleyDB with the SQL API enabled. Then compile the Python SQLite driver against BerkeleyDB’s sqlite replacement.

Begin by downloading and compiling BerkeleyDB:

wget http://download.oracle.com/berkeley-db/db-6.0.30.tar.gz
tar xzf db-6.0.30.tar.gz
cd db-6.0.30/build_unix
export CFLAGS='-DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT -DSQLITE_SECURE_DELETE -DSQLITE_SOUNDEX -DSQLITE_ENABLE_RTREE=1 -fPIC'
../dist/configure --enable-static --enable-shared --enable-sql --enable-sql-compat
make
sudo make prefix=/usr/local/ install

Then get a copy of the standard library SQLite driver and build it against BerkeleyDB:

git clone https://github.com/ghaering/pysqlite
cd pysqlite
sed -i "s|#||g" setup.cfg
python setup.py build
sudo python setup.py install

You can also find up-to-date step by step instructions on my blog.

class BerkeleyDatabase(database, \*kwargs*)

Subclass of the SqliteExtDatabase that supports connecting to BerkeleyDB-backed version of SQLite.

• classmethod check_pysqlite()

  Check whether pysqlite2 was compiled against the BerkeleyDB SQLite. Returns True or False.

• classmethod check_libsqlite()

  Check whether libsqlite3 is the BerkeleyDB SQLite implementation. Returns True or False.

Sqlcipher backend

• Although this extention’s code is short, it has not been properly peer-reviewed yet and may have introduced vulnerabilities.
• The code contains minimum values for passphrase length and kdf_iter, as well as a default value for the later. Do not regard these numbers as advice. Consult the docs at http://sqlcipher.net/sqlcipher-api/ and security experts.

Also note that this code relies on pysqlcipher and sqlcipher, and the code there might have vulnerabilities as well, but since these are widely used crypto modules, we can expect “short zero days” there.

sqlcipher_ext API notes

class SqlCipherDatabase(database, passphrase, kdf_iter=64000, \*kwargs*)

Subclass of SqliteDatabase that stores the database encrypted. Instead of the standard sqlite3 backend, it uses pysqlcipher: a python wrapper for sqlcipher, which – in turn – is an encrypted wrapper around sqlite3, so the API is identical to SqliteDatabase’s, except for object construction parameters:

• If the database file doesn’t exist, it will be created with encryption by a key derived from passhprase with kdf_iter PBKDF2 iterations.
• When trying to open an existing database, passhprase and kdf_iter should be identical to the ones used when it was created.

Notes:

• [Hopefully] there’s no way to tell whether the passphrase is wrong or the file is corrupt. In both cases – the first time we try to acces the database – a DatabaseError error is raised, with the exact message: "file is encrypted or is not a database".

  As mentioned above, this only happens when you access the databse, so if you need to know right away whether the passphrase was correct, you can trigger this check by calling [e.g.] get_tables() (see example below).

• Most applications can expect failed attempts to open the database (common case: prompting the user for passphrase), so the database can’t be hardwired into the Meta of model classes. To defer initialization, pass None in to the database.

Example:

db = SqlCipherDatabase(None)
class BaseModel(Model):
    """Parent for all app's models"""
    class Meta:
        # We won't have a valid db until user enters passhrase.
        database = db
# Derive our model subclasses
class Person(BaseModel):
    name = CharField(primary_key=True)
right_passphrase = False
while not right_passphrase:
    db.init(
        'testsqlcipher.db',
        passphrase=get_passphrase_from_user())
    try:  # Actually execute a query against the db to test passphrase.
        db.get_tables()
    except DatabaseError as exc:
        # We only allow a specific [somewhat cryptic] error message.
        if exc.args[0] != 'file is encrypted or is not a database':
            raise exc
        else:
            tell_user_the_passphrase_was_wrong()
            db.init(None)  # Reset the db.
    else:
        # The password was correct.
        right_passphrase = True

See also: a slightly more elaborate example.

Postgresql Extensions

The postgresql extensions module provides a number of “postgres-only” functions, currently:

• hstore support
• json support, including jsonb for Postgres 9.4.
• server-side cursors
• full-text search
• ArrayField field type, for storing arrays.
• HStoreField field type, for storing key/value pairs.
• IntervalField field type, for storing timedelta objects.
• JSONField field type, for storing JSON data.
• BinaryJSONField field type for the jsonb JSON data type.
• TSVectorField field type, for storing full-text search data.
• DateTimeTZ field type, a timezone-aware datetime field.

In the future I would like to add support for more of postgresql’s features. If there is a particular feature you would like to see added, please open a Github issue.

Warning

In order to start using the features described below, you will need to use the extension PostgresqlExtDatabase class instead of PostgresqlDatabase.

The code below will assume you are using the following database and base model:

from playhouse.postgres_ext import *
ext_db = PostgresqlExtDatabase('peewee_test', user='postgres')
class BaseExtModel(Model):
    class Meta:
        database = ext_db

hstore support

Postgresql hstore is an embedded key/value store. With hstore, you can store arbitrary key/value pairs in your database alongside structured relational data.

Currently the postgres_ext module supports the following operations:

• Store and retrieve arbitrary dictionaries
• Filter by key(s) or partial dictionary
• Update/add one or more keys to an existing dictionary
• Delete one or more keys from an existing dictionary
• Select keys, values, or zip keys and values
• Retrieve a slice of keys/values
• Test for the existence of a key
• Test that a key has a non-NULL value

Using hstore

To start with, you will need to import the custom database class and the hstore functions from playhouse.postgres_ext (see above code snippet). Then, it is as simple as adding a HStoreField to your model:

class House(BaseExtModel):
    address = CharField()
    features = HStoreField()

You can now store arbitrary key/value pairs on House instances:

>>> h = House.create(address='123 Main St', features={'garage': '2 cars', 'bath': '2 bath'})
>>> h_from_db = House.get(House.id == h.id)
>>> h_from_db.features
{'bath': '2 bath', 'garage': '2 cars'}

You can filter by keys or partial dictionary:

>>> f = House.features
>>> House.select().where(f.contains('garage')) # <-- all houses w/garage key
>>> House.select().where(f.contains(['garage', 'bath'])) # <-- all houses w/garage & bath
>>> House.select().where(f.contains({'garage': '2 cars'})) # <-- houses w/2-car garage

Suppose you want to do an atomic update to the house:

>>> f = House.features
>>> new_features = House.features.update({'bath': '2.5 bath', 'sqft': '1100'})
>>> query = House.update(features=new_features)
>>> query.where(House.id == h.id).execute()
1
>>> h = House.get(House.id == h.id)
>>> h.features
{'bath': '2.5 bath', 'garage': '2 cars', 'sqft': '1100'}

Or, alternatively an atomic delete:

>>> query = House.update(features=f.delete('bath'))
>>> query.where(House.id == h.id).execute()
1
>>> h = House.get(House.id == h.id)
>>> h.features
{'garage': '2 cars', 'sqft': '1100'}

Multiple keys can be deleted at the same time:

>>> query = House.update(features=f.delete('garage', 'sqft'))

You can select just keys, just values, or zip the two:

>>> f = House.features
>>> for h in House.select(House.address, f.keys().alias('keys')):
...     print h.address, h.keys
123 Main St [u'bath', u'garage']
>>> for h in House.select(House.address, f.values().alias('vals')):
...     print h.address, h.vals
123 Main St [u'2 bath', u'2 cars']
>>> for h in House.select(House.address, f.items().alias('mtx')):
...     print h.address, h.mtx
123 Main St [[u'bath', u'2 bath'], [u'garage', u'2 cars']]

You can retrieve a slice of data, for example, all the garage data:

>>> f = House.features
>>> for h in House.select(House.address, f.slice('garage').alias('garage_data')):
...     print h.address, h.garage_data
123 Main St {'garage': '2 cars'}

You can check for the existence of a key and filter rows accordingly:

>>> for h in House.select(House.address, f.exists('garage').alias('has_garage')):
...     print h.address, h.has_garage
123 Main St True
>>> for h in House.select().where(f.exists('garage')):
...     print h.address, h.features['garage'] # <-- just houses w/garage data
123 Main St 2 cars

Interval support

Postgres supports durations through the INTERVAL data-type (docs).

class IntervalField([null=False[, …]])

Field class capable of storing Python datetime.timedelta instances.

Example:

from datetime import timedelta
from playhouse.postgres_ext import *
db = PostgresqlExtDatabase('my_db')
class Event(Model):
    location = CharField()
    duration = IntervalField()
    start_time = DateTimeField()
    class Meta:
        database = db
    @classmethod
    def get_long_meetings(cls):
        return cls.select().where(cls.duration > timedelta(hours=1))

JSON Support

peewee has basic support for Postgres’ native JSON data type, in the form of JSONField. As of version 2.4.7, peewee also supports the Postgres 9.4 binary json jsonb type, via BinaryJSONField.

Warning

Postgres supports a JSON data type natively as of 9.2 (full support in 9.3). In order to use this functionality you must be using the correct version of Postgres with psycopg2 version 2.5 or greater.

To use BinaryJSONField, which has many performance and querying advantages, you must have Postgres 9.4 or later.

Note

You must be sure your database is an instance of PostgresqlExtDatabase in order to use the JSONField.

Here is an example of how you might declare a model with a JSON field:

import json
import urllib2
from playhouse.postgres_ext import *
db = PostgresqlExtDatabase('my_database')  # note
class APIResponse(Model):
    url = CharField()
    response = JSONField()
    class Meta:
        database = db
    @classmethod
    def request(cls, url):
        fh = urllib2.urlopen(url)
        return cls.create(url=url, response=json.loads(fh.read()))
APIResponse.create_table()
# Store a JSON response.
offense = APIResponse.request('http://wtf.charlesleifer.com/api/offense/')
booking = APIResponse.request('http://wtf.charlesleifer.com/api/booking/')
# Query a JSON data structure using a nested key lookup:
offense_responses = APIResponse.select().where(
  APIResponse.response['meta']['model'] == 'offense')
# Retrieve a sub-key for each APIResponse. By calling .as_json(), the
# data at the sub-key will be returned as Python objects (dicts, lists,
# etc) instead of serialized JSON.
q = (APIResponse
     .select(
       APIResponse.data['booking']['person'].as_json().alias('person'))
     .where(
       APIResponse.data['meta']['model'] == 'booking'))
for result in q:
    print result.person['name'], result.person['dob']

The BinaryJSONField works the same and supports the same operations as the regular JSONField, but provides several additional operations for testing containment. Using the binary json field, you can test whether your JSON data contains other partial JSON structures (contains(), contains_any(), contains_all()), or whether it is a subset of a larger JSON document (contained_by()).

For more examples, see the JSONField and BinaryJSONField API documents below.

Server-side cursors

When psycopg2 executes a query, normally all results are fetched and returned to the client by the backend. This can cause your application to use a lot of memory when making large queries. Using server-side cursors, results are returned a little at a time (by default 2000 records). For the definitive reference, please see the psycopg2 documentation.

Note

To use server-side (or named) cursors, you must be using PostgresqlExtDatabase.

To execute a query using a server-side cursor, simply wrap your select query using the ServerSide() helper:

large_query = PageView.select()  # Build query normally.
# Iterate over large query inside a transaction.
for page_view in ServerSide(large_query):
    # do some interesting analysis here.
    pass
# Server-side resources are released.

If you would like all SELECT queries to automatically use a server-side cursor, you can specify this when creating your PostgresqlExtDatabase:

from postgres_ext import PostgresqlExtDatabase
ss_db = PostgresqlExtDatabase('my_db', server_side_cursors=True)

Note

Server-side cursors live only as long as the transaction, so for this reason peewee will not automatically call commit() after executing a SELECT query. If you do not commit after you are done iterating, you will not release the server-side resources until the connection is closed (or the transaction is committed later). Furthermore, since peewee will by default cache rows returned by the cursor, you should always call .iterator() when iterating over a large query.

If you are using the ServerSide() helper, the transaction and call to iterator() will be handled transparently.

Full-text search

Postgresql provides sophisticated full-text search using special data-types (tsvector and tsquery). Documents should be stored or converted to the tsvector type, and search queries should be converted to tsquery.

For simple cases, you can simply use the Match() function, which will automatically perform the appropriate conversions, and requires no schema changes:

def blog_search(query):
    return Blog.select().where(
        (Blog.status == Blog.STATUS_PUBLISHED) &
        Match(Blog.content, query))

The Match() function will automatically convert the left-hand operand to a tsvector, and the right-hand operand to a tsquery. For better performance, it is recommended you create a GIN index on the column you plan to search:

CREATE INDEX blog_full_text_search ON blog USING gin(to_tsvector(content));

Alternatively, you can use the TSVectorField to maintain a dedicated column for storing tsvector data:

class Blog(Model):
    content = TextField()
    search_content = TSVectorField()

You will need to explicitly convert the incoming text data to tsvector when inserting or updating the search_content field:

content = 'Excellent blog post about peewee ORM.'
blog_entry = Blog.create(
    content=content,
    search_content=fn.to_tsvector(content))

Note

If you are using the TSVectorField, it will automatically be created with a GIN index.

postgres_ext API notes

class PostgresqlExtDatabase(database[, server_side_cursors=False[, register_hstore=True[, …]]])

Identical to PostgresqlDatabase but required in order to support:

• Server-side cursors
• ArrayField
• DateTimeTZField
• JSONField
• BinaryJSONField
• HStoreField
• TSVectorField

If using server_side_cursors, also be sure to wrap your queries with ServerSide().

If you do not wish to use the HStore extension, you can specify register_hstore=False.

Warning

The PostgresqlExtDatabase by default will attempt to register the HSTORE extension. Most distributions and recent versions include this, but in some cases the extension may not be available. If you do not plan to use the HStore features of peewee, you can pass register_hstore=False when initializing your PostgresqlExtDatabase.

ServerSide(select_query)

Wrap the given select query in a transaction, and call it’s iterator() method to avoid caching row instances. In order for the server-side resources to be released, be sure to exhaust the generator (iterate over all the rows).

Usage:

large_query = PageView.select()
for page_view in ServerSide(large_query):
    # Do something interesting.
    pass
# At this point server side resources are released.

class ArrayField([field_class=IntegerField[, dimensions=1]])

Field capable of storing arrays of the provided field_class.

You can store and retrieve lists (or lists-of-lists):

class BlogPost(BaseModel):
    content = TextField()
    tags = ArrayField(CharField)
post = BlogPost(content='awesome', tags=['foo', 'bar', 'baz'])

Additionally, you can use the __getitem__ API to query values or slices in the database:

# Get the first tag on a given blog post.
first_tag = (BlogPost
             .select(BlogPost.tags[0].alias('first_tag'))
             .where(BlogPost.id == 1)
             .dicts()
             .get())
# first_tag = {'first_tag': 'foo'}

Get a slice of values:

# Get the first two tags.
two_tags = (BlogPost
            .select(BlogPost.tags[:2].alias('two'))
            .dicts()
            .get())
# two_tags = {'two': ['foo', 'bar']}

• contains(\items*)

  Parameters:	items – One or more items that must be in the given array field.

  # Get all blog posts that are tagged with both "python" and "django".
  Blog.select().where(Blog.tags.contains('python', 'django'))

• contains_any(\items*)

  Parameters:	items – One or more items to search for in the given array field.

  Like contains(), except will match rows where the array contains any of the given items.

  # Get all blog posts that are tagged with "flask" and/or "django".
  Blog.select().where(Blog.tags.contains_any('flask', 'django'))

class DateTimeTZField(\args, **kwargs*)

A timezone-aware subclass of DateTimeField.

class HStoreField(\args, **kwargs*)

A field for storing and retrieving arbitrary key/value pairs. For details on usage, see hstore support.

• keys()

  Returns the keys for a given row.

  >>> f = House.features
  >>> for h in House.select(House.address, f.keys().alias('keys')):
  ...     print h.address, h.keys
  123 Main St [u'bath', u'garage']

• values()

  Return the values for a given row.

  >>> for h in House.select(House.address, f.values().alias('vals')):
  ...     print h.address, h.vals
  123 Main St [u'2 bath', u'2 cars']

• items()

  Like python’s dict, return the keys and values in a list-of-lists:

  >>> for h in House.select(House.address, f.items().alias('mtx')):
  ...     print h.address, h.mtx
  123 Main St [[u'bath', u'2 bath'], [u'garage', u'2 cars']]

• slice(\args*)

  Return a slice of data given a list of keys.

  >>> f = House.features
  >>> for h in House.select(House.address, f.slice('garage').alias('garage_data')):
  ...     print h.address, h.garage_data
  123 Main St {'garage': '2 cars'}

• exists(key)

  Query for whether the given key exists.

  >>> for h in House.select(House.address, f.exists('garage').alias('has_garage')):
  ...     print h.address, h.has_garage
  123 Main St True
  >>> for h in House.select().where(f.exists('garage')):
  ...     print h.address, h.features['garage'] # <-- just houses w/garage data
  123 Main St 2 cars

• defined(key)

  Query for whether the given key has a value associated with it.

• update(\*data*)

  Perform an atomic update to the keys/values for a given row or rows.

  >>> query = House.update(features=House.features.update(
  ...     sqft=2000,
  ...     year_built=2012))
  >>> query.where(House.id == 1).execute()

• delete(\keys*)

  Delete the provided keys for a given row or rows.

  Note

  We will use an UPDATE query.

  >>> query = House.update(features=House.features.delete(
  ...     'sqft', 'year_built'))
  >>> query.where(House.id == 1).execute()

• contains(value)

  Parameters:	value – Either a dict, a list of keys, or a single key.

  Query rows for the existence of either:

  • a partial dictionary.
  • a list of keys.
  • a single key.

  >>> f = House.features
  >>> House.select().where(f.contains('garage')) # <-- all houses w/garage key
  >>> House.select().where(f.contains(['garage', 'bath'])) # <-- all houses w/garage & bath
  >>> House.select().where(f.contains({'garage': '2 cars'})) # <-- houses w/2-car garage

• contains_any(\keys*)

  Parameters:	keys – One or more keys to search for.

  Query rows for the existince of any key.

class JSONField(dumps=None, \args, **kwargs*)

Field class suitable for storing and querying arbitrary JSON. When using this on a model, set the field’s value to a Python object (either a dict or a list). When you retrieve your value from the database it will be returned as a Python data structure.

Note

You must be using Postgres 9.2 / psycopg2 2.5 or greater.

Note

If you are using Postgres 9.4, strongly consider using the BinaryJSONField instead as it offers better performance and more powerful querying options.

Example model declaration:

db = PostgresqlExtDatabase('my_db')
class APIResponse(Model):
    url = CharField()
    response = JSONField()
    class Meta:
        database = db

Example of storing JSON data:

url = 'http://foo.com/api/resource/'
resp = json.loads(urllib2.urlopen(url).read())
APIResponse.create(url=url, response=resp)
APIResponse.create(url='http://foo.com/baz/', response={'key': 'value'})

To query, use Python’s [] operators to specify nested key or array lookups:

APIResponse.select().where(
    APIResponse.response['key1']['nested-key'] == 'some-value')

To illustrate the use of the [] operators, imagine we have the following data stored in an APIResponse:

{
  "foo": {
    "bar": ["i1", "i2", "i3"],
    "baz": {
      "huey": "mickey",
      "peewee": "nugget"
    }
  }
}

Here are the results of a few queries:

def get_data(expression):
    # Helper function to just retrieve the results of a
    # particular expression.
    query = (APIResponse
             .select(expression.alias('my_data'))
             .dicts()
             .get())
    return query['my_data']
# Accessing the foo -> bar subkey will return a JSON
# representation of the list.
get_data(APIResponse.data['foo']['bar'])
# '["i1", "i2", "i3"]'
# In order to retrieve this list as a Python list,
# we will call .as_json() on the expression.
get_data(APIResponse.data['foo']['bar'].as_json())
# ['i1', 'i2', 'i3']
# Similarly, accessing the foo -> baz subkey will
# return a JSON representation of the dictionary.
get_data(APIResponse.data['foo']['baz'])
# '{"huey": "mickey", "peewee": "nugget"}'
# Again, calling .as_json() will return an actual
# python dictionary.
get_data(APIResponse.data['foo']['baz'].as_json())
# {'huey': 'mickey', 'peewee': 'nugget'}
# When dealing with simple values, either way works as
# you expect.
get_data(APIResponse.data['foo']['bar'][0])
# 'i1'
# Calling .as_json() when the result is a simple value
# will return the same thing as the previous example.
get_data(APIResponse.data['foo']['bar'][0].as_json())
# 'i1'

class BinaryJSONField(dumps=None, \args, **kwargs*)

Store and query arbitrary JSON documents. Data should be stored using normal Python dict and list objects, and when data is returned from the database, it will be returned using dict and list as well.

For examples of basic query operations, see the above code samples for JSONField. The example queries below will use the same APIResponse model described above.

Note

You must be using Postgres 9.4 / psycopg2 2.5 or newer. If you are using Postgres 9.2 or 9.3, you can use the regular JSONField instead.

• contains(other)

  Test whether the given JSON data contains the given JSON fragment or key.

  Example:

  search_fragment = {
      'foo': {'bar': ['i2']}
  }
  query = (APIResponse
           .select()
           .where(APIResponse.data.contains(search_fragment)))
  # If we're searching for a list, the list items do not need to
  # be ordered in a particular way:
  query = (APIResponse
           .select()
           .where(APIResponse.data.contains({
               'foo': {'bar': ['i2', 'i1']}})))

  We can pass in simple keys as well. To find APIResponses that contain the key foo at the top-level:

  APIResponse.select().where(APIResponse.data.contains('foo'))

  We can also search sub-keys using square-brackets:

  APIResponse.select().where(
      APIResponse.data['foo']['bar'].contains(['i2', 'i1']))

• contains_any(\items*)

  Search for the presence of one or more of the given items.

  APIResponse.select().where(
      APIResponse.data.contains_any('foo', 'baz', 'nugget'))

  Like contains(), we can also search sub-keys:

  APIResponse.select().where(
      APIResponse.data['foo']['bar'].contains_any('i2', 'ix'))

• contains_all(\items*)

  Search for the presence of all of the given items.

  APIResponse.select().where(
      APIResponse.data.contains_all('foo'))

  Like contains_any(), we can also search sub-keys:

  APIResponse.select().where(
      APIResponse.data['foo']['bar'].contains_all('i1', 'i2', 'i3'))

• contained_by(other)

  Test whether the given JSON document is contained by (is a subset of) the given JSON document. This method is the inverse of contains().

  big_doc = {
      'foo': {
          'bar': ['i1', 'i2', 'i3'],
          'baz': {
              'huey': 'mickey',
              'peewee': 'nugget',
          }
      },
      'other_key': ['nugget', 'bear', 'kitten'],
  }
  APIResponse.select().where(
      APIResponse.data.contained_by(big_doc))

Match(field, query)

Generate a full-text search expression, automatically converting the left-hand operand to a tsvector, and the right-hand operand to a tsquery.

Example:

def blog_search(query):
    return Blog.select().where(
        (Blog.status == Blog.STATUS_PUBLISHED) &
        Match(Blog.content, query))

class TSVectorField

Field type suitable for storing tsvector data. This field will automatically be created with a GIN index for improved search performance.

Note

Data stored in this field will still need to be manually converted to the tsvector type.

Example usage:

class Blog(Model):
    content = TextField()
    search_content = TSVectorField()
content = 'this is a sample blog entry.'
blog_entry = Blog.create(
    content=content,
    search_content=fn.to_tsvector(content))  # Note `to_tsvector()`.

DataSet

The dataset module contains a high-level API for working with databases modeled after the popular project of the same name. The aims of the dataset module are to provide:

• A simplified API for working with relational data, along the lines of working with JSON.
• An easy way to export relational data as JSON or CSV.
• An easy way to import JSON or CSV data into a relational database.

A minimal data-loading script might look like this:

from playhouse.dataset import DataSet
db = DataSet('sqlite:///:memory:')
table = db['sometable']
table.insert(name='Huey', age=3)
table.insert(name='Mickey', age=5, gender='male')
huey = table.find_one(name='Huey')
print huey
# {'age': 3, 'gender': None, 'id': 1, 'name': 'Huey'}
for obj in table:
    print obj
# {'age': 3, 'gender': None, 'id': 1, 'name': 'Huey'}
# {'age': 5, 'gender': 'male', 'id': 2, 'name': 'Mickey'}

You can export or import data using freeze() and thaw():

# Export table content to the `users.json` file.
db.freeze(table.all(), format='json', filename='users.json')
# Import data from a CSV file into a new table. Columns will be automatically
# created for each field in the CSV file.
new_table = db['stats']
new_table.thaw(format='csv', filename='monthly_stats.csv')

Getting started

DataSet objects are initialized by passing in a database URL of the format dialect://user:password@host/dbname. See the Database URL section for examples of connecting to various databases.

# Create an in-memory SQLite database.
db = DataSet('sqlite:///:memory:')

Storing data

To store data, we must first obtain a reference to a table. If the table does not exist, it will be created automatically:

# Get a table reference, creating the table if it does not exist.
table = db['users']

We can now insert() new rows into the table. If the columns do not exist, they will be created automatically:

table.insert(name='Huey', age=3, color='white')
table.insert(name='Mickey', age=5, gender='male')

To update existing entries in the table, pass in a dictionary containing the new values and filter conditions. The list of columns to use as filters is specified in the columns argument. If no filter columns are specified, then all rows will be updated.

# Update the gender for "Huey".
table.update(name='Huey', gender='male', columns=['name'])
# Update all records. If the column does not exist, it will be created.
table.update(favorite_orm='peewee')

Importing data

To import data from an external source, such as a JSON or CSV file, you can use the thaw() method. By default, new columns will be created for any attributes encountered. If you wish to only populate columns that are already defined on a table, you can pass in strict=True.

# Load data from a JSON file containing a list of objects.
table = dataset['stock_prices']
table.thaw(filename='stocks.json', format='json')
table.all()[:3]
# Might print...
[{'id': 1, 'ticker': 'GOOG', 'price': 703},
 {'id': 2, 'ticker': 'AAPL', 'price': 109},
 {'id': 3, 'ticker': 'AMZN', 'price': 300}]