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

[Loris Bennett]

Robert Latest <boblatest at yahoo.com> writes:

> 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.

Thanks for pointing out TypeDecorator - I wasn't aware of that.  I won't
need to track changes in the JSON data, because the events I am
recording form an audit trail and so are written and read, but never
modified.

Cheers,

Loris

-- 
This signature is currently under construction.