Flask-SQLAlchemy 多对多关系

Question

我之前用的是Django，现在在理解多对多关系时遇到了困难。

下面这个models.py代码有问题，我收到的错误是：InvalidRequestError: 一个或多个映射器初始化失败 - 无法继续初始化其他映射器。 原始异常是：在关系'Sector.companies'上创建反向引用'sectors'时出错：该名称的属性在映射器'Mapper|Company|companies'上已存在。

代码如下：

`    
company_contacts = db.Table('company_contacts',
                       db.Column('company_id', db.Integer, db.ForeignKey('companies.id')),
                       db.Column('contact_id', db.Integer, db.ForeignKey('contacts.id'))
                       )


company_sectors = db.Table('company_sectors',
                   db.Column('company_id', db.Integer, db.ForeignKey('companies.id')),
                   db.Column('sector_id', db.Integer, db.ForeignKey('sectors.id'))
                   )

company_worklists = db.Table('company_worklists',
                   db.Column('company_id', db.Integer, db.ForeignKey('companies.id')),
                   db.Column('worklist_id', db.Integer, db.ForeignKey('worklists.id'))
                   )


class Sector(db.Model):

    __tablename__ = 'sectors'
    id = db.Column(db.Integer, primary_key = True)
    name_srb= db.Column(db.String(64), unique = True)
    name_ita= db.Column(db.String(64), unique = True)

    companies = db.relationship('Company',
                           secondary = company_sectors,
                           backref = db.backref('sectors', lazy = 'dynamic'),
                           lazy = 'dynamic')



class Contact(db.Model):

    __tablename__ = 'contacts'
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(64), unique = False)
    last_name = db.Column(db.String(64), unique = False)
    position_srb = db.Column(db.String(64), unique = False)
    position_ita = db.Column(db.String(64), unique = False)
    email = db.Column(db.String(120), unique = True)
    phone = db.Column(db.String(100))
    created = db.Column(db.DateTime(), default=datetime.now)
    updated = db.Column(db.DateTime(), default=datetime.now,onupdate=datetime.now)

    #companies = db.relationship('Company',
                           #secondary = company_contacts,
                           #backref = db.backref('companies', lazy = 'dynamic'),
                           #lazy = 'dynamic')



class WorkList(db.Model):

    __tablename__ = 'worklists'
    id = db.Column(db.Integer, primary_key = True)
    title = db.Column(db.String(64), unique = True)
    created = db.Column(db.DateTime(), default = datetime.now)
    updated = db.Column(db.DateTime(), default=datetime.now,onupdate=datetime.now)
    slug =  db.Column(db.String(100), unique = True)


class Company(db.Model):

    __tablename__ = 'companies'
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(64), unique = True)
    address = db.Column(db.String(120), unique = False)
    website = db.Column(db.String(100), unique = False)
    city = db.Column(db.String(20), unique = False)
    desc = db.Column(db.Text())
    email = db.Column(db.String(120), unique = True)
    created = db.Column(db.DateTime(), default=datetime.now)
    updated = db.Column(db.DateTime(), default=datetime.now,onupdate=datetime.now)
    slug =  db.Column(db.String(100), unique = True)
    phone = db.Column(db.String(100))
    fax = db.Column(db.String(100), unique = False)



    contacts = db.relationship('Contact',
                           secondary = company_contacts,
                           backref = db.backref('contacts', lazy = 'dynamic'),
                           lazy = 'dynamic')

    sectors = db.relationship('Sector',
                           secondary = company_sectors,
                           backref = db.backref('sectors', lazy = 'dynamic'),
                           lazy = 'dynamic')


    wlists= db.relationship('WorkList',
                           secondary = company_worklists,
                           backref = db.backref('wlists', lazy = 'dynamic'),
                           lazy = 'dynamic')

我想能够访问像Company.contacts和Contact.companies这样的内容...

Answer 1

IfLoop的回答是对的，但你还应该在关联表的定义中加上metadata参数。我把你的例子改了一下，让它能运行：

#!/bin/python

from datetime import datetime
from sqlalchemy import Table
from sqlalchemy import Integer, Text, String, ForeignKey, create_engine, Column, PrimaryKeyConstraint, DateTime
from sqlalchemy.orm import relationship, backref, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()

company_contacts = Table('company_contacts', Base.metadata, Column('company_id', Integer, ForeignKey('companies.id')), Column('contact_id', Integer, ForeignKey('contacts.id')) )

company_sectors = Table('company_sectors',
                    Base.metadata,
                    Column('company_id', Integer, ForeignKey('companies.id')),
                    Column('sector_id', Integer, ForeignKey('sectors.id'))
                   )

company_worklists = Table('company_worklists',
                    Base.metadata,
                   Column('company_id', Integer, ForeignKey('companies.id')),
                   Column('worklist_id', Integer, ForeignKey('worklists.id'))
                   )


class Sector(Base):

    __tablename__ = 'sectors'
    id = Column(Integer, primary_key = True)
    name_srb= Column(String(64), unique = True)
    name_ita= Column(String(64), unique = True)



class Contact(Base):

    __tablename__ = 'contacts'
    id = Column(Integer, primary_key = True)
    name = Column(String(64), unique = False)
    last_name = Column(String(64), unique = False)
    position_srb = Column(String(64), unique = False)
    position_ita = Column(String(64), unique = False)
    email = Column(String(120), unique = True)
    phone = Column(String(100))
    created = Column(DateTime(), default=datetime.now)
    updated = Column(DateTime(), default=datetime.now,onupdate=datetime.now)

    #companies = relationship('Company',
                           #secondary = company_contacts,
                           #backref = backref('companies', lazy = 'dynamic'),
                           #lazy = 'dynamic')



class WorkList(Base):

    __tablename__ = 'worklists'
    id = Column(Integer, primary_key = True)
    title = Column(String(64), unique = True)
    created = Column(DateTime(), default = datetime.now)
    updated = Column(DateTime(), default=datetime.now,onupdate=datetime.now)
    slug =  Column(String(100), unique = True)


class Company(Base):

    __tablename__ = 'companies'
    id = Column(Integer, primary_key = True)
    name = Column(String(64), unique = True)
    address = Column(String(120), unique = False)
    website = Column(String(100), unique = False)
    city = Column(String(20), unique = False)
    desc = Column(Text())
    email = Column(String(120), unique = True)
    created = Column(DateTime(), default=datetime.now)
    updated = Column(DateTime(), default=datetime.now,onupdate=datetime.now)
    slug =  Column(String(100), unique = True)
    phone = Column(String(100))
    fax = Column(String(100), unique = False)



    contacts = relationship('Contact',
                           secondary = company_contacts,
                           backref = backref('contacts', lazy = 'dynamic'),
                           lazy = 'dynamic')

    sectors = relationship('Sector',
                           secondary = company_sectors,
                           backref = backref('companies', lazy = 'dynamic'),
                           lazy = 'dynamic')


    wlists= relationship('WorkList',
                           secondary = company_worklists,
                           backref = backref('wlists', lazy = 'dynamic'),
                           lazy = 'dynamic')


Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()

company = Company(name="C1")
session.add(company)

contact = Contact(last_name="Doe", name="John")

company.contacts.append(contact)

请问你是从哪里知道“db.”这个写法的？最近还有一个问题也提到metadata参数缺失的事：在SQLAlchemy中查询多对多关系

Answer 2

在这里，relationship(backref=...) 是一种方便的写法，可以让你在两个方向上都能找到引用。比如，你可以通过 Company.sectors 来找到公司的行业，也可以通过 Sector.companies 来找到属于这个行业的公司，而只需要定义其中一个就可以了。你已经定义了这两个关系，看起来你也明白这个道理；你的代码里有很多重复的引用，只是被注释掉了。

你可以把所有重复的关系删掉（也就是那些已经定义了对应的 backref 的部分），这样就没问题了。

另外，你也可以使用 relationship(back_populates=...) 这种写法，明确地定义所有的关系。这样一来，你就能在每个类上看到它具体有哪些集合属性，而且这些属性之间会正确地关联起来。