SqlAlchmey circular import whit many-to-many relationship

I recently started using python for a project. I have a couple of tables with a M2M relataionship.

I created the tables (Left, Right, AssociationTable) but I cannot fix the circular import while using class reference and type checking.

The working solution is like this:

if TYPE_CHECKING:
  from ... import Right

class Left(Base):
    __tablename__ = "LEFT_TABLE"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    brand: Mapped[str] = mapped_column(String(255), nullable=True)

    left_right_assocaiation: Mapped[InstrumentedList["AssociationTable"]] = relationship("AssociationTable",back_populates="LEFT_TABLE")

    rights: Mapped[InstrumentedList["Right"]] = relationship("Right",secondary="ASSOCIATION_TABLE",back_populates="lefts")

on a separate module i have:

if TYPE_CHECKING:
  from ... import Left

class Right(Base):
    __tablename__ = "RIGHT_TABLE"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    version: Mapped[str] = mapped_column(String(255), nullable=True)

    left_right_assocaiation: Mapped[InstrumentedList["AssociationTable"]] = relationship("AssociationTable",back_populates="RIGHT_TABLE")

    lefts: Mapped[InstrumentedList["Left"]] = relationship("Left",secondary="ASSOCIATION_TABLE",back_populates="rights")

on a separate module:

class AssociationTable(Base):
    __tablename__ = "ASSOCIATION_TABLE"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    left_id: Mapped[int] = mapped_column(ForeignKey("LEFT_TABLE.id"),
                                                          nullable=False)
    right_id: Mapped[int] = mapped_column(ForeignKey("RIGHT_TABLE.id"), nullable=False)

    left= relationship("Left",back_populates="ASSOCIATION_TABLE")
    right= relationship("Right",back_populates="ASSOCIATION_TABLE")

If i switch from string reference to class reference in the relationsips for all the classes, e.g.

    lefts: Mapped[InstrumentedList["Left"]] = relationship(Left,secondary="ASSOCIATION_TABLE",back_populates="rights")

then

If i use TYPE_CHECKING: NameError: name 'Left' is not defined
If i don't use it: cannot import name '' from partially initialized module '' (most likely due to a circular import)

I tried different solutions (@declared_attr, @classmethod) but the result is always the same.

Is it possible to switch to class reference without incurring in the issues?

thanks

I tried different solutions (@declared_attr, @classmethod) but the result is always the same.

Solution

It is an interesting question that has multiple possible solutions or needs further explanations.

Your issue generally is not connected to SqlAlchemy, it is a very common Python problem. However, here it is triggered by specific SqlAlchemy behavior.

I'll try to explain the problem as I see it and you'll correct me if I misunderstand the situation.

From SqlAlchemy point of view: why do you try to use class reference instead of string reference? As far as I understand, class needs to be imported at the moment of execution, while string is processed by SqlAlchemy itself without the need to load the class. I'm using string references everywhere and haven't, so far, seen any tutorials that say I'm doing it wrong.

Also: why do you need relationship(Left) or relationship("Left") at all? You already has "Left" in lefts: Mapped[InstrumentedList["Left"]]. It is not an error, it is, I think, redundant. I use it this way:

breed_groups: Mapped[Optional[list["BreedGroup"]]] = relationship(secondary=user2breed_group)

And it works fine, as far as I can judge. What are you trying to achieve here?

Now, for your actual question, which is not about SqlAlchemy, but about Python.

Generally, you have 2 classes which reference each other. You try to execute a class Right, which imports class Left, but class Left imports class Right, so it tries to import it back, but can't, because class Right imports class Left. And so on, that's a circle. It is a very common problem in Python. It doesn't matter if you are using SqlAlchemy or not, if you are trying to import Left from Right AND Right from Left, it will tell you, at a runtime, that you can't do that. But obviously you need that connection, and often.

The solution (as I understand it, because there are as many opinions on the matter in the Internets and there are python programmers) is to load and initialise both classes before runtime, so at runtime they were both loaded and cached and could reference each other without problem. You can't do it from inside their files, it's just not how Python runs.

What I do: I add more files. First, I use init.py file for a module (lets call it "models") where both my classes are stored, in this way:

from .left import Left
from .right import Right

There are no imports inside my 'left.py' and 'right.py' classes! At least no imports referencing each other.

Then, when I need the classes to be actually USED, executed, be it, for example, for create_all, I load and initialise the module in full:

from models import *

At this point models' init.py loads and caches both Right and Left classes, and when any of the classes is run, the other, referenced, class is already loaded and cached and nothing needs to be imported. So when Right tries to do something with Left, Left is already loaded and initiated by models and vice versa.

@declared_attr, @classmethod and any other "solutions" do not, in any way, influence the problem of "file right.py imports from file left.py which imports from file right.py"

Also, do you understand what you are doing with TYPE_CHECKING? It is a VERY specific boolean needed for very specific reason (purely as a technical aid for IDEs and other tools) and I have a suspicion you don't understand why it's there and what does it does. I would advice you to not use it at all, despite what tutorials show, until you know why you need it. It may deceive you. It is always false at runtime, so none of your imports would actually work at runtime and other imports, non-obvious for you, would be used. Or not used. I don't really understand TYPE_CHECKING and never use it.

There are many, many, many tutorials and explanations about circular import and not all of them are any good, but you really should understand the concept, because it's one of the main problems and flaws of Python, in my opinion, and you would have to work around it for your whole programming life. Not that I have that much experience with Python, either. You may start with something like that https://medium.com/@hamana.hadrien/so-you-got-a-circular-import-in-python-e9142fe10591 and experiment further.

If I misunderstood something, I apologise, I'm using Python for less than half a year myself. If you want to correct me or add a question, please do. That goes for anyone who understand imports or SqlAlchemy better than me.