A minimal example demonstrating how SQLAlchemy 1.2 breaks pickling of ORM objects

minimal_pickle.py

"""Demonstrate how SQLAlchemy 1.2 breaks pickling of ORM objects.

This somewhat-contrived example shows a query scenario where:

- We have an object (Garply)
- It is related to a table of Foos
- Foos uses single-table polymorphism to hold multiple types
- We query a Garply and eager-load some of its Foos
- We then pickle this Garply and its Foos for hand-off to some other process.
  Since it already has its Foos loaded, we know it won't need further database
  accesses.

Under SQLAlchemy 1.1.x, this works. Under SQLAlchemy 1.2.x, pickle fails.
"""

import datetime
import pickle

from sqlalchemy import (
    Column,
    Integer,
    DateTime,
    Text,
    ForeignKey,
    create_engine,
)
from sqlalchemy.orm import (
    sessionmaker,
    scoped_session,
    with_polymorphic,
    relationship,
    subqueryload,
)
from sqlalchemy.ext.declarative import (
    declarative_base,
)


Base = declarative_base()


class Foo(Base):
    __tablename__ = 'foo'

    id = Column(Integer, primary_key=True)
    identity = Column(Text)
    created = Column(DateTime, default=datetime.datetime.now)
    garply_id = Column(ForeignKey('garply.id'))

    __mapper_args__ = {
        'polymorphic_on': identity,
        'polymorphic_identity': 'foo',
    }


class Bar(Foo):
    __mapper_args__ = {
        'polymorphic_identity': 'bar',
    }


class Baz(Foo):
    __mapper_args__ = {
        'polymorphic_identity': 'baz',
    }


class Garply(Base):
    __tablename__ = 'garply'

    id = Column(Integer, primary_key=True)

    foos = relationship(Foo)


BarBaz = with_polymorphic(Foo, [Bar, Baz])

# SQLAlchemy session manager.  Updated by lexdb.init_model().
Session = scoped_session(sessionmaker())

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

Session.configure(bind=engine)
Base.metadata.create_all(engine)

garply_a = Garply()
Session.add(garply_a)
Session.flush()

Session.add(Bar(garply_id=garply_a.id))
Session.add(Baz(garply_id=garply_a.id))
Session.commit()

garply_again = Session.query(Garply).options(
    subqueryload(Garply.foos.of_type(BarBaz))
).first()

pickle.dumps(garply_again)