如何处理“错误的多对多"交易? SQLAlchemy中的关系? [英] How to deal with "false many-to-many" relationships in SQLAlchemy?

问题描述

我正在使用SQLAlchemy映射数据库，该数据库具有假"多对多关系的多种情况.我的意思是，假设我有以下对象:

I'm mapping a database using SQLAlchemy that have multiple cases of "false" many-to-many relationships. What I mean by this is, suppose I have the following objects:

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    addresses = relationship('Address', secondary='user_address')

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    users = relationship('User', secondary='user_address')

class UserAddressLink(Base):
    __tablename__ = 'user_address'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    address_id = Column(Integer, ForeignKey('address.id'))

那么，一个简单的多对多关系，对吗?但是有一个陷阱:从来没有打算要多对多.实际上，这是一对一的关系，无论出于何种原因，有人都会决定在数据库中设计这样的关系.每个User只有一个Address，反之亦然.我无法控制数据库的设计(实际上，我只从该数据库中读取数据，而从未在其上写入数据)，所以我无法更改它.

So, a simple many-to-many relationship, right? But there's one catch: it was never intended to be many-to-many. This is actually a one-to-one relationship that someone decided to design like this in the database for whatever reason. There's only one Address per User and vice-versa. I have no control over database design (in fact, I'm only reading from this database and never writing on it) so I can't change this.

在SQLAlchemy上是否有处理此问题的标准方法?它会自动假定这是一个多对多关系，并将User.adresses和Address.users视为列表.

Is there a standard way of dealing with this on SQLAlchemy? It automatically assumes that this is a many-to-many relationships and treat the User.adresses and Address.users as lists.

我处理它的方式是创建属性:

The way I'm dealing with it is creating properties:

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    _addresses = relatioship('Address', secondary='user_address')

    @property
    def address(self):
        return self.addresses[0] if len(self.addresses) > 0 else None

    @address.setter
    def address(self, value):
        self.addresses = [value]

以此类推.

这是处理此问题的最佳方法还是有其他解决方法?

Is this the best way to deal with this or is there any other workaround?

推荐答案

有一种非常简单的方法来定义这种关系，方法是使用uselist = False，如

There is a very straightforward way to define such a relationship by using uselist = False as done in One-to-One relationship definition on both side of the relationship:

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    # other columns
    name = Column(String)


class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    # other columns
    name = Column(String)

    # relationship(
    user = relationship(
        User,
        secondary='user_address',
        uselist=False,
        backref=backref('address', uselist=False),
    )

user_address = Table(
    'user_address', Base.metadata,
    Column('id', Integer, primary_key=True),
    Column('use_id', Integer, ForeignKey('user.id')),
    Column('address_id', Integer, ForeignKey('address.id')),
)

然后，您可以根据需要使用代码:

Then you can use the code as you desire to:

# add some data
u1 = User(name='JJ', address=Address(name='superstreet'))
a2 = Address(name='LA')
a2.user = User(name='John')
session.add(u1)
session.add(a2)
session.commit()
session.expunge_all()

# get users and preload addresses as well in one query
q = session.query(User).options(joinedload(User.address))
for u in q.all():
    print(u)
    print("  {}".format(u.address))

关于代码的更多说明:

• 您不应在双方之间都定义关系，只需为此使用backref
• 您不应为user_address表定义整个映射的类，上面的表定义为

• you should not define the relationship on both sides, just use backref for this
• you should not define the whole mapped class for the user_address table, table definition as above is

这篇关于如何处理“错误的多对多"交易? SQLAlchemy中的关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！