将sum()和unest()与sqlalchemy一起使用

Question

我正在试图找出使用sum和unnest的正确语法，但我还没有找到正确的语法，因为当前生成的SQL对PostgreSQL无效：

sqlalchemy.exc.ProgrammingError: (ProgrammingError) could not determine polymorphic type because input has type "unknown"
 'SELECT (SELECT sum(foo) AS sum_1 nFROM unnest(%(unnest_1)s) AS foo) AS anon_1 nFROM products' {'unnest_1': 'stock'}

工作SQL将是：

SELECT (SELECT sum(foo) AS sum_1 FROM unnest(stock) AS foo) AS anon_1 FROM products;

示例测试脚本在这里：

from sqlalchemy import Column, Integer, select
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy import func as F
from sqlalchemy.sql import column
import logging
Base = declarative_base()
db = create_engine('postgresql://scott:tiger@localhost/')
# create a configured "Session" class
Session = sessionmaker(bind=db)
session = Session()
logging.basicConfig()
class P(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    stock = Column(ARRAY(Integer, dimensions=1))
    total = Column(Integer)

    def __init__(self, stock=[]):
        self.stock = stock
        self.total = 0
if __name__ == '__main__':
    Base.metadata.create_all(db)
    session.add_all([P([1, 2]), P([0, 0]), P([0, 2])])
    session.commit()
    _q = select([F.sum(column('foo'))]) 
        .select_from(F.unnest('stock').alias('foo')).as_scalar()
    q = select([_q]).select_from(P.__table__)
    print session.execute(q).fetchall()

Answer 1

stock需要是一列：

F.unnest(P.stock)

您传递了一个字符串绑定参数。