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

非“None”类型的条目只调用了3次convert_int()？我需要注册哪个所需的转换器，以便将另外2个“None”类型转换/解析为某些替代值？很遗憾，我上面的尝试都没有成功。

- kzk2000

Answer 1

以下是如何处理要转换的值的 Modules/_sqlite/cursor.c中_pysqlite_fetch_one_row()函数。

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

sqlite3_column_blob()函数对于SQL NULL值返回NULL；在这种情况下，if (!val_str)分支将返回一个None值而不调用转换器。

因此，无法将NULL值转换为其他任何值。

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

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

（如果没有裸表列，这也需要使用PARSE_COLNAMES。）

- CL.

---

非常感谢！这就结束了我的难题，我会使用建议中的IFNULL。 - kzk2000