sqlalchemy.exc.InvalidRequestError: Can't attach instance another instance with key is already present in this session

Question

Here I have a problem for which I can't find the solution. I've tried to detach category objects with expunge but once it's added it doesn't work.

Traceback (most recent call last):
      File "/home/scwall/PycharmProjects/purebeurre/recovery.py", line 171, in <module>
        connection.connect.add(article)
      File "/home/scwall/PycharmProjects/purebeurre/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1776, in add
        self._save_or_update_state(state)
      File "/home/scwall/PycharmProjects/purebeurre/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1796, in _save_or_update_state
        self._save_or_update_impl(st_)
      File "/home/scwall/PycharmProjects/purebeurre/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2101, in _save_or_update_impl
        self._update_impl(state)
      File "/home/scwall/PycharmProjects/purebeurre/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2090, in _update_impl
        self.identity_map.add(state)
      File "/home/scwall/PycharmProjects/purebeurre/venv/lib/python3.6/site-packages/sqlalchemy/orm/identity.py", line 149, in add
        orm_util.state_str(state), state.key))
    sqlalchemy.exc.InvalidRequestError: Can't attach instance <Categories at 0x7fe8d8000e48>; another instance with key (<class 'packages.databases.models.Categories'>, (26,), None) is already present in this session.

class CategoriesQuery(ConnectionQuery):
    
    @classmethod
    def get_categories_by_tags(cls, tags_list):
        return cls.connection.connect.query(
            Categories
        ).filter(Categories.id_category.in_(tags_list)).all()

other file:

 def function_recovery_and_push(link_page):

        list_article = []
        try:
            products_dic = requests.get(link_page).json()
            for product in products_dic["products"]:
                if 'nutrition_grades' in product.keys() \
                        and 'product_name_fr' in product.keys() \
                        and 'categories_tags' in product.keys() \
                        and 1 <= len(product['product_name_fr']) <= 100:
                    try:
                        list_article.append(
                            Products(name=product['product_name_fr'], description=product['ingredients_text_fr'],
                                     nutrition_grade=product['nutrition_grades'], shop=product['stores'],
                                     link_http=product['url'],
                                     categories=CategoriesQuery.get_categories_by_tags(product['categories_tags'])))

                    except KeyError:
                        continue

            list_article.append(count_and_end_page_return_all)
            return list_article

        except:
            list_article.append(count_and_end_page_return_all)
            return list_article


    p = Pool()
    articles_list_all_pool = p.map(function_recovery_and_push, list_page_for_pool)
    p.close()

    for articles_list_pool in articles_list_all_pool:
        for article in articles_list_pool:
                connection.connect.add(article)

Answer 1

This error message is emitted when an ORM model instance is added to a session when another instance of the model with the same primary key already exists in the session.

This is an example that would raise, based on the SQLAlchemy session unit tests:

with Session() as s:
    instance1 = MyModel()
    s.add(instance1)
    s.flush()
    s.expunge(instance1)
    instance2 = s.get(MyModel, instance1.id)
    s.add(instance1)  # <- this line will raise

The case in the question is more complicated. The error is raised for a related model (Category) rather than the model being added (Products) and the categories, which already exist in the database, are retrieved in multiple sessions (because multiprocessing is being used).

Since categories are being retrieved in multiple sessions it's possible that a situation like this may arise:

session.add(Products(categories=[category1_from_session1]))
session.add(Products(categories=[category1_from_session2]))

In this case the second add() will fail, because category1_from_session1 already exists in the session, and category1_from_session2 has the same primary key but is not the same Python instance, that is:

# Both of these statements are true.
category1_from_session1.id == category1_from_session2.id
category1_from_session1 is not category1_from_session2

As the category data is the same regardless of the session that retrieved it, the solution is to merge the objects into the session rather than adding them.

Here's a full example based on the case in the question:

import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.orm import Mapped, mapped_column


class Base(orm.DeclarativeBase):
    pass


assoc = sa.Table(
    't48601123a',
    Base.metadata,
    sa.Column(
        'product_id', sa.Integer, sa.ForeignKey('t48601123p.id'), primary_key=True
    ),
    sa.Column(
        'category_id', sa.Integer, sa.ForeignKey('t48601123c.id'), primary_key=True
    ),
)


class Product(Base):
    __tablename__ = 't48601123p'

    id: Mapped[int] = mapped_column(primary_key=True)
    categories: Mapped[list['Category']] = orm.relationship(
        secondary=assoc, back_populates='products'
    )


class Category(Base):
    __tablename__ = 't48601123c'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(unique=True)
    products: Mapped[list[Product]] = orm.relationship(
        secondary=assoc, back_populates='categories'
    )


engine = sa.create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
Session = orm.sessionmaker(engine)

with Session.begin() as s:
    category = Category(name='A')
    s.add(category)

products = []

with Session() as s:
    cats1 = s.scalars(sa.select(Category).where(Category.name.in_(['A']))).all()
    products.append(Product(categories=cats1))

with Session() as s:
    cats2 = s.scalars(sa.select(Category).where(Category.name.in_(['A']))).all()
    products.append(Product(categories=cats2))

with Session.begin() as s:
    for product in products:
        s.merge(product)

Answer 2

Had the same issue, not sure you implemented the models as same as I did, but in my case at least, I had in the table's model - i.e:

product_items = relationship(...)

So later when I tried to do

products = session.query(Products).all()

one_of_the_products = products[0]
new_product = ProductItem(product_id=one_of_the_products.id, name='foo', category='bla')
session.add(new_product)

It raises the same exception as you:

sqlalchemy.exc.InvalidRequestError: Can't attach instance <ProductItem at 0x7fe8d8000e48>; another instance with key (<class 'packages.databases.models.ProductItem'>, (26,), None) is already present in this session.

The reason for the exception, is that when I queried for products - the relationship created it's own sub-query and attached the product_item's objects, it placed them in the variable name I defined in the relationship() -> product_items.

So instead of doing:

session.add(new_product)

I just had to use the relationship:

one_of_the_products.product_items.append(new_product)
session.commit()

hope it helps others.

Answer 3

unloading all objects from session and then adding it again in session might help.

db.session.expunge_all()
db.session.add()

Answer 4

This error happens when you try to add an object to a session but it is already loaded.

The only line that I see you use .add function is at the end where you run: connection.connect.add(article)

So my guess is that this Model is already loaded in the session and you don't need to add it again. You can add a try, except and rollback the operation if it throws an exception.