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.
