inserting numpy integer types into sqlite with pyt

Question

What is the correct way to insert the values of numpy integer objects into databases in python 3? In python 2.7 numpy numeric datatypes insert cleanly into sqlite, but they don't in python 3

import numpy as np
import sqlite3
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),)) # <-- Fails in 3

The np.float types seem to still work just fine in both 2 and 3.

    conn.execute("insert into foo values(?)", (np.float64(101),))

In python 2, the numpy scalar integer datatypes are no longer instances of int, even converting integer-valued floating point numbers to ints.

   isinstance(np.int64(1), int)  # <- true for 2, false for python 3

Is this why the dbapi no longer works seamlessly with numpy?

Answer 1

Rather than:

sqlite3.register_adapter(np.int64, lambda val: int(val))

You can use:

sqlite3.register_adapter(np.int64, int)

Answer 2

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 np
import sqlite3

sqlite3.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),))

Docs