'Set SQLAlchemy PickleType Column Size
I am trying to save a python list to an SQL table in a PickleType column which is defined in the class object as text = Column(PickleType). When I try to commit a longer entry I get the following error: (DataError) (1406, "Data too long for column 'text' at row 1"). However, SQLAlchemy PickleType documentation says nothing about how to set the size of the column. How do I do this?
Solution 1:[1]
You can try LargeBinary(length=65536) to create a MediumBlob
Solution 2:[2]
PickleType uses a non-lengthed text type to store the data. The error you're getting is probably related to the DBAPI driver you're using, which likely has some configurable limits on large objects, e.g. you're probably using cx_oracle or pyodbc with SQL server. Check with the DBAPI and the underlying connectivity features in order to configure the max size of a single field that can be sent.
Solution 3:[3]
If using flask-sqlalchemy you can edit the migration file and change PickleType() to LargeBinary(length=65536) for example.
| Column Type | Type of BLOB | Max Data that can be stored |
|---|---|---|
| LargeBinary(length=254) | TINYBLOB | Up to 255 bytes |
| LargeBinary() | BLOB | Up to 64 Kb |
| LargeBinary(length=65536) | MEDIUMBLOB | Up to 16 Mb |
| LargeBinary(length=16777216) | LONGBLOB | Up to 4 Gb |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | whuala |
| Solution 2 | zzzeek |
| Solution 3 |