Is there a built-in way in SQLite (or similar) to keep the best of both worlds SQL / NoSQL, for small projects, i.e.:
pip install <package>
)dict
, without having a common structure for each row, like NoSQL databasesExample:
db = NoSQLite('test.db')
db.addrow({'name': 'john doe', 'balance': 1000, 'data': [1, 73.23, 18]})
db.addrow({'name': 'alice', 'balance': 2000, 'email': '[email protected]'})
for row in db.find('balance > 1500'):
print(row)
# {'id': 'f565a9fd3a', 'name': 'alice', 'balance': 2000, 'email': '[email protected]'} # 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.
a relational database. It's helpful to think of NoSQL as a flat file storage system where the filename is the key and the file contents are the value. You can store whatever you want in these files and you can read/write to them very quickly, but there are no brains behind the storage.
MongoDB stores data in flat files using their own binary storage objects. This means that data storage is very compact and efficient, perfect for high data volumes. MongoDB stores data in JSON-like documents, which makes the database very flexible and scalable. MongoDB is a document-oriented database model.
SQLite has a backend which is well suited as a key-value store. Here is a NoSql database based on the SQLite backend: https://github.com/rochus-keller/Udb. I use it in many of my apps, e.g. https://github.com/rochus-keller/CrossLine.
The data is arranged in rows -- or records -- across columns or fields. Each row contains the same type of information as the other rows in the flat file; that information is defined by the columns which describe the type of data and sets a limit on the number of characters allowed to represent the field information.
JSON1
extension and json_extract
(see accepted answer). Example:
import sqlite3, json # tested with precompiled Windows binaries from https://www.sqlite.org/download.html (sqlite3.dll copied in C:\Python37\DLLs)
class sqlitenosql:
def __init__(self, f):
self.db = sqlite3.connect(f)
self.db.execute('CREATE TABLE test(data TEXT);')
def close(self):
self.db.commit()
self.db.close()
def addrow(self, d):
self.db.execute("INSERT INTO test VALUES (?);", (json.dumps(d),))
def find(self, query):
for k, v in query.items():
if isinstance(v, str):
query[k] = f"'{v}'"
q = ' AND '.join(f" json_extract(data, '$.{k}') = {v}" for k, v in query.items())
for r in self.db.execute(f"SELECT * FROM test WHERE {q}"):
yield r[0]
db = sqlitenosql(':memory:')
db.addrow({'name': 'john', 'balance': 1000, 'data': [1, 73.23, 18], 'abc': 'hello'})
db.addrow({'name': 'alice', 'balance': 2000, 'email': '[email protected]'})
db.addrow({'name': 'bob', 'balance': 1000})
db.addrow({'name': 'richard', 'balance': 1000, 'abc': 'hello'})
for r in db.find({'balance': 1000, 'abc': 'hello'}):
print(r)
# {"name": "john", "balance": 1000, "data": [1, 73.23, 18], "abc": "hello"}
# {"name": "richard", "balance": 1000, "abc": "hello"}
db.close()
sqlitedict as mentioned in Key: value store in Python for possibly 100 GB of data, without client/server and Use SQLite as a key:value store with:
key = an ID
value = the dict we want to store, e.g. {'name': 'alice', 'balance': 2000, 'email': '[email protected]'}
Further reading about use of SQLite with JSON: https://community.esri.com/groups/appstudio/blog/2018/08/21/working-with-json-in-sqlite-databases
TinyDB looks like a good solution:
>>> from tinydb import TinyDB, Query
>>> db = TinyDB('path/to/db.json')
>>> User = Query()
>>> db.insert({'name': 'John', 'age': 22})
>>> db.search(User.name == 'John')
[{'name': 'John', 'age': 22}]
However, the documentation mentions that it's not the right tool if we need:
- access from multiple processes or threads,
- creating indexes for tables,
- an HTTP server,
- managing relationships between tables or similar,
- ACID guarantees
So it's a half solution :)
Seems interesting too : WhiteDB
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":"[email protected]"}');
sqlite> SELECT * FROM test WHERE json_extract(data, '$.balance') > 1500;
data
--------------------------------------------------
{"name":"alice","balance":2000,"email":"[email protected]"}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With