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

[Loris Bennett]

Dennis Lee Bieber <wlfraed at ix.netcom.com> writes:

> On Tue, 01 Mar 2022 08:35:05 +0100, Loris Bennett
> <loris.bennett at fu-berlin.de> declaimed the following:
>
>>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?
>>
>
> 	Having taken a few on-line short courses on database normalization and
> SQL during my first lay-off, my view would be to normalize everything
> first... Which, in your description, means putting that dictionary into a
> separate table of the form (I also tend to define an autoincrement primary
> key for all tables):
>
> DICTDATA(*ID*, _eventID_, dictKey, dictValue)
>
> where * delimits primary key, _ delimits foreign key to parent (event?)
> record.

Ah, yes, you are right. That would indeed be the correct way to do it.
I'll look into that.  Up to now I was thinking I would only ever want to
read out the dict in its entirety, but that's probably not correct.

> Caveat: While I have a book on SQLAlchemy, I confess it makes no sense to
> me -- I can code SQL joins faster than figuring out how to represent the
> same join in SQLAlchemy.

I currently can't code SQL joins fast anyway, so although doing it in
SQLAlchemy is might be relatively slower, absolutely there's maybe
not going to be much difference :-)

Cheers,

Loris

-- 
This signature is currently under construction.