FastAPI Developer Help

I have an inventory system backend using SQLAlchemy FastAPI PostgreSQL.

I am working on storing sub site locations for every site.



Zone / Aisle / Rack / Shelf / Bin

Here is the models I am using:



class Site(Base):

__tablename__ = "sites"

__mapper_args__ = {"eager_defaults": True}

id = Column(

Integer,

Identity(start=100_000),

primary_key=True,

index=True,

autoincrement=True,

)

name = Column(String, unique=True, index=True)

description = Column(String)

addressLine1 = Column(String)



class Site_Location(Base):

__tablename__ = "site_locations"

__mapper_args__ = {"eager_defaults": True}

__table_args__ = (

UniqueConstraint("name", "parentID", "siteID", name="UC_name_parentID_siteID"),

)

id = Column(

Integer,

Identity(start=100_000),

primary_key=True,

index=True,

autoincrement=True,

)

name = Column(String)

description = Column(String)

level = Column(Integer)

pathstring = Column(String)

parentID = Column(Integer, ForeignKey("site_locations.id"))

siteID = Column(Integer, ForeignKey("sites.id"))





I need to be able to read, create, update and delete site locations.

I need the level and path string to be computed. Currently I have it working but can not edit. I am calculating the level and path string on create.



I would like the json output to be similar to the following:

[

{

"id": 1,

"name": "B1",

"description": "Bin 1",

"parentID": 5,

"level": 4,

"pathstring": "4th - Storage Room/A1/R1/S1/B1",

"siteID": 2,

"site": {

"name": "Main"

}

},

{

"id": 2,

"name": "B2",

"description": "Bin 2",

"parentID": 5,

"level": 4,

"pathstring": "4th - Storage Room/A1/R1/S1/B2",

"siteID": 2,

"site": {

"name": "Main"

}

}

]



I am open for other suggestions but need the path string so I can display it correctly on the front end. I have tired these and could not get it working for myself.

https://www.kite.com/blog/python/sqlalchemy/

https://docs.sqlalchemy.org/en/13/_modules/examples/materialized_paths/materialized_paths.html



Please let me know if you have questions or would like more details. I need this to be completed as soon as possible.