假设我们有一个带有两个表A,B的PostgreSQL数据库。

table A columns: id, name
table B columns: id, name, array_a

The column array_a in table B contains a variable length array of ids from table A. In SQLAlchemy we have two classes that model those tables, say class A and B.

The following works fine to get all the objects A that are referenced in an object B:

session.query(A).join(B, A.id == func.any(B.array_a)).filter(B.id == <id>).all()

我们如何在B中创建与数组相对应的对象A的关系?使用上面的func.any尝试了列比较器,但是它提示ANY(array_a)不是模型中的列。如上所述指定primaryjoin条件似乎也不能削减它。

最佳答案

这种反模式称为"Jaywalking"; PostgreSQL强大的类型系统使它非常诱人。您应该使用另一个表:

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    name VARCHAR
);

CREATE TABLE table_b (
    id SERIAL PRIMARY KEY,
    name VARCHAR
);

CREATE TABLE a_b (
    a_id INTEGER PRIMARY KEY REFERENCES table_a(id),
    b_id INTEGER PRIMARY KEY REFERENCES table_b(id)
)

哪个被映射:
from sqlalchemy import *
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *

Base = declarative_base()

a_b_table = Table("a_b", Base.metadata,
    Column("a_id", Integer, ForeignKey("table_a.id"), primary_key=True),
    Column("b_id", Integer, ForeignKey("table_b.id"), primary_key=True))

class A(Base):
    __tablename__ = "table_a"
    id = Column(Integer, primary_key=True)
    name = Column(String)

class B(Base):
    __tablename__ = "table_b"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    a_set = relationship(A, secondary=a_b_table, backref="b_set")

例子:
>>> print Query(A).filter(A.b_set.any(B.name == "foo"))
SELECT table_a.id AS table_a_id, table_a.name AS table_a_name
FROM table_a
WHERE EXISTS (SELECT 1
FROM a_b, table_b
WHERE table_a.id = a_b.a_id AND table_b.id = a_b.b_id AND table_b.name = :name_1)

如果您对ARRAY列感到困惑,那么最好的选择是使用“看起来”像适当的关联表的替代选择。
from sqlalchemy import *
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *

Base = declarative_base()


class A(Base):
    __tablename__ = "table_a"
    id = Column(Integer, primary_key=True)
    name = Column(String)

class B(Base):
    __tablename__ = "table_b"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    array_a = Column(postgresql.ARRAY(Integer))

a_b_selectable = select([func.unnest(B.array_a).label("a_id"),
                         B.id.label("b_id")]).alias()

A.b_set = relationship(B, secondary=a_b_selectable,
                          primaryjoin=A.id == a_b_selectable.c.a_id,
                          secondaryjoin=a_b_selectable.c.b_id == B.id,
                          viewonly=True,)

B.a_set = relationship(A, secondary=a_b_selectable,
                          primaryjoin=A.id == a_b_selectable.c.a_id,
                          secondaryjoin=a_b_selectable.c.b_id == B.id,
                          viewonly=True)

这给你:
>>> print Query(A).filter(A.b_set.any(B.name == "foo"))
SELECT table_a.id AS table_a_id, table_a.name AS table_a_name
FROM table_a
WHERE EXISTS (SELECT 1
FROM (SELECT unnest(table_b.array_a) AS a_id, table_b.id AS b_id
FROM table_b) AS anon_1, table_b
WHERE table_a.id = anon_1.a_id AND anon_1.b_id = table_b.id AND table_b.name = :name_1)

显然,由于那里没有真正的表,因此viewonly=True是必需的,并且如果您避免乱走马路,您将无法获得良好的,动态的,客观的优势。

关于python - 与Postgresql ARRAY的SQLAlchemy关系,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/9729381/

10-11 23:02
查看更多