Python insert numpy array into sqlite3 database

Question

I'm trying to store a numpy array of about 1000 floats in a sqlite3 database but I keep getting the error "InterfaceError: Error binding parameter 1 - probably unsupported type".

I was under the impression a BLOB data type could be anything but it definitely doesn't work with a numpy array. Here's what I tried:

import sqlite3 as sql import numpy as np con = sql.connect('test.bd',isolation_level=None) cur = con.cursor() cur.execute("CREATE TABLE foobar (id INTEGER PRIMARY KEY, array BLOB)") cur.execute("INSERT INTO foobar VALUES (?,?)", (None,np.arange(0,500,0.5))) con.commit() 

Is there another module I can use to get the numpy array into the table? Or can I convert the numpy array into another form in Python (like a list or string I can split) that sqlite will accept? Performance isn't a priority. I just want it to work!

Thanks!

Answer 1

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