SQLAlchemy AttributeError: ‘int’ object has no attribute ‘_sa_instance_state’

Introduction

When working with SQLAlchemy, a common error that developers might encounter is the AttributeError: 'int' object has no attribute '_sa_instance_state'. This error can be confusing and could be caused by a variety of reasons. In this article, we’ll discuss the common reasons behind this error and provide multiple solutions along with their respective pros and cons.

Using SQLAlchemy with Correct Relationships

This error typically happens when an integer, rather than an instance of the mapped class, is being used where SQLAlchemy expects an ORM-mapped object.

Steps to implement:

1. Review your model relations and make sure you are assigning objects not ids, for ForeignKey-related assignments.
2. Use the session to add and commit your changes as expected.

Code example:

from sqlalchemy import create_engine, Integer, Column, ForeignKey, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()
class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

# Connect to the database and create tables
db_uri = "sqlite:///:memory:"
engine = create_engine(db_uri)
Base.metadata.create_all(engine)

# Example of correct usage where a Child object is added to Parent
child = Child()
parent = Parent()
parent.children.append(child)

DBSession = sessionmaker(bind=engine)
session = DBSession()
session.add(parent)
session.commit()

Pros: Following the correct ORM patterns helps maintain code consistency, eases readability, and promotes good data integrity.

Cons: It might require a significant codebase update if you didn’t follow this practice from the start.

Convert Foreign Key References Directly

Directly convert id(s) to reference objects when creating or updating related fields.

Steps to implement:

1. Ensure that you convert foreign keys to actual objects before assigning them if not using the relationship’s collection directly.
2. Fetch the related object using its id and assign it to the parent.

Example:

DBSession = sessionmaker(bind=engine)
session = DBSession()

# Retrieve the parent object based on a given id (foreign key).
parent_id = 1  # This should be a valid id in your database
parent = session.query(Parent).get(parent_id)

# Now assign the parent object to the child's parent relationship field.
child = Child()
child.parent = parent  # Notice referencing the object directly

session.add(child)
session.commit()

Pros: This method finds a balance by allowing foreign keys to be used effectively, yet conforming correctly with the ORM’s expectations.

Cons: It may have a performance impact due to extra queries fetching related objects, rather than direct foreign key assignments.