SQLAlchemy: JSON vs. PickleType vs. raw string for serialised data

[Robert Latest]

Loris Bennett wrote:
> Thanks for the various suggestions.  The data I need to store is just a
> dict with maybe 3 or 4 keys and short string values probably of less
> than 32 characters each per event.  The traffic on the DB is going to be
> very low, creating maybe a dozen events a day, mainly triggered via a
> command-line interface, although I will probably set up one or two cron
> jobs, each of which might generate another 0 to maybe 5 records a day.
>
> I could go for JSON (or rather LONGSTRING, as JSON is just an alias for
> LONGSTRING, but JSON is not available on the version of MariaDB I am
> using).  However, that seems like overkill, since I am never going to
> have to store anything near 4 GB in the field.  So I should probably in
> fact just use say VARCHAR(255).
>
> WDYT?

Using TypeDecorator to transparently convert between a dict and its JSON string
representation and MutableDict to track changes, you will get a completely
transparent attribute that works just like a dict. Make sure to check that the
generated JSON fits into your column width. I once got bitten by the fact that
VARCHAR(x) can hold only x/4 characters in utf8mb4 character set.