python - 如何在 sqlalchemy 中映射 postgres EARTH 列

Question

我正在使用 sqlalchemy 和 alembic 进行迁移(和 flask-sqlalchemy)。我有一个使用 EARTH 数据类型的 postgres 表。

CREATE TABLE things
(
  id INTEGER PRIMARY KEY NOT NULL,
  name TEXT,
  earth_location EARTH
)

这是我的 sqlalchemy 映射:

class Thing(db.Model):
    __tablename__ = 'things'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.UnicodeText, nullable=False)
    earth_location = db.Column(???)

如何映射地球柱？

alembic 迁移是否能够处理它？<​​/p>

谢谢!

Answer 1

使用来自 earthdistance extension 的 earth 类型的列使用 SQLAlchemy 是可能的:

• column_property , 如果您只想将纬度和经度值映射到属性
• UserDefinedType , 如果您希望拥有一个支持例如表创建的完整类型

column_property 实现起来有些简单，但仅限于只读，并且如果声明性列不可用，则需要一些非正统的语法:

from sqlalchemy import Float, column, func
from sqlalchemy.orm import column_property

class Thing(db.Model):
    __tablename__ = 'things'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.UnicodeText, nullable=False)

_earth_location = column('earth_location', _selectable=Thing.__table__)

Thing.earth_location_latitude = column_property(func.latitude(
        _earth_location, type_=Float))
Thing.earth_location_longitude = column_property(func.longitude(
        _earth_location, type_=Float))

为了产生column对于正确的 _selectable，必须在类 Thing 创建后将属性添加到类中，因为以声明方式创建的 Table 在期间不可用在类主体中创建。这允许像

这样的查询

session.query(Thing.earth_location_longitude).scalar()

按预期工作。如果没有 _selectable，发出的 SQL 将是:

SELECT longitude(loc)

不幸的是，这使得映射表完全忘记了底层列things.earth_location，因此 Alembic 也不会更明智。 Alembic 中的表创建必须通过执行原始 SQL 字符串来完成。

UserDefinedType 的优点是能够支持表创建。原始 earth 值在 python 上下文中非常无用，因此一些来回使用 ll_to_earth、latitude 和 longitude 函数是必须的。将 UserDefinedType 与 column_property 结合使用可能会提供“两全其美”的解决方案:

from sqlalchemy.types import UserDefinedType
from sqlalchemy.orm import deferred


class EARTH(UserDefinedType):
    def get_col_spec(self, **kw):
        return 'EARTH'


class Thing(db.Model)
    __tablename__ = 'things'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.UnicodeText, nullable=False)
    # defer loading, as the raw value is pretty useless in python
    earth_location = deferred(db.Column(EARTH))
    # A deferred column, aka column_property cannot be adapted, extract the
    # real column. A bit of an ugly hack.
    latitude = column_property(func.latitude(*earth_location.columns,
                                             type_=Float))
    longitude = column_property(func.longitude(*earth_location.columns,
                                               type_=Float))

检查表创建:

In [21]: t = Table('test', meta, Column('loc', EARTH))

In [22]: print(CreateTable(t))                        

CREATE TABLE test (
        loc earth
)

添加一个新的事物:

>>> latitude = 65.012089
>>> longitude = 25.465077
>>> t = Thing(name='some name',
              earth_location=func.ll_to_earth(latitude, longitude))
>>> session.add(t)
>>> session.commit()

请注意，对 ll_to_earth 的绑定(bind)函数调用作为值提供。

一个更复杂的自定义类型支持访问 lat 和 lon 作为属性等是完全可能的，但可能超出了这个 q/a 的范围。