{"id":7887,"library":"zope-sqlalchemy","title":"Zope/SQLAlchemy Transaction Integration","description":"zope.sqlalchemy provides minimal integration between SQLAlchemy and Zope's transaction management system. It acts as a data manager, unifying various approaches to allow SQLAlchemy ORM sessions to participate in Zope transactions, often used in web frameworks like Pyramid. The library is currently at version 4.1, released in November 2025, and maintains an active release schedule.","status":"active","version":"4.1","language":"en","source_language":"en","source_url":"https://github.com/zopefoundation/zope.sqlalchemy","tags":["zope","sqlalchemy","transaction","orm","web-framework-integration"],"install":[{"cmd":"pip install zope.sqlalchemy","lang":"bash","label":"Install with pip"}],"dependencies":[{"reason":"Provides the ORM and database connectivity; zope.sqlalchemy integrates its sessions with transactions. Requires SQLAlchemy >= 0.9, >= 1.1, and 2.0+ for recent versions.","package":"SQLAlchemy"},{"reason":"Provides the Zope transaction management API that zope.sqlalchemy integrates with.","package":"transaction"}],"imports":[{"note":"This is the current and recommended way to integrate a SQLAlchemy session factory with Zope transactions.","symbol":"register","correct":"from zope.sqlalchemy import register"},{"note":"In `zope.sqlalchemy` v1.2, `ZopeTransactionExtension` was renamed to `ZopeTransactionEvents`. The `register()` function is now the preferred way to integrate, replacing the `extension` argument in `sessionmaker`.","wrong":"from zope.sqlalchemy import ZopeTransactionExtension","symbol":"ZopeTransactionExtension","correct":"from zope.sqlalchemy import ZopeTransactionEvents"}],"quickstart":{"code":"import os\nfrom sqlalchemy import create_engine, Column, Integer, String\nfrom sqlalchemy.orm import sessionmaker, scoped_session, declarative_base\nfrom zope.sqlalchemy import register\nimport transaction\n\n# 1. Define a simple SQLAlchemy model\nBase = declarative_base()\nclass User(Base):\n    __tablename__ = 'users'\n    id = Column(Integer, primary_key=True)\n    name = Column(String(50), nullable=False)\n\n    def __repr__(self):\n        return f\"<User(id={self.id}, name='{self.name}')>\"\n\n# 2. Create an engine (using an in-memory SQLite for example)\n# Use an environment variable for a real database DSN, e.g., POSTGRES_DSN='postgresql://user:pass@host/db'\ndatabase_url = os.environ.get('TEST_DSN', 'sqlite:///:memory:')\nengine = create_engine(database_url)\n\n# 3. Create tables (only if they don't exist)\nBase.metadata.create_all(engine)\n\n# 4. Create a SQLAlchemy session factory and scope it\n# Scoped sessions are important for web applications or multi-threaded environments\nSession = sessionmaker(bind=engine)\nDBSession = scoped_session(Session)\n\n# 5. Register the scoped session factory with zope.sqlalchemy\n# This integrates its commits/rollbacks with Zope transactions\nregister(DBSession)\n\n# --- Usage Example ---\n\n# Get a session from the factory (it's thread-local/scoped)\nsession = DBSession()\n\ntry:\n    # Add a new user\n    new_user = User(name='Alice')\n    session.add(new_user)\n\n    # The transaction manager handles committing/rolling back the session\n    transaction.commit()\n    print(f\"Added user: {new_user}\")\n\n    # After commit, the session might be closed/invalidated by default\n    # Attempting to access new_user might raise DetachedInstanceError if not reloaded\n    # print(new_user.name) # This might fail if session is closed.\n\nexcept Exception as e:\n    transaction.abort()\n    print(f\"Transaction aborted due to error: {e}\")\nfinally:\n    # Ensure the session is removed from the scope, especially in web requests\n    DBSession.remove()\n\n# Start a new transaction for another operation\nsession = DBSession()\n\ntry:\n    # Query users\n    users = session.query(User).all()\n    print(f\"Current users: {users}\")\n    transaction.commit()\nexcept Exception as e:\n    transaction.abort()\n    print(f\"Query transaction aborted: {e}\")\nfinally:\n    DBSession.remove()","lang":"python","description":"This quickstart demonstrates how to set up `zope.sqlalchemy` with a SQLAlchemy engine and a scoped session. It defines a simple `User` model, creates an in-memory SQLite database, registers the `DBSession` factory, and then uses a Zope transaction to add and retrieve data. Note the explicit `transaction.commit()` and `transaction.abort()`, and `DBSession.remove()` after each operation to properly manage the session lifecycle."},"warnings":[{"fix":"Instead of `with session.begin_nested(): session.commit()`, use `with session.begin_nested(): pass` or `savepoint = session.begin_nested(); savepoint.commit()`. The outer Zope transaction will manage the ultimate commit.","message":"As of `zope.sqlalchemy` v3.0, you can no longer call `session.commit()` directly within a manual nested database transaction (i.e., when using `session.begin_nested()`). You must commit the savepoint returned by `session.begin_nested()` or use it as a context manager.","severity":"breaking","affected_versions":"3.0+"},{"fix":"Update imports from `from zope.sqlalchemy import ZopeTransactionExtension` to `from zope.sqlalchemy import register`. Change session factory creation from `scoped_session(sessionmaker(extension=ZopeTransactionExtension(), **options))` to `DBSession = scoped_session(sessionmaker(**options)); register(DBSession)`.","message":"In `zope.sqlalchemy` v1.2, `ZopeTransactionExtension` was renamed to `ZopeTransactionEvents`. The recommended integration pattern also shifted from passing an `extension` argument to `sessionmaker` to using the `register()` function on the session factory.","severity":"breaking","affected_versions":"1.2+"},{"fix":"If you need to access objects after a commit, either reload them (`session.query(MyObject).get(obj.id)`) or configure `zope.sqlalchemy.register` with `keep_session=True` for long-lasting sessions (though this can have other implications).","message":"By default, `zope.sqlalchemy` often closes sessions after `transaction.commit()`. Accessing ORM-managed objects after a commit without reloading them can lead to `DetachedInstanceError`.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Manually mark the session as changed using `zope.sqlalchemy.mark_changed(session)` after direct SQL write operations.","message":"When performing direct SQL write operations (not through the ORM), `zope.sqlalchemy` cannot automatically detect that the session has changed. This can lead to uncommitted changes if `transaction.commit()` is called without the session being marked as 'changed'.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Ensure you are using a version of SQLAlchemy outside this problematic range (e.g., <2.0.32 or >2.0.35) when working with Python 3.11+ and `zope.sqlalchemy`.","message":"SQLAlchemy versions 2.0.32 through 2.0.35 are known to cause deadlocks when running tests on Python 3.11+. These versions are explicitly excluded from `zope.sqlalchemy`'s supported versions.","severity":"gotcha","affected_versions":"2.0.32 - 2.0.35 of SQLAlchemy with Python 3.11+"}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"fix":"Reload the object within a new session context or configure `zope.sqlalchemy.register` with `keep_session=True` (use with caution in web contexts). Alternatively, only access object attributes that were loaded before the session was detached.","cause":"Attempting to access an ORM object after the session it was loaded from has been closed or invalidated, typically after a `transaction.commit()` or `transaction.abort()`.","error":"sqlalchemy.orm.exc.DetachedInstanceError: Instance <User at 0x...> is not bound to a Session; attribute refresh operation cannot proceed (for example, due to a delete or rollback) or validation is pending"},{"fix":"Update your code to use `from zope.sqlalchemy import register` and then call `register(DBSession)` after creating your `scoped_session` factory. If you *must* use the class directly for compatibility, import `ZopeTransactionEvents` instead.","cause":"`ZopeTransactionExtension` was renamed to `ZopeTransactionEvents` in `zope.sqlalchemy` v1.2, and direct use of `extension` in `sessionmaker` is deprecated in favor of `register()`.","error":"ImportError: cannot import name 'ZopeTransactionExtension' from 'zope.sqlalchemy'"},{"fix":"Ensure proper session scoping, especially `scoped_session` and `DBSession.remove()` at the end of each request/thread. Review transaction logic for any manual `session.commit()` calls within implicitly managed Zope transactions. Consider database pooling settings. For `session.merge()` issues, explicitly call `session.expire_all()` or configure `register(DBSession, initial_state='changed')` to ensure state is reloaded.","cause":"Can occur in multi-threaded environments or complex transaction scenarios (e.g., with `Products.SQLAlchemyDA` or intricate nested transactions) where database connections or sessions are not properly isolated or released, leading to contention or premature invalidation.","error":"Connection busy issue / The transaction is inactive due to a rollback in a subtransaction"},{"fix":"Ensure your SQLAlchemy operations are wrapped in `try...except` blocks that catch `IntegrityError` (and other `sqlalchemy.exc` errors) and explicitly call `transaction.abort()` to roll back the Zope transaction. Verify the data being committed adheres to database constraints.","cause":"This is a standard SQLAlchemy integrity error, but sometimes in Zope/Pyramid contexts, the traceback or error handling can be obscured if the transaction manager isn't set up to propagate exceptions cleanly.","error":"sqlalchemy.exc.IntegrityError: (IntegrityError) NOT NULL constraint failed: user.name [SQL: INSERT INTO user DEFAULT VALUES]"}]}