sqlite寄存器\u转换器不会在Python类型上触发吗？

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,)]  # 

对于非None类型的条目，convert\u int（）只被调用3次？ 为了将其他2个None类型转换/解析为其他值，我需要注册什么样的转换器？ 不幸的是，我上面的尝试不起作用。你知道吗

Answer 1

这就是the ^{} 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;
    }
}

^{} function为SQL NULL值返回NULL；在本例中，if (!val_str)分支返回None值而不调用转换器。你知道吗

因此不可能将空值转换为其他值。你知道吗

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

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

（如果没有空表列，这也需要PARSE_COLNAMES。）