0

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

demetere._
  • 268
  • 2
  • 10

0 Answers0