Invalidated Collection

[Christian Henning]

I don't understand why SQLAlchemy gives me the following warning:

SAWarning: This collection has been invalidated.

  util.warn("This collection has been invalidated.")

Here is a minimal code example:

-----------------

-----------------

import sqlalchemy

print(sqlalchemy.__version__)

from sqlalchemy import create_engine

from sqlalchemy import Column, Integer, ForeignKey

from sqlalchemy.exc import IntegrityError

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import relationship

from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class ModelBase:

  @classmethod

  def create(cls, session, **kwargs):

    obj = cls(**kwargs)

    session.add(obj)

    session.commit()

    return obj

class User(ModelBase, Base):

  __tablename__ = 'users'

  id = Column(Integer, primary_key=True)

  addresses = relationship("Address", order_by="Address.id", back_populates="user")

class Address(ModelBase, Base):

  __tablename__ = 'addresses'

  id = Column(Integer, primary_key=True)

  user_id = Column(Integer, ForeignKey('users.id'))

  user = relationship("User", back_populates="addresses")

engine = create_engine('sqlite:///:memory:', echo=False)

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

session = Session()

jack = User.create(session)

jack.addresses.append(Address.create(session))

-----------------

-----------------

I have tested the code with SQLAlchemy for 1.3.18 and 1.4.0b1. I'm using python 3.9.1

When I rewrite the last few lines a bit the warning disappears. For instance:

jack = User.create(session)

a = Address.create(session)

jack.addresses.append(a)

Any insight is appreciated. I'm still learning.

Thanks,

Christian

[Mike Bayer]

the session.commit() method expires all attributes by default:

https://docs.sqlalchemy.org/en/13/orm/session_basics.html#committing

your code is organized in an unusual way such that transactions are being committed inside of attribute assignment operations:

    jack.addresses.append(Address.create(session))

We can expand this out as follows:

    addr = jack.addresses

    address = Address()

    session.add(address)

    session.commit()   # <--- expires all attributes on "jack", "address", invalidates "addr"

    attr.append(address)  # <--- this is invalid, transaction was already commited

I would strongly advise keeping session scope manage external to the set of operations you are performing.  See https://docs.sqlalchemy.org/en/13/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it for guidelines.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7a369ff7-659c-4ee4-a8d0-a786ec3579aen%40googlegroups.com.

[Christian Henning]

Hi Mike,

thanks for your advice! I'll make the changes.

But let me ask you one thing. My classmethod create() is but more complex than I have posted. It's meant to catch IntegrityError so that unique constraints are enforced. Image a User table has a "unique" constraint on the name. When I try to create a user with the same name the create() would catch the exception and return the original user. I believe this only possible when all objects are committed to the database.

class ModelBase:

  @classmethod

  def create(cls, session, **kwargs):

    obj = cls(**kwargs)

    try:

      obj.save(session)

    except IntegrityError:

      # print('HOLA - Integrity Error')

      session.rollback()

      obj = cls.find(session, **kwargs)

    return obj

  def save(self, session):

    session.add(self)

    session.commit()

How would you solve the problem? Is there anything in the documentation?

Thanks,

Christian

[Mike Bayer]

On Wed, Feb 3, 2021, at 6:23 PM, Christian Henning wrote:

Hi Mike,

thanks for your advice! I'll make the changes.

But let me ask you one thing. My classmethod create() is but more complex than I have posted. It's meant to catch IntegrityError so that unique constraints are enforced. Image a User table has a "unique" constraint on the name. When I try to create a user with the same name the create() would catch the exception and return the original user. I believe this only possible when all objects are committed to the database.

class ModelBase:

  @classmethod

  def create(cls, session, **kwargs):

    obj = cls(**kwargs)

    try:

      obj.save(session)

    except IntegrityError:

      # print('HOLA - Integrity Error')

      session.rollback()

      obj = cls.find(session, **kwargs)

    return obj

  def save(self, session):

    session.add(self)

    session.commit()

How would you solve the problem? Is there anything in the documentation?

yes, use a SAVEPOINT for that, so you can roll back to the savepoint and still have the transaction continue:

https://docs.sqlalchemy.org/en/13/faq/sessions.html#but-why-does-flush-insist-on-issuing-a-rollback

so you can place in your method:

session.begin_nested():

session.add(obj)

try:

     session.flush()

 except IntegrityError:

    session.rollback()

else:

    session.commit()

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ba9d2326-976f-4418-bb92-f5e120fd3f52n%40googlegroups.com.

[Christian Henning]

Thanks, Mike! I have some studying to do...