Is there a simple way to insert the Table with Data I've pulled from a json file?

[kopitb]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import Optional

from sqlmodel import Field, SQLModel, create_engine
from typing_extensions import Literal

class Json2ModelsTest(SQLModel, table=True):
    version: Literal["1.0.0"] = Field(..., alias="Version")
    disclaimer: Literal[""] = Field(..., alias="Disclaimer")
    export_id: str = Field(..., alias="ExportId")
    export_date: str = Field(..., alias="ExportDate")
    export_user: Literal[""] = Field(..., alias="ExportUser")

engine = create_engine('postgresql://.......', echo=True)

SQLModel.metadata.create_all(engine)

Original Json file:
{
  "Version": "1.0.0",
  "Disclaimer": "",
  "ExportId": "fcab4412-837e-4436-86ca-eb1e92970139",
  "ExportDate": "2023-04-13T21:24:47.417563Z",
  "ExportUser": "",
  "Model": {
    "ModelId": "5a3e5cb4-95c8-4dfd-9399-b774eedbd2c0"}
}

Description

Hello! I'm trying to use SQLModel to write data into a PostreSQL Database.

I originally used json2python-models to transform my large json file into SQLModel classes. From those classes I want to insert them into a database.

Is this possible?

Thank you!
Brent

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10.9

Additional Context

No response

[gamesbook]

Create a file called loader.py in the same folder as your main.py. Below is an example of how to load data from a JSON data file, based on the "hero" app in the SQLModel tutorial.

"""
Run this file from outside of src folder:

    python src/loader.py
"""
import json
from sqlmodel import Session
from main import Hero, engine


'''
#Assume a JSON file - heroes.json  - with example data, has beeb saved in src folder:
[
  {
     "name": "Sgt.England",
     "secret_name": "John Jones"
  },
  {
     "name": "Rusty-Man",
     "secret_name": "Tommy Sharp",
     "age": 48
  }
]
'''

def loader(filename: str = 'heroes.json'):
    with open('src/'+filename) as _file:
        file_contents = _file.read()
    records = json.loads(file_contents)
    for record in records:
        with Session(engine) as session:
            new_hero = Hero(
                name=record.get('name'),
                secret_name=record.get('secret_name'),
                age=record.get('age'),
            )
            session.add(new_hero)
            session.commit()


if __name__ == "__main__":
     loader()

[kopitb]

Thank you for this @gamesbook

The reason for trying to start with a SQLModel, is because I need to parse a heavily nested json file, and I'm concerned that in your example it would be a lot of work to define the variables such as "name"

Any thoughts?

[gamesbook]

In my example, "name" is not a variable; its the field in the model. You will need to know the mapping between the fields and the values in the JSON file. Your JSON file example does not show any heavy nesting.

[talongao]

maybe you can try Json2ModelsTest.model_validate({})