Can someone tell me how to use SQLAlchemy relationship if each model is in a separate file? Example:
# ./models/user.py
class UserModel(Base):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True)
characters: Mapped[list['CharacterModel']] = relationship(back_populates='user')
# ./models/character.py
class CharacterModel(Base):
__tablename__ = 'characters'
id: Mapped[int] = mapped_column(primary_key=True)
user_id: Mapped[int] = mapped_column(ForeignKey('users.id', ondelete='CASCADE'))
characters: Mapped['UserModel'] = relationship(back_populates='characters')
# ./models/__init__.py
from . import user, character
Error when creating a user:
sqlalchemy.exc.InvalidRequestError: When initializing mapper Mapper[UserModel(users)], expression 'CharacterModel' failed to locate a name ('CharacterModel'). If this is a class name, consider adding this relationship() to the <class 'python.database.models.user.UserModel'> class after both dependent classes have been defined.
I can’t find information anywhere, and in the use cases the models are always in the same file. I will be grateful for any information.
Do you use the same Base object in both models?
There are 2 common mistakes that may cause problems here:
- Creating new
Baseobject in every module. Actually, it should be one object - Not importing some modules with models. You should import all modules containing your models, so that
Baseobject knows about these models
Create file database.py:
from sqlalchemy.orm import DeclarativeBase
class Base(DeclarativeBase):
pass
Then import and use this Base object everywhere:
user.py
from sqlalchemy.orm import Mapped, mapped_column, relationship
from database import Base
class UserModel(Base):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True)
characters: Mapped[list['CharacterModel']] = relationship(back_populates='user')
character.py
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy import ForeignKey
from database import Base
class CharacterModel(Base):
__tablename__ = 'characters'
id: Mapped[int] = mapped_column(primary_key=True)
user_id: Mapped[int] = mapped_column(ForeignKey('users.id', ondelete='CASCADE'))
characters: Mapped['UserModel'] = relationship(back_populates='characters')
main.py
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from database import Base
# Import all modules with models!
import user
import character
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session:
user_1 = user.UserModel()
character_1 = character.CharacterModel(user=user_1)
session.add(character_1)
session.commit()
session.refresh(character_1)
print(f"{character_1.id=}, {character_1.user.id=}")
2
I found a solution, probably the only correct one. Everything is the same as in the code above, but at the end imports are added with the comment # noqa: E402 and add import from __future__ import annotations.
# ./models/user.py
from __future__ import annotations
class UserModel(Base):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True)
characters: Mapped[list['CharacterModel']] = relationship(back_populates='user')
from .character import CharacterModel # noqa: E402
# ./models/character.py
from __future__ import annotations
class CharacterModel(Base):
__tablename__ = 'characters'
id: Mapped[int] = mapped_column(primary_key=True)
user_id: Mapped[int] = mapped_column(ForeignKey('users.id', ondelete='CASCADE'))
characters: Mapped['UserModel'] = relationship(back_populates='characters')
from .user import UserModel # noqa: E402
# ./models/__init__.py
from . import user, character