Skip to content

Instantly share code, notes, and snippets.

@coleifer
Created Sep 17, 2015
Embed
What would you like to do?
{
"cells": [
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"from jqlite import dbapi2 as jqlite\n",
"import json\n",
"import urllib2"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"from peewee import *\n",
"from playhouse.sqlite_ext import *\n",
"\n",
"class JQLiteDatabase(SqliteExtDatabase):\n",
" def _connect(self, database, **kwargs):\n",
" conn = jqlite.connect(database, **kwargs)\n",
" conn.isolation_level = None\n",
" self._add_conn_hooks(conn)\n",
" return conn"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"db = JQLiteDatabase(':memory:')"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"class Entry(Model):\n",
" data = TextField()\n",
" class Meta:\n",
" database = db"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"Entry.create_table()"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"{u'metadata': {u'tags': [u'python', u'sqlite']},\n",
" u'title': u'My List of Python and SQLite Resources',\n",
" u'url': u'http://charlesleifer.com/blog/my-list-of-python-and-sqlite-resources/'}"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"fh = urllib2.urlopen('http://media.charlesleifer.com/downloads/misc/blogs.json')\n",
"data = json.load(fh)\n",
"data[0]"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"with db.atomic():\n",
" for entry in data:\n",
" Entry.create(data=json.dumps(entry))"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"[{'title': u'A Tour of Tagging Schemas: Many-to-many, Bitmaps and More'},\n",
" {'title': u'Alternative Redis-Like Databases with Python'},\n",
" {'title': u'Building the SQLite FTS5 Search Extension'},\n",
" {'title': u'Connor Thomas Leifer'},\n",
" {'title': u'Extending SQLite with Python'},\n",
" {'title': u'How to make a Flask blog in one hour or less'},\n",
" {'title': u'Introduction to the fast new UnQLite Python Bindings'},\n",
" {'title': u'Managing Database Connections with Peewee'},\n",
" {'title': u'Meet Scout, a Search Server Powered by SQLite'},\n",
" {'title': u'My List of Python and SQLite Resources'}]"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"title = fn.json_extract(Entry.data, '$.title')\n",
"query = (Entry\n",
" .select(title.alias('title'))\n",
" .order_by(title)\n",
" .limit(10))\n",
"[row for row in query.dicts()]"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"SELECT json_extract(\"t1\".\"data\", ?) AS title FROM \"entry\" AS t1 ORDER BY json_extract(\"t1\".\"data\", ?) LIMIT 10\n",
"['$.title', '$.title']\n"
]
}
],
"source": [
"print query.sql()[0]\n",
"print query.sql()[1]"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"[u'Building the SQLite FTS5 Search Extension',\n",
" u'Extending SQLite with Python',\n",
" u'Meet Scout, a Search Server Powered by SQLite',\n",
" u'My List of Python and SQLite Resources',\n",
" u'Querying Tree Structures in SQLite using Python and the Transitive Closure Extension',\n",
" u\"Using SQLite4's LSM Storage Engine as a Stand-alone NoSQL Database with Python\",\n",
" u'Web-based SQLite Database Browser, powered by Flask and Peewee']"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"from peewee import Entity\n",
"tags_src = fn.json_each(Entry.data, '$.metadata.tags')\n",
"tags_ref = Entity('tags')\n",
"\n",
"query = (Entry\n",
" .select(title.alias('title'))\n",
" .from_(Entry, tags_src.alias('tags'))\n",
" .where(tags_ref.value == 'sqlite')\n",
" .order_by(title))\n",
"\n",
"[row for row, in query.tuples()]"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"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\", ?)\n",
"['$.title', '$.metadata.tags', 'sqlite', '$.title']\n"
]
}
],
"source": [
"print query.sql()[0]\n",
"print query.sql()[1]"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"[u'Building the SQLite FTS5 Search Extension',\n",
" u'Extending SQLite with Python',\n",
" u'Meet Scout, a Search Server Powered by SQLite',\n",
" u'My List of Python and SQLite Resources',\n",
" u'Querying Tree Structures in SQLite using Python and the Transitive Closure Extension',\n",
" u\"Using SQLite4's LSM Storage Engine as a Stand-alone NoSQL Database with Python\",\n",
" u'Web-based SQLite Database Browser, powered by Flask and Peewee']"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"tree = fn.json_tree(Entry.data, '$').alias('tree')\n",
"parent = fn.json_tree(Entry.data, '$').alias('parent')\n",
"\n",
"tree_ref = Entity('tree')\n",
"parent_ref = Entity('parent')\n",
"\n",
"query = (Entry\n",
" .select(title.alias('title'))\n",
" .from_(Entry, tree, parent)\n",
" .where(\n",
" (tree_ref.parent == parent_ref.id) &\n",
" (parent_ref.key == 'tags') &\n",
" (tree_ref.value == 'sqlite'))\n",
" .order_by(title))\n",
"\n",
"[row for row, in query.tuples()]"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"[(u'A Tour of Tagging Schemas: Many-to-many, Bitmaps and More',\n",
" u'peewee, sql, python'),\n",
" (u'Alternative Redis-Like Databases with Python',\n",
" u'python, walrus, redis, nosql'),\n",
" (u'Building the SQLite FTS5 Search Extension',\n",
" u'sqlite, search, python, peewee'),\n",
" (u'Connor Thomas Leifer', u'thoughts'),\n",
" (u'Extending SQLite with Python', u'peewee, python, sqlite'),\n",
" (u'How to make a Flask blog in one hour or less', u'python, flask, peewee'),\n",
" (u'Introduction to the fast new UnQLite Python Bindings',\n",
" u'nosql, python, unqlite, cython'),\n",
" (u'Managing Database Connections with Peewee', u'peewee, python'),\n",
" (u'Meet Scout, a Search Server Powered by SQLite',\n",
" u'sqlite, peewee, python, search, scout'),\n",
" (u'My List of Python and SQLite Resources', u'python, sqlite')]"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"tags = fn.json_each(Entry.data, '$.metadata.tags').alias('tags')\n",
"tags_ref = Entity('tags')\n",
"\n",
"query = (Entry\n",
" .select(title.alias('title'), fn.group_concat(tags_ref.value, ', ').alias('tags'))\n",
" .from_(Entry, tags)\n",
" .group_by(title)\n",
" .limit(10))\n",
"\n",
"[row for row in query.tuples()]"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 2",
"language": "python",
"name": "python2"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 2
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython2",
"version": "2.7.6"
}
},
"nbformat": 4,
"nbformat_minor": 0
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment