Insert numpy type in sqlite using python3

Question

What is the correct way to insert numpy integer values ​​into databases in python 3? In python 2.7 numpy, numeric data types are inserted purely in sqlite, but they are not 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 still seem to work fine in both 2 and 3.

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

In python 2, numeric data types with the numpy numeric value are no longer int instances and even convert floating point integers to ints.

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

Which is why dbapi no longer works without using numpy?

Answer 1

According to sqlite3 docs:

To use other types of Python with SQLite, you must adapt them to one of the supported sqlite3 modules for SQLite: one of NoneType, int, float, str, bytes.

This way you can adapt the 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