Python Insert Numpy Array into SQLite3 Database

Python insert numpy array into sqlite3 database

You could register a new array data type with sqlite3:

import sqlite3
import numpy as np
import io

def adapt_array(arr):
    """
    http://stackoverflow.com/a/31312102/190597 (SoulNibbler)
    """
    out = io.BytesIO()
    np.save(out, arr)
    out.seek(0)
    return sqlite3.Binary(out.read())

def convert_array(text):
    out = io.BytesIO(text)
    out.seek(0)
    return np.load(out)

# Converts np.array to TEXT when inserting
sqlite3.register_adapter(np.ndarray, adapt_array)

# Converts TEXT to np.array when selecting
sqlite3.register_converter("array", convert_array)

x = np.arange(12).reshape(2,6)

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test (arr array)")

With this setup, you can simply insert the NumPy array with no change in syntax:

cur.execute("insert into test (arr) values (?)", (x, ))

And retrieve the array directly from sqlite as a NumPy array:

cur.execute("select arr from test")
data = cur.fetchone()[0]

print(data)
# [[ 0  1  2  3  4  5]
#  [ 6  7  8  9 10 11]]
print(type(data))
# <type 'numpy.ndarray'>

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 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

Trouble storing numpy array in sqlite3 with python

The only problem with unutbu's code is that his adapt_array raises an exception in Python 3:

def adapt_array(arr):
    out = io.BytesIO()
    np.save(out, arr)
    out.seek(0)
    # http://stackoverflow.com/a/3425465/190597 (R. Hill)
    return buffer(out.read())

That's because buffer doesn't exist in 3.x. And it isn't actually doing anything useful here in 2.x, so you can just remove it. Just replace that last line with:

return out.read()

And now, everything else works perfectly.

If you need compatibility with older 2.x and also with 3.x (I'm not sure if there are any versions where this overlaps, but there might be…), you can instead fix it by doing this at the top of the module:

try:
    buffer
except NameError:
    buffer = bytes

---

Related Topics