Python Download Extension - SQLite JSON1

Question

I want to use the json1 extension for SQLite in Python. According to official documentation , this should be a downloadable extension. I got the json1.c file from the source and compiled it to json1.so according to the official instructions without errors.

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

The problem arose when I tried to download the extension in Python 2.7.12 (and 3.5.2) according to sqlite3 documentation.

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

I received the following trace error message. I started the Python interpreter from the folder with the json1.so file. Although there seems to be more information due to the last colon, the following error message is complete.

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

Is it really not possible to use json1 as a loadable extension in Python? I am the only option to recompile SQLite, pysqlite2, etc., As described in this blog post by Charles Leifer?

EDIT:

As it turned out, I was getting an error because my car already had this and other extensions are already included. An error activating an activated extension caused an error. So far, all Linux computers that I have access to already have the json1 and fts5 extensions included in SQLite, which comes with Python. You can check what compilation options were used when connecting to the SQLite database and executing the following query.

 PRAGMA compile_options; 

Answer 1

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

compile the download extension first:

 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 the 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 the 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 would like it to be easier. It looks like boot extensions (rather than creating them in sqlite when creating) make much more sense. my last problem is that I cannot 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 abandoned json1 for my purposes. Now I just use pysmap dump_to_csv for the csv column, extracting the required fields, and then dump_to_sqlite_db to create a normal sqlite db from this csv. see pysmap smapp_collection