Query() in TinyDB with multiple files

Question

I use Python + TinyDB, I have 2 json files, and I create 2 TinyDB objects:

db = TinyDB(“db.json”)
forum = TinyDB(“forum.json”)

But how do I use Query()? How can I code that Query() will only check db.json or vice versa?

Answer 1

A "Query" in tiny db is a special Python object that override operators, so that when used they can be used by tinyDB engine in the "search" method to compare values in all records it can reach.

It is made so that is not attached to any specific tinyDB object ("database"): it just binds any operands and operations to be performed on a subsequent search.

It is the search that must be the .search method of a specific database or tinyDB object.

So, if you have two databases where you want to perform the same query, just do that, calling the .search method in one, and then on the other.

But if you want to somehow have a search that depends on the two datasets, like a relational search: tinyDB does not do that. it is meant to be simple, and do not correlate data.

If you need that, make a quick script to dump your tinyDB contents into SQLite databases, and then use plain SQL (or an ORM, lightweight or not), to do the query.

Dumping a tinyDB base to a sqlite can be as simple as:

from tinydb import TinyDB
import sqlite3



def get_schema_from_db(db):
    schema = {}
    for record in db.all():
        for key, value in record.items():
            schema.setdefault(key, set()).add(type(value))
    return {key:('REAL' if float in v else 'INTEGER' if int in v else 'TEXT' if str in v else "BLOB") for key, v in schema.items()}


def create_sql_table(sqldb, name, schema):
    createsql = f"CREATE TABLE IF NOT EXISTS {name} (id INTEGER PRIMARY KEY, {{}})".format(", ".join(f"{key} {value}" for key, value in schema.items() ) )
    sqldb.execute(createsql)

def transfer(tydb, sqldb, schema, tablename):
    insertsql = f"INSERT INTO {tablename} ({', '.join(schema.keys())})  VALUES ({', '.join('?' for _ in range(len(schema)))})"
    sqldb.executemany(insertsql, [tuple(rec.values()) for rec in tydb.all()])

# And to use these 3 functions:

tydb = TinyDB("db.json")
sqldb = sqlite3.connect("sql.db")

schema = get_schema_from_db(tydb)
create_sql_table(sqldb, "mytable", schema)
transfer(tydb, sqldb, schema, "mytable")

Sorry if the code above looks too complicated: it is basically string manipulation to arrive at the final SQL forms - it is "complicated" but not "complex" - rather, quite straightforward.

After that you have your tinyDB data in SQL form and can post queries joining different tables and what not.