如何在SQLModel、SQLAlchemy与PostgreSQL中使用POINT数组

问题根源 #

1. 你之前误用了geoalchemy2.Geometry类型，但DDL中a_value是PostgreSQL原生的point[]数组，而非PostGIS的Geometry类型，导致类型不匹配报错。
2. 第二种写法未指定a_value的具体类型，查询时无法正确处理point数组的序列化/反序列化，引发查询异常。

正确的模型定义 #

直接使用SQLAlchemy对PostgreSQL原生POINT类型的支持，结合ARRAY定义数组字段，同时匹配DDL中的可空约束和自增规则：

from sqlmodel import Field, SQLModel
from sqlalchemy.dialects.postgresql import ARRAY, POINT
from typing import Optional
from datetime import datetime


class SiteMetrics(SQLModel, table=True):
    __tablename__ = "site_metrics"  # 显式指定表名，与DDL一致
    
    site_metric_id: Optional[int] = Field(
        default=None,
        primary_key=True,
        sa_column_kwargs={"server_default": "GENERATED BY DEFAULT AS IDENTITY"}  # 匹配DDL的自增规则
    )
    site_id: int = Field(nullable=False)
    metric_id: int = Field(nullable=False)
    created_at: datetime = Field(nullable=False)
    updated_at: datetime = Field(nullable=False)
    n_value: Optional[float] = Field(nullable=True)  # 对应DDL的double precision
    a_value: Optional[list[tuple[float, float]]] = Field(
        sa_type=ARRAY(POINT())  # 定义PostgreSQL point数组类型
    )
    deleted_at: Optional[datetime] = Field(nullable=True)

插入与查询示例 #

插入数据

from sqlmodel import Session, create_engine
from datetime import datetime

engine = create_engine("postgresql://your_user:your_password@your_host:5432/your_db")

with Session(engine) as session:
    new_metric = SiteMetrics(
        site_id=1,
        metric_id=2,
        created_at=datetime.now(),
        updated_at=datetime.now(),
        n_value=2.718,
        a_value=[(116.397, 39.908), (121.473, 31.230)]
    )
    session.add(new_metric)
    session.commit()
    session.refresh(new_metric)
    print(f"插入的记录ID: {new_metric.site_metric_id}")

查询数据

from sqlmodel import select

with Session(engine) as session:
    stmt = select(SiteMetrics).where(SiteMetrics.site_id == 1)
    metrics = session.exec(stmt).all()
    for metric in metrics:
        print(f"站点ID: {metric.site_id}, 坐标列表: {metric.a_value}")

关键说明 #

• 用sqlalchemy.dialects.postgresql.POINT对应PostgreSQL原生point类型，ARRAY(POINT())直接匹配DDL的point[]字段。
• 显式指定字段的nullable属性，与DDL的约束保持一致。
• site_metric_id的server_default参数匹配DDL中的自增规则，确保模型生成的表结构与现有DDL完全一致。

内容的提问来源于stack exchange，提问作者demetere._