mysql - 在 SQLAlchemy 中，如何跨 2 个不同的 .py 文件创建 ForeignKey 关系？

Question

在 user_models.py 中，我有这个:

class Users(Base):
    __tablename__ = 'account_users'
    id = Column(Integer, primary_key = True)
    username = Column(String(255), nullable=False)    
Base.metadata.create_all(engine)

当我运行它时，我会创建一个用户表。

在我的另一个文件 groups_models.py 上，我有这个:

class Groups(Base):
    __tablename__ = 'personas_groups'
    id = Column(Integer, primary_key = True)
    user_id = Column(Integer, ForeignKey('account_users.id')) #This creates an error!!!
    user = relationship('Users') #this probably won't work. But haven't hit this line yet.

Base.metadata.create_all(engine)

所以，如您所见，我想从组 -> 用户中建立多对一关系。

但是当我运行 groups_models.py...我得到这个错误:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'personas_groups.user_id' could not find table 'account_users' with which to generate a foreign key to target column 'id'

如果我将两个表放在一个文件中，我相信它可以工作...但是因为我将它分成 2 个文件(我绝对必须这样做)...我不知道如何制作ForeignKey 关系不再起作用了？

Answer 1

关键是对两个外键使用相同的 Base，而不是为每个表创建一个新的。

基础测试.py

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

engine = create_engine('mysql://test:test@localhost/test1',
                    echo=False)

Base = declarative_base()

user_models.py

from sqlalchemy import Column, Integer, String
from sqlalchemy import Table, Text


#Base = declarative_base()
from basetest import Base

class Users(Base):
    __tablename__ = 'account_users'
    __table_args__ = {'extend_existing':True}
    id = Column(Integer, primary_key = True)
    username = Column(String(255), nullable=False)    

Base.metadata.create_all(engine)

groups_models.py

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

from basetest import Base
#Base = declarative_base()
from test1 import Users

class Groups(Base):
    __tablename__ = 'personas_groups'
    __table_args__ = {'extend_existing':True}
    id = Column(Integer, primary_key = True )
    user_id = Column(Integer, ForeignKey('account_users2.id')) #This creates an error!!!
    user = relationship(Users) #this probably won't work. But haven't hit this line yet.

Base.metadata.create_all(engine)

确保您有相同的 Base 来创建所有相关表。