sqlite - register_converters don't trigger on Python NoneType?

Question

import sqlite3
import numpy as np

def convert_int(x):
    print('convert_int was called with {}'.format(x))
    if x == b'None' or x == b'':
        return -1    # minus 1 as placeholder for integer nan
    return np.int64(np.float64(x))  # np.float64 needed here as int(b'4.0') throws 

sqlite3.register_converter('int', convert_int)
sqlite3.register_converter('None', convert_int)  # attempt to tigger upon None
sqlite3.register_converter('NoneType', convert_int) # attempt to tigger upon None
sqlite3.register_converter('null', convert_int) # attempt to tigger upon None

values = [(4.0,), (4,), (None,), ('',), (1.0,)]  #

conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
conn.execute("create table test(p int)")
conn.executemany("insert into test(p) values (?)", values)

print(list(conn.execute("select p from test")))

yields the following output:

convert_int was called with b'4'
convert_int was called with b'4'
convert_int was called with b'1'
Out[2]: 
[(4,), (4,), (None,), (None,), (1,)]  # 

convert_int() is only called 3 times for the non-None type entries? What's the required converter I need to register in order to convert/parse the other 2 None types to some alternative value? My attempts above unfortunately don't work.

Answer 1

This is how the _pysqlite_fetch_one_row() function in Modules/_sqlite/cursor.c handles a value to be converted:

if (converter != Py_None) {
    nbytes = sqlite3_column_bytes(self->statement->st, i);
    val_str = (const char*)sqlite3_column_blob(self->statement->st, i);
    if (!val_str) {
        Py_INCREF(Py_None);
        converted = Py_None;
    } else {
        item = PyBytes_FromStringAndSize(val_str, nbytes);
        if (!item)
            goto error;
        converted = PyObject_CallFunction(converter, "O", item);
        Py_DECREF(item);
        if (!converted)
            break;
    }
}

The sqlite3_column_blob() function returns NULL for an SQL NULL value; in this case, the if (!val_str) branch returns a None value without calling the converter.

So it is not possible to convert NULL values to anything else.

Converters are intended to add support for other data types. If you want to get values that are not actually in the database, change your query:

SELECT ifnull(p, -1) AS "p [int]" FROM test;

(Without a bare table column, this also requires PARSE_COLNAMES.)