populate a PostgreSQL database with SQLModel

Create models, which are effectively columns in the database (see the SQLModel docs for details; consider sqlmodel (sqlalchemy) cascade in Relationship).

class Club(SQLModel, table=True):
	id: Optional[int] = Field(default=None, primary_key=True)
	name: str
	players: List["Player"] = Relationship(back_populates="club")
 
class Player(SQLModel, table=True):
	id: Optional[int] = Field(default=None, primary_key=True)
	name: str
	foot: str
	quotes: Optional[str] = None
	club_id: Optional[int] = Field(default=None, foreign_key="team.id")
	club: Optional[Club] = Relationship(back_populates="players")

Then, connect to the database and write from JSON records format.

sql_model_objs = [Model(**entry) for entry in json_records]
 
with Session(engine) as session:
	for sql_model_obj in sql_model_objects:
		Model.model_validate(sql_model_obj)
		session.add(sql_model_obj)
	# or session.bulk_save_objects(sql_model_objects)
	session.commit()

If writing from a pandas dataframe, make sure the NaN values are of the correct type. This can be forced with df = df.astype({"quotes": "string"}).

These can be used with FastAPI (with tests).