#python sqlite json

I was interested in playing with the JSON extension to SQLite. This allows you to query data stored as JSON in a similar fashion to document storage databases. This excellent article by Charles Leifer goes in depth into this. He compiles all the parts from source. I was wondering could I skip the compilation and just use homebrew? Turns out the answer is yes!

First off I need to make sure my home brew on my OSX (mavericks) install was up to date:

brew upgrade

Next was a quick check of the sqlite3 formula:

brew info sqlite3 sqlite: stable 3.9.2 (bottled) [keg-only] : etc : --with-fts Enable the FTS3 module --with-fts5 Enable the FTS5 module (experimental) --with-json1 : etc : ==> Caveats This formula is keg-only, which means it was not symlinked into /usr/local. OS X provides an older sqlite3. Generally there are no consequences of this for you. If you build your own software and it requires this formula, you'll need to add to your build variables: LDFLAGS: -L/usr/local/opt/sqlite/lib CPPFLAGS: -I/usr/local/opt/sqlit

Looks like json1 and full text index extension are present. Lets install them:

brew install sqlite3 --with-json1 --with-ft5 ==> Downloading https://sqlite.org/2015/sqlite-autoconf-3090200.tar.gz Already downloaded: /Library/Caches/Homebrew/sqlite-3.9.2.tar.gz ==> ./configure --prefix=/usr/local/Cellar/sqlite/3.9.2 --enable-dynamic-extensions ==> make install : etc : Generally there are no consequences of this for you. If you build your own software and it requires this formula, you'll need to add to your build variables: LDFLAGS: -L/usr/local/opt/sqlite/lib CPPFLAGS: -I/usr/local/opt/sqlite/include LDFLAGS: -L/usr/local/opt/sqlite/lib CPPFLAGS: -I/usr/local/opt/sqlite/include ==> Summary 🍺 /usr/local/Cellar/sqlite/3.9.2: 9 files, 3.0M, built in 35 seconds

Now we need to setup the links to sqlite3 libs and includes so pip can build pysqlite and find the extensions. To do this add the following to your .pydistutils.cfg file.

vi ~/.pydistutils.cfg [build_ext] library_dirs = /usr/local/opt/sqlite/lib include_dirs = /usr/local/opt/sqlite/include

Now lets create a virtual env to work out of:

virtualenv ~/sql source ~/sql/bin/activate

Install the python dependancies:

pip install pysqlite peewee Downloading/unpacking pysqlite Downloading pysqlite-2.8.1.tar.gz (79kB): 79kB downloaded Running setup.py (path:/Users/oisin/sql/build/pysqlite/setup.py) egg_info for package pysqlite : etc : Successfully installed pysqlite Cleaning up... Installing collected packages: peewee Running setup.py install for peewee changing mode of build/scripts-2.7/pwiz.py from 644 to 755 changing mode of /Users/oisin/sql/bin/pwiz.py to 755 Successfully installed peewee Cleaning up...

This looks good so far. Now lets create a python script call sql.py from this gist https://gist.github.com/oisinmulvihill/28daaff39add58c6b249. Now using python to run this from out environment you should see:

python ~/sql.py [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']