使用SQLAlchemy在Postgres中访问复合数据类型

Question

我试图从老虎地理代码函数在python中使用sqlalchemy。
在纯sql形式下，如下所示：

SELECT   
    g.rating  
    ,ST_X(g.geomout) As lon  
    ,ST_Y(g.geomout) As lat  
    ,(addy).address As stno  
    ,(addy).streetname As street  
    ,(addy).streettypeabbrev As styp  
    ,(addy).location As city  
    ,(addy).stateabbrev As st  
    ,(addy).zip  
FROM geocode(pagc_normalize_address('1 Capitol Square Columbus OH 43215')) As g  
;

这将产生以下输出：

^{pr2}$

我面临的问题是当从sqlalchemy（rating，lon，lat，stno，street，stip，city，st，zip）查询对象时，如何引用复合列？在

谢谢你。在

Answer 1

SQLAlchemy不直接支持set返回函数，但是它的FunctionElement被认为是FromClause，这意味着您已经可以将它们视为表；我们只需要添加从函数中选择特定列的功能。幸运的是，这很简单（虽然不明显）：

from sqlalchemy.sql.base import ColumnCollection
from sqlalchemy.sql.expression import column
from sqlalchemy.sql.functions import FunctionElement

NormAddy = CompositeType(
    "norm_addy",
    [
        Column("address", Integer),
        Column("predirAbbrev", String),
        Column("streetName", String),
        Column("streetTypeAbbrev", String),
        Column("postdirAbbrev", String),
        Column("internal", String),
        Column("location", String),
        Column("stateAbbrev", String),
        Column("zip", String),
        Column("parsed", Boolean),
    ],
)

class geocode(GenericFunction):
    columns = ColumnCollection(
        Column("rating", Integer),
        column("geomout"),  # lowercase column because we don't have the `geometry` type
        Column("addy", NormAddy),
    )

来自GenericFunction的子类化还有一个额外的好处，即全局注册geocode函数，这样func.geocode将按预期工作。在

^{pr2}$

不幸的是，这不太管用。似乎有一个bug使g.c.addy.address语法在最近版本的SQLAlchemy上不起作用。我们可以很快修复它（虽然这应该在sqlalchemy\u utils中真正修复）：

from sqlalchemy_utils.types.pg_composite import CompositeElement
import sqlalchemy_utils

class CompositeType(sqlalchemy_utils.CompositeType):
    class comparator_factory(_CompositeType.comparator_factory):
        def __getattr__(self, key):
            try:
                type_ = self.type.typemap[key]
            except KeyError:
                raise AttributeError(key)
            return CompositeElement(self.expr, key, type_)

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.typemap = {c.name: c.type for c in self.columns}

现在它起作用了：

print(query.statement.compile(engine))
# SELECT g.rating, ST_X(g.geomout) AS lon, ST_Y(g.geomout) AS lat, (g.addy).address AS stno, (g.addy).streetName AS street, (g.addy).streetTypeAbbrev AS styp, (g.addy).location AS city, (g.addy).stateAbbrev AS st, (g.addy).zip AS zip_1 
# FROM geocode(pagc_normalize_address(%(pagc_normalize_address_1)s)) AS g