Python - SQLite JSON1 load extension

Question

I want to use the json1 extension for SQLite within Python. According to the official documentation, it should be a loadable extension. I got the json1.c file from the source and compiled it into json1.so as per the official instructions without any errors.

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

The trouble came up when I tried to load the extension in Python 2.7.12 (and 3.5.2) according to the sqlite3 documentation.

>>> import sqlite3
>>> con = sqlite3.connect(":memory:")
>>> con.enable_load_extension(True)
>>> con.load_extension("./json1.so")

I received the following traceback error message. I ran the Python interpreter from the folder with the json1.so file in it. Even though it seems like there should be more information due to the last colon, the following is the complete error message.

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: error during initialization:

Is it actually not possible to use json1 as a loadable extension within Python? Is my only option to recompile SQLite, pysqlite2, etc. as explained in this blog post by Charles Leifer?

EDIT:

As it turns out, I was receiving the error because my machine already had this and other extensions already enabled. The action of enabling an already enabled extension triggered the error. So far, all linux computers I have access to already have the json1 and fts5 extensions enabled in the SQLite that comes with Python. You can check which compile options have been used by connecting to a SQLite database and running the following query.

PRAGMA compile_options;

Answer 1

For anyone who's still trying to figure out how to build the json1 extension from source code, here it is:

After you download the latest release source code from SQLite Source Repository, unzip it, cd into its folder and run ./configure.

Then add the following to the generated Makefile:

json1.dylib: json1.lo  
    $(LTCOMPILE) -c $(TOP)/ext/misc/json1.c  
    $(TCC) -shared -o json1.dylib json1.o  

make is finicky, so be sure $(LTCOMPILE) and $(TCC) are preceded by TAB, not spaces!

Then run make json1.dylib

Reference: https://burrows.svbtle.com/build-sqlite-json1-extension-as-shared-library-on-os-x