mariadb-dyncol 3.6.1

Unmaintained (2022-12-07)

I stopped maintaining this package as it has never been popular. Since MariaDB added JSON support, it’s better to use that for portability, rather than the custom dynamic columns format. If you’d like to take over maintenance of this package please email me.

Pack/unpack Python dicts into/out of MariaDB’s Dynamic Columns format.

A quick example:

>>> mariadb_dyncol.pack({"key": "value"})
b'\x04\x01\x00\x03\x00\x00\x00\x03\x00key!value'
>>> mariadb_dyncol.unpack(mariadb_dyncol.pack({"key": "value"}))
{'key': 'value'}

python -m pip install mariadb-dyncol

INSERT INTO mytable (attrs) VALUES (COLUMN_CREATE('key', 'value'))

INSERT INTO mytable (attrs) VALUES (X'0401000300000003006B65792176616C7565')

pack(mapping)

Packs the given mapping (a dict) into the MariaDB Dynamic Columns format for named columns and returns it as a byte string (Python 3’s bytes, Python 2’s str). This is suitable for then inserting into a table as part of a normal query.

The dict's keys must all be unicode strings, and the values must all be one of the supported data types:

• int between -(2 ** 32) + 1 and (2 ** 64) - 1 (Python 2: long is supported too)

• str up to 4GB encoded in UTF-8 (Python 2: unicode)

• float - anything except NaN or +/- inf

• datetime.datetime - full range supported

• datetime.date - full range supported

• datetime.time - full range supported

• Any dict that is valid by these rules, allowing nested keys. There is no nesting limit except from for MariaDB’s COLUMN_JSON function which restricts the depth to 10

Note that this does not support the DECIMAL type that MariaDB does (and would naturally map to Python’s Decimal) - it is a little more fiddly to pack/unpack, though certainly possible, and pull requests are welcomed. If you try and pack a Decimal, a DynColNotSupported exception will be raised.

There are other restrictions on the UTF-8 encoded column names as documented in MariaDB:

• The maximum length of a column name is 16383 bytes

• The maximum length of all column names (at one level in nested hierarchies) is 65535 bytes

All other unsupported types will raise a DynColTypeError. Out of range values will raise a DynColValueError.

Examples:

>>> mariadb_dyncol.pack({"a": 1})
b'\x04\x01\x00\x01\x00\x00\x00\x00\x00a\x02'
>>> mariadb_dyncol.pack({"a": "💩"})
b'\x04\x01\x00\x01\x00\x00\x00\x03\x00a!\xf0\x9f\x92\xa9'

unpack(bytestring)

Unpacks MariaDB dynamic columns data encoded byte string into a dict; the types you can expect back are those listed above. This is suitable for fetching the data direct from MariaDB and decoding in Python as opposed to with MariaDB’s COLUMN_JSON function, preserving the types that JSON discards.

As noted above, DECIMAL values are not supported, and unpacking this will raise DynColNotSupported. Also strings will only be decoded with the MySQL charsets utf8 or utf8mb4; strings with other charsets will raise DynColNotSupported as well.

Unsupported column formats, for example the old MariaDB numbered dynamic columns format, or corrupt data, will raise DynColValueError.

Examples:

>>> mariadb_dyncol.unpack(b"\x04\x01\x00\x01\x00\x00\x00\x03\x00a!\xf0\x9f\x92\xa9")
{"a": "💩"}
>>> mariadb_dyncol.unpack(b"\x04\x01\x00\x01\x00\x00\x00\x00\x00a\x02")
{"a": 1}