I have just started working with SQLModel which is built on top of the SQLAlchemy. I want to create a Class Model for table to do selects and inserts. I have one column that is an Array of Points. The DDL for that table looks like that:
CREATE TABLE "public"."site_metrics" (
"site_metric_id" integer DEFAULT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
"site_id" integer NOT NULL,
"metric_id" integer NOT NULL,
"created_at" timestamp NOT NULL,
"updated_at" timestamp NOT NULL,
"n_value" double precision,
"a_value" point[],
"deleted_at" timestamp,
CONSTRAINT "PK_f30bdeddd128eea8b65c72b3653" PRIMARY KEY ("site_metric_id")
)
now I want to create a model in SQLModel that will represent that table, I have found some threads that uses geoalchemy for creating Point type of columns but I have not seen using them with SQLModel or with arrays. So I have tried this by looking all examples by far:
from sqlmodel import Field, SQLModel, ARRAY
from geoalchemy2 import Geometry
from datetime import datetime
class SiteMetrics(SQLModel, table=True):
site_metric_id: Optional[int] = Field(default=None, primary_key=True)
site_id: int
metric_id: int
created_at: datetime
updated_at: datetime
n_value: float
a_value: ARRAY(Geometry(geometry_type='POINT'))
deleted_at: datetime
but when I try this it thors this error:
error checking inheritance of ARRAY(Geometry(geometry_type='POINT', from_text='ST_GeomFromEWKT', name='geometry')) (type: ARRAY)
is there anything else I can do? I am kinda stuck on that
UPDATE I also tried this, with alchemy schemas:
from sqlmodel import Field, Session, SQLModel, create_engine, select, ARRAY, Integer, String, FLOAT
from geoalchemy2 import Geometry
from typing import Optional
from datetime import datetime
from urllib.parse import quote_plus
from typing import List, Optional, Set
from sqlalchemy.sql.schema import Column
class Site_Metrics(SQLModel, table=True):
site_metric_id: Optional[int] = Field(default=None, primary_key=True)
site_id: int
metric_id: int
created_at: datetime
updated_at: datetime
n_value: float
a_value: Optional[List] = Field(default_factory=list, sa_column=Column(ARRAY(Geometry('POINT'))))
deleted_at: datetime
With this code, I was able to at least create the class and now when I try to select. Now I am thinking about creating Type on my own, lets see if I will be able to