it's unclear if there is an existing issue for this but apparently the Load() object is not pickleable directly due to the objects present in the context, so I would advise to not make use of the Load() object directly.
https://github.com/sqlalchemy/sqlalchemy/issues/4823 On Mon, Aug 26, 2019, at 7:04 AM, Jan Wegger wrote: > Hi, > > I've been using SqlAlchemy and Alembic for a year and a half now and it's > been a great experience, thanks a lot for providing such an awesome piece of > engineering! > > Recently I stumbled upon a problem that I'm wondering if it might point to an > inconsistency in SqlAlchemy, or (perhaps more likely) that I'm doing > something I shouldn't. I've been passing detached SqlAlchemy orm models to > other processes using python's multiprocessing.Pool, effectively causing the > models to get pickled. This has been working as expected, and I haven't had > any trouble with it until now. I realize the models are complex objects > though, so perhaps doing this was a bad idea in the first place? > > Anyway, I recently had a need for specifying eager relationship loads using > the explicit sqlalchemy.orm.Load() variant described here: > https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#sqlalchemy.orm.Load > > I'm providing full steps for reproducing this situation below, but the > essence is that I see a difference in 'pickleability' of results originating > from the following two, in my understanding equivalent, ways of specifying an > eager relationship load: > session.query(MyClass).options(selectinload('widget')) > session.query(MyClass).options(Load(MyClass).selectinload('widget')) > > When the resulting MyClass objects do *not* have any corresponding 'widget', > the latter query specification will yield results that can *not* be pickled, > whereas results from the former query can always be pickled. Am I naïve to > expect pickling to work, or have I stumbled across an unintentional quirk in > SqlAlchemy and that this really is expected to work? > > I've tried my best to reduce the setup into a minimal but complete set of > steps to reproduce below. The original setup uses a MySQL engine, but below I > use 'sqlite:///:memory:' to keep it simple. The error output is not exactly > the same, but I'm guessing that's not essential since the overall problem is > the same. > > Thanks and best regards, > Jan Wegger > > > Starting with a clean virtualenv and just sqlalchemy installed: > $ python > Python 3.7.3 (default, Mar 27 2019, 09:23:15) > [Clang 10.0.1 (clang-1001.0.46.3)] on darwin > >>> import sqlalchemy > >>> sqlalchemy.__version__ > '1.3.5' > > Common setup: > >>> from sqlalchemy import create_engine > >>> engine = create_engine('sqlite:///:memory:') > >>> > >>> from sqlalchemy.ext.declarative import declarative_base > >>> Base = declarative_base() > >>> > >>> from sqlalchemy import Column, Integer, String, ForeignKey > >>> from sqlalchemy.orm import relationship > >>> > >>> class MyClass(Base): > ... __tablename__ = 'my_class' > ... id = Column(Integer, primary_key=True) > ... widget_id = Column(Integer, ForeignKey('widget.id')) > ... widget = relationship('Widget') > ... > >>> class Widget(Base): > ... __tablename__ = 'widget' > ... id = Column(Integer, primary_key=True) > ... > >>> Base.metadata.create_all(engine) > >>> > >>> from sqlalchemy.orm import sessionmaker > >>> Session = sessionmaker(bind=engine) > >>> session = Session() > >>> obj = MyClass(widget_id=None) > >>> session.add(obj) > >>> session.commit() > >>> session.close() > > Query with explicit Load(): > >>> from sqlalchemy.orm import Load > >>> session = Session() > >>> obj = > >>> session.query(MyClass).options(Load(MyClass).selectinload('widget')).one() > >>> > >>> import pickle > >>> pickle.dumps(obj) > Traceback (most recent call last): > File "<stdin>", line 1, in <module> > AttributeError: Can't pickle local object 'setquit.<locals>.Quitter' > > Query with sqlalchemy.orm.selectinload() option: > >>> from sqlalchemy.orm import selectinload > >>> obj = session.query(MyClass).options(selectinload('widget')).one() > >>> > >>> import pickle > >>> pickle.dumps(obj) > b'\x80\x03c__main__\nMyClass\nq\x00... > > When running the original MySQL setup, the error is instead: > >>> pickle.dumps(obj) > Traceback (most recent call last): > File "<stdin>", line 1, in <module> > TypeError: can't pickle module objects > > > -- > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/cf86dd7b-858e-47cf-adef-e322fc24d783%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/cf86dd7b-858e-47cf-adef-e322fc24d783%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/8039c444-8bdc-4921-8686-712f3164f5b0%40www.fastmail.com.
