在postgres中使用sqlalchemy访问复合数据类型
问题描述:
我想从python中的tiger.geocode函数中使用sqlalchemy提取组合列。
纯SQL形式,这看起来像这样:在postgres中使用sqlalchemy访问复合数据类型
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
;
这将产生以下输出:
# rating lon lat stno street styp city st zip
1 17 -82.99782603089086 39.96172588526335 1 Capital St Columbus OH 43215
我面对的问题是如何引用组合柱查询从SQLAlchemy的对象时(评分,lon,lat,stno,street,styp,city,st,zip)?
请和谢谢。
答
SQLAlchemy不支持直接设置返回函数,但其FunctionElement
s被认为是FromClause
s,这意味着您已经可以将它们视为表;我们只需要添加从函数中选择特定列的功能。幸运的是,这是简单的(虽然不是很明显):从GenericFunction
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),
)
子类在全球拥有注册geocode
功能,以便为预期func.geocode
将工作的额外好处。
g = func.geocode(func.pagc_normalize_address("1 Capitol Square Columbus OH 43215")).alias("g")
query = session.query(
g.c.rating,
func.ST_X(g.c.geomout).label("lon"),
func.ST_Y(g.c.geomout).label("lat"),
g.c.addy.address.label("stno"),
g.c.addy.streetName.label("street"),
g.c.addy.streetTypeAbbrev.label("styp"),
g.c.addy.location.label("city"),
g.c.addy.stateAbbrev.label("st"),
g.c.addy.zip,
).select_from(g)
不幸的是,这并不奏效。似乎有一个错误,使得g.c.addy.address
语法不适用于最新版本的SQLAlchemy。我们可以修复它真正的快速(虽然这应该真正固定在sqlalchemy_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
你有没有看着['sqlalchemy_utils.types.pg_composite'(HTTP: //sqlalchemy-utils.readthedocs.io/en/latest/data_types.html#module-sqlalchemy_utils.types.pg_composite)? – univerio
我可以看到,如果我的复合类型是一个静态表上的列的情况下,将如何工作,但在我的情况下,它是一个函数返回的复合类型。在我的设置中,我们没有为任何函数(仅限于表格)定义sqlalchemy类。对这部分问题有什么想法? – BrianEdwardHoover