inserting numpy integer types into sqlite with python3

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?

A numpy integer type isn't just the byte representation of the number (it's `.item()` value); is an object, almost the same as a single element, 0d, array. So I don't think you can save it, in all of its numpy glory, in a database. You could save its integer value, or a some byte equivalent, but not the full numpy object. Is there anything in `sqlite3` about saving a user defined object instance? — hpaulj, Aug 03 '16 at 22:51
There is always the scary pickle-approach (targeting TEXT type), or something more modern and binary-based like [MessagePack](http://msgpack.org/) (targeting BLOB type). — sascha, Aug 03 '16 at 23:15
What's the advantage to saving `np.int64(100)` instead of `100`? Is there some useful information that you couldn't recover during a fetch? You might look at how modules like SQLAlchemy handle the sql-object interface. — hpaulj, Aug 03 '16 at 23:49
http://stackoverflow.com/questions/18621513/python-insert-numpy-array-into-sqlite3-database is an example of previous SO questions. The solution there to saving a whole array is to write `np.save` to a byteString and saving that as a custom type in the database. Search `sqlite3` and `numpy`. — hpaulj, Aug 03 '16 at 23:58
@hpaulj I'm creating the np.int64 explicitly as an example. I'm not trying to save the int64 objects into the database, just their integer representation. — David Nehme, Aug 04 '16 at 05:04
OK, I glossed over the py2 v py3 difference. If you'd tried to save an element of an array I might have gone off on another rabbit track. `item()` will strip off the `numpy` object wrapper. I don't have `numpy` installed on py2 to test that side of the problem. — hpaulj, Aug 04 '16 at 11:53
Py3 removed the distinction between integer and long. So some numpy integer dtypes no longer subclass `integer`. Floats still subclass. Can you still save an element of a float array? — hpaulj, Aug 04 '16 at 12:15
In testing a structured array to sqlite3 write for http://stackoverflow.com/questions/38798033/fast-query-in-formatted-data/38808983, I found that `tolist()` handles this `integer` issue nicely: `c.executemany(exec_string, data.tolist())` — hpaulj, Aug 06 '16 at 23:04

score 12 · Accepted Answer · answered Aug 23 '16 at 16:21

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

score 6 · Answer 2 · answered Nov 25 '18 at 22:05

6

Rather than:

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

You can use:

sqlite3.register_adapter(np.int64, int)

answered Nov 25 '18 at 22:05

Will

163
2
3

score -1 · Answer 3 · answered Mar 15 '21 at 03:23

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

inserting numpy integer types into sqlite with python3

3 Answers3

Linked