sqlite - register_converters不会在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")))

生成以下输出：

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()只被调用3次吗？为了将另外两个None类型转换/解析为某个可选的值，我需要注册什么转换器？不幸的是，我上面的尝试没有奏效。

Answer 1

下面是the _pysqlite_fetch_one_row() function in Modules/_sqlite/cursor.c处理要转换的值的方式：

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;
    }
}

对于SQLSQL值，sqlite3_column_blob() function返回NULL；在本例中，if (!val_str)分支返回None值，而不调用转换器。

因此，不可能将空值转换为其他任何值。

转换器旨在添加对其他数据类型的支持。如果要获取数据库中实际不存在的值，请更改查询：

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

(如果没有裸表列，这也需要PARSE_COLNAMES。)