Sqlite - register_converters not starting on Python NoneType?
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")))
outputs 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 () only called 3 times for non-None records? What required converter do I need to register in order to convert / parse the other 2 types of "None" to any alternative value? My attempts unfortunately don't work.
source to share
This is how the function _pysqlite_fetch_one_row()
in the /_sqlite/cursor.c modules handles the 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 function returns for the SQL value ; in this case, the branch returns without calling the converter. sqlite3_column_blob()
NULL
NULL
if (!val_str)
None
So it is not possible to convert NULL values to anything else.
Converters are designed to support 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;
(No column, no table, this also requires PARSE_COLNAMES
.)
source to share