fergu
commented
5 years ago I found the solution from this link
Essentially, np.ndarray needs something to convert the array in to something that sqlite can actually handle as it has no array datatype, and apparently something goes wrong when trying to buffer the BLOB'd array directly. So, instead, convert the whole mess to text using numpy's save functionality.
Taking the code from the above link and attaching it to my (now working) code from above.
import sqlalchemy
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)
engine = sqlalchemy.create_engine('sqlite:///:memory:',connect_args={'detect_types':sqlite3.PARSE_DECLTYPES})This feels like something that should maybe be default behavior for Panda's underlying communication with sqlalchemy (or maybe sqlalchemy's communication with sqlite3)?
I am trying out using pandas+sqlalchemy (specifically sqlite) as a means to store my data. In my use case, I will have a number of rows where most will be obvious types such as string, int, float, etc - but I will want to store a few numpy ndarrays. In the example below, each 'channel' entry is a 1x3000 array of floats. For higher dimensional arrays (at most 2D) I plan to store the shape of the data alongside the flattened array, so this is all I need.
Code Sample, a copy-pastable example if possible
This was run in Jupyter Lab.
This produces the traceback
Problem description
I notice that the bottom of the traceback specifies the error started in sqlalchemy - though for all my googling I can't find any record of this problem (or error message) anywhere either with sqlalchemy or pandas. I'm assuming this means I probably am doing something wrong, but I'm not seeing it when looking at docs etc. Storing a numpy array as a binary blob makes sense to me, so I feel like the type inference makes sense.
Expected Output
Writing to the database/no error. :)
Output of
pd.show_versions()