[SOLVED] have SQLAlchemy or SQLModel define a database schema starting from an example of the object I would like to persist

have SQLAlchemy or SQLModel define a database schema starting from an example of the object I would like to persist

I would like to save objects like this to a local database.

The object is a dictionary with various entries.

For instance, the first entries are like:

"id": "https://openalex.org/W1562723576",
"doi": "https://doi.org/10.1038/nrg817",
"title": "Genetics and geography of wild cereal domestication in the near east",

How can I have SQLAlchemy or SQLModel define for me a database schema starting from an example of the object I would like to persist?

Given the example above, I would like them to generate a class table with an attribute id typed as string, an attribute title typed as string, an attribute doi typed as string, etc.

In case the value of the dictionary entry is a list of other objects, they should define a 1:m relationship into another table.

Solution

It doesn't use SQLAlchemy, but DuckDB is able to take a JSON array of JSON objects and create a table with an appropriate schema AND populate it with the objects at the same time.

CREATE TABLE todos AS
    SELECT *
    FROM read_json('todos.json');
DESCRIBE todos;

DuckDB Documentation / Data Import / JSON Files / Loading JSON / The read_json Function