-
-
Save coleifer/f1fc90c7d4938c73951c to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"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