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...:
- Declare the database (generate the schema)
- Then use the
automap_base(metadata= MetaData(engine))to extract the db's schema/metadata - 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:
- 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?
- 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.