PickleType hysteresis: PickleType column sometimes doesn't auto-pickle list, sometimes does

Question

I'm trying to store Python objects (including lists) as blobs in PickleType columns.

re PickleType:

 |  PickleType builds upon the Binary type to apply Python's
 |  ``pickle.dumps()`` to incoming objects, and ``pickle.loads()`` on
 |  the way out, allowing any pickleable Python object to be stored as
 |  a serialized binary field.

Two ways to populate record fields : first works, second fails

When I insert records with (correctly-mapped) fields using the explicit table definitions used to generate the table schema, all works fine -- lists are auto-pickled by the PickleType Column and stored in the table as blobs, every record is submitted as I expect it.

(By "explicit table definitions used to generate the table schema" I mean:

class MyTable1(base_object):
    __tablename__ = 'My Table 1'
    #
    Day     = Column(Integer, primary_key= True)
    Field1  = Column(PickleType)
    Field2  = Column(PickleType)

)

HOWEVER, even with the exact same data in all the same forms, if I...:

1. Declare the database (generate the schema)
2. Then use the automap_base(metadata= MetaData(engine)) to extract the db's schema/metadata
3. Use the table definitions stored in the automap_base object (automapBaseObject.classes['My Table']) to generate and submit new (or even any) records. ===> FAILS specifically on PickleType Columns populated with list objects. Records submitted to the same columns without any lists work perfectly fine.

---- >(Submitting a float to a PickleType column also failed, but I changed such columns to be Float/VARCHAR instead, circumventing this problem.)

e.g. Step 3.

MyTable1= automapBaseObject.classes['My Table 1']
record = MyTable1(**{'Day': 1, 'Field1': [1, 2], 'Field2': [np.arange(10), np.arange(20)]})

session.add(record)
session.commit()

>>> sqlalchemy.exc.StatementError: (builtins.TypeError) memoryview: a bytes-like object is required, not 'list'

Two issues here:

1. Why does SQLAlchemy's PickleType behave differently when populating fields with simple native Python types using the original schema definition tables (resulting in the expected behavior) vs. when using the automap_base retrieved tables?
2. Why does PickleType not auto-pickle lists, floats, et al in the automap_base() case? (It seems like the error produced comes from the object not being pickled, then being submitted via sqlite where it expects the object to be a binary type but is not and thus fails).

I guess a way to avoid this problem is to manually pickle all the list fields. But that seems clunky and I imagine there'd be a way to turn off this type-determination-before-deciding-to-pickle behavior. That is, ignoring the hysteresis to begin with.

Answer 1

Pickletype columns are created as BLOB columns in SQLite. Dumping and loading of objects is handled at the Python level. Automap uses reflection to build its models. By default, it has no way of knowing that some BLOB columns in the database should be PickleType columns in a model.

The simplest way to get round this is to provide automap with metadata that contains the appropriate type mappings. The base_object has such a metadata attribute.

This should work:

auto_base = automap_base(metadata=base_object.metadata)
auto_base.prepare(engine, reflect=True)

This is documented at Generating Mappings from an Existing MetaData.