In this article, we will discuss about some error messages in SQLAlchemy and the way to locate and resolve them, including:

• Mapping
  • SQLAlchemy Mapper Property Error
  • Relationships
    • SQLAlchemy Ambiguous Join Path
    • SQLAlchemy Custom Join Path
• Query
  • SQLAlchemy Lazy Loading Issue
  • SQLAlchemy Nested Relation Eager Loading

Mapper Has No Property

One of the example for this type of error msg is as below:

sqlalchemy.exc.InvalidRequestError: Mapper 'Mapper[Role(role)]' has no property 'user'.
If this property was indicated from other mappers or configure events, 
ensure registry.configure() has been called.

Here consider we have a Many-To-Many relationship between User and Role ORM class. Then the message above is actually saying:

• A relationship() has definded in ORM Class User
• This relationship() has parameter like: back_populate="user"

This will actually required another relationship defined in the referenced class Role, with member named user:

class Role(SQLBassClass):
    ...
    # A member named user in role orm class
    user = relationship(back_populate='<member_name_in_user_class>') 

An this error message is telling you that: A member with name user in Role ORM class could NOT be found.

One of the general reason causing this issue is typing error. For example the member in Role is actually named users. In this case, you need to modify User class to change the back_populate='user' to back_populate='users'

Lazy Loading Issues

This is a really common issue when working with SQLAlchemy ORM framework, and particularly with the async version of Session. Below is an example of the error message.

sqlalchemy.orm.exc.DetachedInstanceError: 
Parent instance <User at 0x2bdb0e85490> is not bound to a Session; 
lazy load operation of attribute 'roles' cannot proceed 
(Background on this error at: https://sqlalche.me/e/20/bhk3)

Sometimes the lazy loading error with presented as a Greenlet execution eror:

raise exc.MissingGreenlet(
sqlalchemy.exc.StatementError: 
(sqlalchemy.exc.MissingGreenlet) greenlet_spawn has not been called; 
can't call await_only() here. Was IO attempted in an unexpected place?

Let's see an code example, consider the following statement:

stmt = (  
    select(orm.ContactInfo)  
    .where(orm.ContactInfo.contact_info.__eq__(login_info))  
    .where(orm.ContactInfo.deleted.__eq__(False))  
)

# get one instance based on statement above
contact_info = (await session.scalars(stmt)).one()

And the relationships used are:

• One-To-Many between User and ContactInfo
• Many-To-Many between User and Role

So what happened if I tried to access user and roles from the instance retrieved?

# assume the session already closed at this point

# relation chain: user <-- contact info
related_user = contact_info.user

# relation chain: roles <-- user <-- contact info
roles = related_user.roles

Both will failed to execute, since those relationships are not used when session was alive, so they had not been loaded.

Also noticed that the relationship roles is a nested relationship, we need to first get User from ContactInfo.user, then get List[Roles] from User.roles.

Solution

In this case, we need to eager load all properties we used. The correct code to load those relation in advance is as below:

stmt_contact_info = (  
    select(orm.ContactInfo)
    # first load ContactInfo.user
    .options(selectinload(orm.ContactInfo.user)
	    # nested: load User.roles
        .options(selectinload(orm.User.roles))
    )  
    .where(orm.ContactInfo.contact_info.__eq__(login_info))  
    .where(orm.ContactInfo.deleted.__eq__(False))  
)

selectinload() is one of the choice of eager loading strategy in SQLAlchemy. For more info, check out SQLAlchemy - SelectInLoad.

Ambiguous Join Path

Consider the example given in SQLAlchemy Official Docs:

class User(SQLBaseModel):  
    __tablename__ = "user"
    user_id: Mapped[IntPrimaryKey]
    
    # Trade record in which this user as a seller 
    sells: Mapped[List["TradeRecord"]] = relationship(  
        back_populates="seller", foreign_keys="[TradeRecord.seller_id]"  
    )  
    
    # Trade record in which this user as a buyer
    buys: Mapped[List["TradeRecord"]] = relationship(
        back_populates="buyer", foreign_keys="[TradeRecord.buyer_id]"  
    )


class TradeRecord(SQLBaseModel):  
    __tablename__ = "trade"  
    trade_id: Mapped[IntPrimaryKey]  
  
    seller_id: Mapped[int] = mapped_column(ForeignKey("user.user_id"))  
    buyer_id: Mapped[int] = mapped_column(ForeignKey("user.user_id"))  
  
    seller: Mapped["User"] = relationship(back_populates="sells", foreign_keys=[seller_id])
    buyer: Mapped["User"] = relationship(back_populates="buys", foreign_keys=[buyer_id])  

Notice that there are two One-To-Many relationship between these two tables, and these two relationship will cause ambiguity that SQLAlchemy unable to resolve automatically.

Solution

In this case, the solution is to specify foreign_keys in relationship. It should be a or a list of foreign key member(s), which will be used in the JOIN operation when connecting the relationship.

Here notice in TradeRecord class, since the foreignkey is alreay in this class, we could directly using the member object as the param of foreign_keys. However in User class, we should specify the foreign_key using Python Evaluable String:

# notice, when using list, the list square brackets should also be part of the string
foreign_keys="[TradeRecord.buyer_id]"
# or since we only use one foreign key, the list could be ignored
foreign_keys="TradeRecord.buyer_id"

For more info, check out SQLAlchemy - Handle Multiple Join Paths

Custom Join Condition

To use custom join condition in relationship, we only need to specify primaryjoin paramter:

class User(Base):
	...
    boston_addresses = relationship(
        "Address",
        primaryjoin="and_(User.id==Address.user_id, Address.city=='Boston')",
    )

Check out SQLAlchemy - Alternate Join Condition for more info.