Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Flat file NoSQL solution [closed]

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': '[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.

like image 395
Basj Avatar asked Apr 07 '20 19:04

Basj


People also ask

Is NoSQL a flat file database?

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.

Is MongoDB a flat file?

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.

Does SQLite support NoSQL?

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.

How does extracting data to a flat file work?

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.


Video Answer


2 Answers

SQLite

  • 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

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 :)

Oher solutions

Seems interesting too : WhiteDB

like image 102
Basj Avatar answered Oct 23 '22 05:10

Basj


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.

like image 34
Shawn Avatar answered Oct 23 '22 04:10

Shawn