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

you can run sqlite with python 3. here is what worked for me on my mac:

first compile the loadable extension:

curl -O http://sqlite.org/2016/sqlite-src-3140100.zip 

unzip sqlite-src-3140100.zip

gcc -g -fPIC -dynamiclib sqlite-src-3140100/ext/misc/json1.c -o json1

then use it in a script:

import sqlite3
conn = sqlite3.connect('testingjson.db')

#load precompiled json1 extension
conn.enable_load_extension(True)
conn.load_extension("./json1")

# create a cursor
c = conn.cursor()

# make a table
# create table NAME_OF_TABLE (NAME_OF_FIELD TYPE_OF_FIELD);
c.execute('create table testtabledos (testfield JSON);')

# Insert a row of data into a table
c.execute("insert into testtabledos (testfield) values (json('{\"json1\": \"works\"}'));")

# Save (commit) the changes
conn.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()

or in a shell:

.load json1
CREATE TABLE test_table (id INTEGER, json_field JSON);
# insert data into test table
insert into test_table (id, json_field) values (1, json('{"name":"yvan"}'));
insert into test_table (id, json_field) values (2, json('{"name":"sara"}'));
#select json objects from the json column
select * from test_table where json_extract("json_field", '$.name') is not null;
1|{"name":"yvan"}
2|{"name":"sara"}

i wish this was easier. it seems like loading extensions (rather than building them into sqlite on creation) makes a lot more sense. my latest issue is that i cant seem to compile the json1 extension on CentOS 6.

i wrote a guide here: https://github.com/SMAPPNYU/smapphowto/blob/master/howto_get_going_with_sqlite_json1.md

EDIT: i eventually gave up on json1 for my purposes. i now just use pysmap dump_to_csv to column based csv by extracting the fields i want, and then dump_to_sqlite_db create a normal sqlite db from that csv. see pysmap smapp_collection

Answer 2

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