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

Question

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.

Put what is needed to ask (& only that) in your post. If you quote, give always-visible credit & format as a quote. Make clear what it has to do with your question. If a link is not necessary to understand the post, make that clear. What is "this"? What is it "like"? PS Ask exactly 1 (specific researched non-duplicate) question. PS Yes or no questions are seldom useful & seldom ask what the asker actually wants answered. — philipxy
– philipxy, Commented May 29 at 3:17
@defalt because there are a lot of them and I frequently find my self in this situation, of having to save in a database a JSON object like that downloaded from the internet, so I would like the software to do it for me — robertspierre
– robertspierre, Commented May 29 at 9:15

robertspierre · Accepted Answer · 2025-07-05 11:05:21Z

0

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;

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

edited Jul 5 at 11:05

answered Jun 17 at 12:38

robertspierre

5,3653 gold badges43 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related