Clearing nullable IntArray field results in TypeError when loading

[osteenbergen]

PonyORM stores None as 'null' for a nullable Int[] field. If set this causes loading to fail as it can't parse 'null' to an array

Versions: Python 3.8.10 PonyORM: 0.7.16

Example:

from pony.orm import *
db = Database()

class Test(db.Entity):
    key = PrimaryKey(str)
    numbers = Optional(IntArray, nullable=True)

set_sql_debug(True)
db.bind(provider="sqlite", filename=":memory:")
db.generate_mapping(create_tables=True)

with db_session:
    t1 = Test(key="test1", numbers=[1,2,3])

with db_session:
    t1 = Test.get(key="test1")
    t1.numbers=None

with db_session:
    t1 = Test.get(key="test1")

Output:

GET NEW CONNECTION
RELEASE CONNECTION
GET CONNECTION FROM THE LOCAL POOL
PRAGMA foreign_keys = false
BEGIN IMMEDIATE TRANSACTION
CREATE TABLE "Test" (
  "key" TEXT NOT NULL PRIMARY KEY,
  "numbers" INT[]
)

SELECT "Test"."key", "Test"."numbers"
FROM "Test" "Test"
WHERE 0 = 1

COMMIT
PRAGMA foreign_keys = true
CLOSE CONNECTION
GET CONNECTION FROM THE LOCAL POOL
BEGIN IMMEDIATE TRANSACTION
INSERT INTO "Test" ("key", "numbers") VALUES (?, ?)
['test1', '[1,2,3]']

COMMIT
RELEASE CONNECTION
GET CONNECTION FROM THE LOCAL POOL
SWITCH TO AUTOCOMMIT MODE
SELECT "key", "numbers"
FROM "Test"
WHERE "key" = ?
['test1']

BEGIN IMMEDIATE TRANSACTION
UPDATE "Test"
SET "numbers" = ?
WHERE "key" = ?
['null', 'test1']

COMMIT
RELEASE CONNECTION
GET CONNECTION FROM THE LOCAL POOL
SWITCH TO AUTOCOMMIT MODE
SELECT "key", "numbers"
FROM "Test"
WHERE "key" = ?
['test1']

ROLLBACK
RELEASE CONNECTION
Traceback (most recent call last):
  File "../scratch_3.py", line 20, in <module>
    t1 = Test.get(key="test1")
  File ".../venv/lib/python3.8/site-packages/pony/orm/core.py", line 4007, in get
    try: return entity._find_one_(kwargs)  # can throw MultipleObjectsFoundError
  File ".../venv/lib/python3.8/site-packages/pony/orm/core.py", line 4114, in _find_one_
    if obj is None: obj = entity._find_in_db_(avdict, unique, for_update, nowait, skip_locked)
  File ".../venv/lib/python3.8/site-packages/pony/orm/core.py", line 4174, in _find_in_db_
    objects = entity._fetch_objects(cursor, attr_offsets, 1, for_update, avdict)
  File ".../venv/lib/python3.8/site-packages/pony/orm/core.py", line 4308, in _fetch_objects
    obj._db_set_(avdict)
  File ".../venv/lib/python3.8/site-packages/pony/orm/core.py", line 4934, in _db_set_
    new_vals = {attr: attr.converters[0].dbval2val(dbval, obj) if not attr.reverse else dbval
  File ".../venv/lib/python3.8/site-packages/pony/orm/core.py", line 4934, in <dictcomp>
    new_vals = {attr: attr.converters[0].dbval2val(dbval, obj) if not attr.reverse else dbval
  File ".../venv/lib/python3.8/site-packages/pony/orm/dbproviders/sqlite.py", line 281, in dbval2val
    return TrackedArray(obj, converter.attr, items)
  File ".../venv/lib/python3.8/site-packages/pony/orm/ormtypes.py", line 333, in __init__
    TrackedList.__init__(self, obj, attr, value)
  File ".../venv/lib/python3.8/site-packages/pony/orm/ormtypes.py", line 308, in __init__
    list.__init__(self, (self.make(obj, attr, val) for val in value))
TypeError: 'NoneType' object is not iterable

Process finished with exit code 1

Workaround Either set nullable=False, use db.execute to set the field to NULL or use a default value with a entity hook:

class Test(db.Entity):
    key = PrimaryKey(str)
    numbers = Optional(IntArray, nullable=True, default=[])

    def before_update(self):
        if self.numbers is None:
            self.numbers = []