SQLite: JSON entity doesn't meet Python Standard

[gimpel-cs]

Python objects to be stored in a SQLite database are converted to str by (pony/orm/dbproviders/sqlite.py)

class SQLiteJsonConverter(dbapiprovider.JsonConverter):
    json_kwargs = {'separators': (',', ':'), 'sort_keys': True, 'ensure_ascii': False}    

based on json.JSONEncoder from the Python Standard Library.

Because sort__keys is set to True (not the default value in the Standard Library) all dicts written to the database are sorted by key.

For [C]Python 3.7+ the order of the items when iterating over a dict is given by "Dict keeps insertion order" (GvR), which is a guaranteed language feature.

Pony should meet the language standard, so that the object retrieved from the database has the same properties as the object inserted into the database.

Program (CPython 3.11.5, Pony 0.7.17)

from pony import orm

db = orm.Database()

class JsonOnly(db.Entity):
    data = orm.Required(orm.Json)


db.bind("sqlite", ":memory:", create_db=True)
db.generate_mapping(create_tables=True)

data_put = {"Z": 9, "L": [4, 5, 3], "A": 1, "D": {"DB": 2, "DA": 1}, "B": 2}
print(f"{data_put=}")
with orm.db_session:
    _ = JsonOnly(data=data_put)
with orm.db_session:
    data_get = JsonOnly[1].data
print(f"{data_get=}")

Output

data_put={'Z': 9, 'L': [4, 5, 3], 'A': 1, 'D': {'DB': 2, 'DA': 1}, 'B': 2}
data_get={'A': 1, 'B': 2, 'D': {'DA': 1, 'DB': 2}, 'L': [4, 5, 3], 'Z': 9}

[andreymal]

It should not, because JSON does not keep insertion order

See also https://github.com/ponyorm/pony/commit/bd7627e5c1d23fe6672c126105890f8f34f1c877