In [2]:
from jqlite import dbapi2 as jqlite
import json
import urllib2
In [3]:
from peewee import *
from playhouse.sqlite_ext import *

class JQLiteDatabase(SqliteExtDatabase):
    def _connect(self, database, **kwargs):
        conn = jqlite.connect(database, **kwargs)
        conn.isolation_level = None
        self._add_conn_hooks(conn)
        return conn
In [4]:
db = JQLiteDatabase(':memory:')
In [5]:
class Entry(Model):
    data = TextField()
    class Meta:
        database = db
In [6]:
Entry.create_table()
In [7]:
fh = urllib2.urlopen('http://media.charlesleifer.com/downloads/misc/blogs.json')
data = json.load(fh)
data[0]
Out[7]:
{u'metadata': {u'tags': [u'python', u'sqlite']},
 u'title': u'My List of Python and SQLite Resources',
 u'url': u'http://charlesleifer.com/blog/my-list-of-python-and-sqlite-resources/'}
In [8]:
with db.atomic():
    for entry in data:
        Entry.create(data=json.dumps(entry))
In [9]:
title = fn.json_extract(Entry.data, '$.title')
query = (Entry
         .select(title.alias('title'))
         .order_by(title)
         .limit(10))
[row for row in query.dicts()]
Out[9]:
[{'title': u'A Tour of Tagging Schemas: Many-to-many, Bitmaps and More'},
 {'title': u'Alternative Redis-Like Databases with Python'},
 {'title': u'Building the SQLite FTS5 Search Extension'},
 {'title': u'Connor Thomas Leifer'},
 {'title': u'Extending SQLite with Python'},
 {'title': u'How to make a Flask blog in one hour or less'},
 {'title': u'Introduction to the fast new UnQLite Python Bindings'},
 {'title': u'Managing Database Connections with Peewee'},
 {'title': u'Meet Scout, a Search Server Powered by SQLite'},
 {'title': u'My List of Python and SQLite Resources'}]
In [10]:
print query.sql()[0]
print query.sql()[1]
SELECT json_extract("t1"."data", ?) AS title FROM "entry" AS t1 ORDER BY json_extract("t1"."data", ?) LIMIT 10
['$.title', '$.title']
In [11]:
from peewee import Entity
tags_src = fn.json_each(Entry.data, '$.metadata.tags')
tags_ref = Entity('tags')

query = (Entry
         .select(title.alias('title'))
         .from_(Entry, tags_src.alias('tags'))
         .where(tags_ref.value == 'sqlite')
         .order_by(title))

[row for row, in query.tuples()]
Out[11]:
[u'Building the SQLite FTS5 Search Extension',
 u'Extending SQLite with Python',
 u'Meet Scout, a Search Server Powered by SQLite',
 u'My List of Python and SQLite Resources',
 u'Querying Tree Structures in SQLite using Python and the Transitive Closure Extension',
 u"Using SQLite4's LSM Storage Engine as a Stand-alone NoSQL Database with Python",
 u'Web-based SQLite Database Browser, powered by Flask and Peewee']
In [12]:
print query.sql()[0]
print query.sql()[1]
SELECT json_extract("t1"."data", ?) AS title FROM "entry" AS t1, json_each("t1"."data", ?) AS tags WHERE ("tags"."value" = ?) ORDER BY json_extract("t1"."data", ?)
['$.title', '$.metadata.tags', 'sqlite', '$.title']
In [13]:
tree = fn.json_tree(Entry.data, '$').alias('tree')
parent = fn.json_tree(Entry.data, '$').alias('parent')

tree_ref = Entity('tree')
parent_ref = Entity('parent')

query = (Entry
         .select(title.alias('title'))
         .from_(Entry, tree, parent)
         .where(
             (tree_ref.parent == parent_ref.id) &
             (parent_ref.key == 'tags') &
             (tree_ref.value == 'sqlite'))
         .order_by(title))

[row for row, in query.tuples()]
Out[13]:
[u'Building the SQLite FTS5 Search Extension',
 u'Extending SQLite with Python',
 u'Meet Scout, a Search Server Powered by SQLite',
 u'My List of Python and SQLite Resources',
 u'Querying Tree Structures in SQLite using Python and the Transitive Closure Extension',
 u"Using SQLite4's LSM Storage Engine as a Stand-alone NoSQL Database with Python",
 u'Web-based SQLite Database Browser, powered by Flask and Peewee']
In [15]:
tags = fn.json_each(Entry.data, '$.metadata.tags').alias('tags')
tags_ref = Entity('tags')

query = (Entry
         .select(title.alias('title'), fn.group_concat(tags_ref.value, ', ').alias('tags'))
         .from_(Entry, tags)
         .group_by(title)
         .limit(10))

[row for row in query.tuples()]
Out[15]:
[(u'A Tour of Tagging Schemas: Many-to-many, Bitmaps and More',
  u'peewee, sql, python'),
 (u'Alternative Redis-Like Databases with Python',
  u'python, walrus, redis, nosql'),
 (u'Building the SQLite FTS5 Search Extension',
  u'sqlite, search, python, peewee'),
 (u'Connor Thomas Leifer', u'thoughts'),
 (u'Extending SQLite with Python', u'peewee, python, sqlite'),
 (u'How to make a Flask blog in one hour or less', u'python, flask, peewee'),
 (u'Introduction to the fast new UnQLite Python Bindings',
  u'nosql, python, unqlite, cython'),
 (u'Managing Database Connections with Peewee', u'peewee, python'),
 (u'Meet Scout, a Search Server Powered by SQLite',
  u'sqlite, peewee, python, search, scout'),
 (u'My List of Python and SQLite Resources', u'python, sqlite')]