inserting numpy integer types into sqlite with python3
According to sqlite3 docs:
To use other Python types with SQLite, you must adapt them to one of the sqlite3 module’s supported types for SQLite: one of NoneType, int, float, str, bytes.
So you can adapt np.int64 type. You should do something like this:
import numpy as npimport sqlite3sqlite3.register_adapter(np.int64, lambda val: int(val))conn = sqlite3.connect(":memory:")conn.execute("CREATE TABLE foo (id INTEGER NOT NULL, primary key (id))")conn.execute("insert into foo values(?)", (np.int64(100),))
Rather than:
sqlite3.register_adapter(np.int64, lambda val: int(val))You can use:
sqlite3.register_adapter(np.int64, int)
Use the .item() method.
np.int64(100).item()The advantage of this solution is that is portable and not sqlite3 specific.
For reference about numpy type conversions with the .item() method, refer to https://numpy.org/doc/stable/reference/generated/numpy.ndarray.item.html#numpy.ndarray.item