Is there a built-in way in SQLite (or similar) to keep the best of both worlds SQL / NoSQL, for small projects, i.e.:
- stored in a (flat) file like SQLite (no client/server scheme, no server to install; more precisely : nothing else to install except
pip install <package>) - possibility to store rows as
dict, without having a common structure for each row, like NoSQL databases - support of simple queries
Example:
db = NoSQLite('test.db')
db.addrow({'name': 'john doe', 'balance': 1000, 'data': [1, 73.23, 18]})
db.addrow({'name': 'alice', 'balance': 2000, 'email': 'a@b.com'})
for row in db.find('balance > 1500'):
print(row)
# {'id': 'f565a9fd3a', 'name': 'alice', 'balance': 2000, 'email': 'a@b.com'} # id was auto-generated
Note: I have constantly been amazed along the years by how many interesting features are in fact possible with SQLite in a few lines of code, that’s why I’m asking if what I describe here could maybe be available simply with SQLite by using only a few SQLite core features.
PS: shelve could look like a solution but in fact it’s just a persistent key/value store, and it doesn’t have query/find functions; also bsddb (BerkeleyDB for Python) looks deprecated and has no query feature with a similar API.
Advertisement
Answer
It’s possible via using the JSON1 extension to query JSON data stored in a column, yes:
sqlite> CREATE TABLE test(data TEXT);
sqlite> INSERT INTO test VALUES ('{"name":"john doe","balance":1000,"data":[1,73.23,18]}');
sqlite> INSERT INTO test VALUES ('{"name":"alice","balance":2000,"email":"a@b.com"}');
sqlite> SELECT * FROM test WHERE json_extract(data, '$.balance') > 1500;
data
--------------------------------------------------
{"name":"alice","balance":2000,"email":"a@b.com"}
If you’re going to be querying the same field a lot, you can make it more efficient by adding an index on the expression:
CREATE INDEX test_idx_balance ON test(json_extract(data, '$.balance'));
will use that index on the above query instead of scanning every single row.