==============
mariadb-dyncol
==============
.. image:: https://img.shields.io/github/workflow/status/adamchainz/mariadb-dyncol/CI/main?style=for-the-badge
:target: https://github.com/adamchainz/mariadb-dyncol/actions?workflow=CI
.. image:: https://img.shields.io/pypi/v/mariadb-dyncol.svg?style=for-the-badge
:target: https://pypi.org/project/mariadb-dyncol/
.. image:: https://img.shields.io/badge/code%20style-black-000000.svg?style=for-the-badge
:target: https://github.com/psf/black
.. image:: https://img.shields.io/badge/pre--commit-enabled-brightgreen?logo=pre-commit&logoColor=white&style=for-the-badge
:target: https://github.com/pre-commit/pre-commit
:alt: pre-commit
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 ``dict``\s into/out of MariaDB's **Dynamic Columns** format.
A quick example:
.. code-block:: pycon
>>> 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'}
Installation
============
Use **pip**:
.. code-block:: sh
python -m pip install mariadb-dyncol
Python 3.7 to 3.11 supported.
----
**Working on a Django project?**
Check out my book `Boost Your Django DX <https://adamchainz.gumroad.com/l/byddx>`__ which covers many ways to improve your development experience.
----
Features
========
* Sensible type mapping from Python to SQL
* Tested against examples from MariaDB, including property/fuzz testing with
`hypothesis <https://hypothesis.readthedocs.io/en/latest/>`_ (which is
amazing and found many bugs)
Why?
====
The normal way for adding data into dynamic columns fields is with the
``COLUMN_CREATE`` function, and its relatives. This allows you to do things
like:
.. code-block:: sql
INSERT INTO mytable (attrs) VALUES (COLUMN_CREATE('key', 'value'))
Unfortunately the Django ORM is restricted and cannot use database functions
like this in every instance, at least not until Django 1.9. It was this
limitation I hit whilst implementing a dynamic columns field for my project
`django-mysql <https://github.com/adamchainz/django-mysql>`_ that spurred the
creation of this library.
By pre-packing the dynamic columns, the above query can just insert the blob
of data directly:
.. code-block:: sql
INSERT INTO mytable (attrs) VALUES (X'0401000300000003006B65792176616C7565')
Asides from being more easily implemented with the Django ORM, this approach
of packing/unpacking dynamic columns in Python also has some advantages:
* All data types are properly preserved in Python. The only way MariaDB
provides of pulling back all values for a dynamic columns field is to call
``COLUMN_JSON``, but JSON only supports strings and integers. Also
``COLUMN_JSON`` has a depth limit of 10, but the format has no actual limit.
* The CPU overhead of packing/unpacking the dynamic columns is moved from you
database server to your (presumably more scalable) clients.
API
===
All functions and names are accessible as attributes of the ``mariadb_dyncol``
module, which you can import with ``import mariadb_dyncol``.
``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:
.. code-block:: pycon
>>> 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:
.. code-block:: pycon
>>> 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}
Raw data
{
"_id": null,
"home_page": "https://github.com/adamchainz/mariadb-dyncol",
"name": "mariadb-dyncol",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.7",
"maintainer_email": "",
"keywords": "MariaDB",
"author": "Adam Johnson",
"author_email": "me@adamj.eu",
"download_url": "https://files.pythonhosted.org/packages/50/ba/5df33fdf9a961dbc3a062648540e21f3a7223936b376f5bddb2d32affa57/mariadb-dyncol-3.6.1.tar.gz",
"platform": null,
"description": "==============\nmariadb-dyncol\n==============\n\n.. image:: https://img.shields.io/github/workflow/status/adamchainz/mariadb-dyncol/CI/main?style=for-the-badge\n :target: https://github.com/adamchainz/mariadb-dyncol/actions?workflow=CI\n\n.. image:: https://img.shields.io/pypi/v/mariadb-dyncol.svg?style=for-the-badge\n :target: https://pypi.org/project/mariadb-dyncol/\n\n.. image:: https://img.shields.io/badge/code%20style-black-000000.svg?style=for-the-badge\n :target: https://github.com/psf/black\n\n.. image:: https://img.shields.io/badge/pre--commit-enabled-brightgreen?logo=pre-commit&logoColor=white&style=for-the-badge\n :target: https://github.com/pre-commit/pre-commit\n :alt: pre-commit\n\nUnmaintained (2022-12-07)\n-------------------------\n\nI stopped maintaining this package as it has never been popular.\nSince MariaDB added JSON support, it\u2019s better to use that for portability, rather than the custom dynamic columns format.\nIf you\u2019d like to take over maintenance of this package please email me.\n\n----\n\nPack/unpack Python ``dict``\\s into/out of MariaDB's **Dynamic Columns** format.\n\nA quick example:\n\n.. code-block:: pycon\n\n >>> mariadb_dyncol.pack({\"key\": \"value\"})\n b'\\x04\\x01\\x00\\x03\\x00\\x00\\x00\\x03\\x00key!value'\n >>> mariadb_dyncol.unpack(mariadb_dyncol.pack({\"key\": \"value\"}))\n {'key': 'value'}\n\nInstallation\n============\n\nUse **pip**:\n\n.. code-block:: sh\n\n python -m pip install mariadb-dyncol\n\nPython 3.7 to 3.11 supported.\n\n----\n\n**Working on a Django project?**\nCheck out my book `Boost Your Django DX <https://adamchainz.gumroad.com/l/byddx>`__ which covers many ways to improve your development experience.\n\n----\n\nFeatures\n========\n\n* Sensible type mapping from Python to SQL\n* Tested against examples from MariaDB, including property/fuzz testing with\n `hypothesis <https://hypothesis.readthedocs.io/en/latest/>`_ (which is\n amazing and found many bugs)\n\nWhy?\n====\n\nThe normal way for adding data into dynamic columns fields is with the\n``COLUMN_CREATE`` function, and its relatives. This allows you to do things\nlike:\n\n.. code-block:: sql\n\n INSERT INTO mytable (attrs) VALUES (COLUMN_CREATE('key', 'value'))\n\nUnfortunately the Django ORM is restricted and cannot use database functions\nlike this in every instance, at least not until Django 1.9. It was this\nlimitation I hit whilst implementing a dynamic columns field for my project\n`django-mysql <https://github.com/adamchainz/django-mysql>`_ that spurred the\ncreation of this library.\n\nBy pre-packing the dynamic columns, the above query can just insert the blob\nof data directly:\n\n.. code-block:: sql\n\n INSERT INTO mytable (attrs) VALUES (X'0401000300000003006B65792176616C7565')\n\nAsides from being more easily implemented with the Django ORM, this approach\nof packing/unpacking dynamic columns in Python also has some advantages:\n\n* All data types are properly preserved in Python. The only way MariaDB\n provides of pulling back all values for a dynamic columns field is to call\n ``COLUMN_JSON``, but JSON only supports strings and integers. Also\n ``COLUMN_JSON`` has a depth limit of 10, but the format has no actual limit.\n* The CPU overhead of packing/unpacking the dynamic columns is moved from you\n database server to your (presumably more scalable) clients.\n\nAPI\n===\n\nAll functions and names are accessible as attributes of the ``mariadb_dyncol``\nmodule, which you can import with ``import mariadb_dyncol``.\n\n``pack(mapping)``\n-----------------\n\nPacks the given mapping (a ``dict``) into the MariaDB Dynamic Columns\nformat for named columns and returns it as a byte string (Python 3's ``bytes``,\nPython 2's ``str``). This is suitable for then inserting into a table as part\nof a normal query.\n\nThe ``dict``\\'s keys must all be unicode strings, and the values must all be\none of the supported data types:\n\n* ``int`` between ``-(2 ** 32) + 1`` and ``(2 ** 64) - 1`` (Python 2: ``long``\n is supported too)\n* ``str`` up to 4GB encoded in UTF-8 (Python 2: ``unicode``)\n* ``float`` - anything except ``NaN`` or ``+/- inf``\n* ``datetime.datetime`` - full range supported\n* ``datetime.date`` - full range supported\n* ``datetime.time`` - full range supported\n* Any ``dict`` that is valid by these rules, allowing nested keys. There is no\n nesting limit except from for MariaDB's ``COLUMN_JSON`` function which\n restricts the depth to 10\n\nNote that this does not support the ``DECIMAL`` type that MariaDB does (and\nwould naturally map to Python's ``Decimal``) - it is a little more fiddly to\npack/unpack, though certainly possible, and pull requests are welcomed. If you\ntry and pack a ``Decimal``, a ``DynColNotSupported`` exception will be raised.\n\nThere are other restrictions on the UTF-8 encoded column names as documented in\nMariaDB:\n\n* The maximum length of a column name is 16383 bytes\n* The maximum length of all column names (at one level in nested hierarchies)\n is 65535 bytes\n\nAll other unsupported types will raise a ``DynColTypeError``. Out of range\nvalues will raise a ``DynColValueError``.\n\nExamples:\n\n.. code-block:: pycon\n\n >>> mariadb_dyncol.pack({\"a\": 1})\n b'\\x04\\x01\\x00\\x01\\x00\\x00\\x00\\x00\\x00a\\x02'\n >>> mariadb_dyncol.pack({\"a\": \"\ud83d\udca9\"})\n b'\\x04\\x01\\x00\\x01\\x00\\x00\\x00\\x03\\x00a!\\xf0\\x9f\\x92\\xa9'\n\n``unpack(bytestring)``\n----------------------\n\nUnpacks MariaDB dynamic columns data encoded byte string into a dict; the types\nyou can expect back are those listed above. This is suitable for fetching the\ndata direct from MariaDB and decoding in Python as opposed to with MariaDB's\n``COLUMN_JSON`` function, preserving the types that JSON discards.\n\nAs noted above, ``DECIMAL`` values are not supported, and unpacking this\nwill raise ``DynColNotSupported``. Also strings will only be decoded with the\nMySQL charsets ``utf8`` or ``utf8mb4``; strings with other charsets will raise\n``DynColNotSupported`` as well.\n\nUnsupported column formats, for example the old MariaDB numbered dynamic\ncolumns format, or corrupt data, will raise ``DynColValueError``.\n\nExamples:\n\n.. code-block:: pycon\n\n >>> mariadb_dyncol.unpack(b\"\\x04\\x01\\x00\\x01\\x00\\x00\\x00\\x03\\x00a!\\xf0\\x9f\\x92\\xa9\")\n {\"a\": \"\ud83d\udca9\"}\n >>> mariadb_dyncol.unpack(b\"\\x04\\x01\\x00\\x01\\x00\\x00\\x00\\x00\\x00a\\x02\")\n {\"a\": 1}\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Pack/unpack Python dicts into/out of MariaDB's Dynamic Columns format.",
"version": "3.6.1",
"split_keywords": [
"mariadb"
],
"urls": [
{
"comment_text": "",
"digests": {
"md5": "3f46bcf67daa49da4ac606f54a0cc8b9",
"sha256": "843cc1cd56e6e696e75331f644cfc30ff9f20a40741267ed3cf86a2093994804"
},
"downloads": -1,
"filename": "mariadb_dyncol-3.6.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "3f46bcf67daa49da4ac606f54a0cc8b9",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.7",
"size": 8789,
"upload_time": "2022-12-08T01:12:15",
"upload_time_iso_8601": "2022-12-08T01:12:15.749965Z",
"url": "https://files.pythonhosted.org/packages/1c/0e/60d6183ac16b01fa61cba920f282a8df0e26302dde2e47c456851b9d1b21/mariadb_dyncol-3.6.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"md5": "b0de0fe57c3b5b5ee0e39350603f92cb",
"sha256": "cc63fe28fa5dad4d843f665fad0ae091db3756f0a51d1fded5b2861304359724"
},
"downloads": -1,
"filename": "mariadb-dyncol-3.6.1.tar.gz",
"has_sig": false,
"md5_digest": "b0de0fe57c3b5b5ee0e39350603f92cb",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.7",
"size": 12561,
"upload_time": "2022-12-08T01:12:17",
"upload_time_iso_8601": "2022-12-08T01:12:17.843463Z",
"url": "https://files.pythonhosted.org/packages/50/ba/5df33fdf9a961dbc3a062648540e21f3a7223936b376f5bddb2d32affa57/mariadb-dyncol-3.6.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2022-12-08 01:12:17",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "adamchainz",
"github_project": "mariadb-dyncol",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"tox": true,
"lcname": "mariadb-dyncol"
}
JSON
