sqlite3 writes only floating-point numpy arrays not integer ones

Question

I am able to save Numpy arrays with floating point numbers to sqlite3, but not arrays with integers:

import sqlite3
import numpy as np

db = sqlite3.connect('database.db')
database = db.cursor()

database.execute("CREATE TABLE table_name " 
     "(round INT, id INT, PRIMARY   KEY(round, id))")

row_to_write = np.array([1])
dtype = str(row_to_write.dtype)
if dtype.startswith('float'):
    database.execute("ALTER TABLE table_name ADD data FLOAT;")
elif dtype.startswith('int'):
    database.execute("ALTER TABLE table_name ADD data INTEGER;")

insert_str = "INSERT INTO table_name (round, id, data) VALUES (0, 0, ?);"
database.execute(insert_str, row_to_write)

results in:

InterfaceError: Error binding parameter 0 - probably unsupported type.

If I assign an floating point array instead it works row_to_write = np.array([1.1])

Answer 1

Numpy is using some custom integer data types to efficiently pack data into memory. Since these types aren't familiar to sqlite, you'll have to tell it about how to handle them, beforehand:

>>> for t in (np.int8, np.int16, np.int32, np.int64,
...           np.uint8, np.uint16, np.uint32, np.uint64):
...     sqlite3.register_adapter(t, long)
... 
>>> cur.execute("insert into foo(bar) values(?)", np.array([1]))
<sqlite3.Cursor object at 0x027A7620>