store numpy array in mysql

Question

My use case is simple, i have performed some kind of operation on image and the resulting feature vector is a numpy object of shape rowX1000(what i mean to say is that the row number can be variable but column number is always 1000)

I want to store this numpy array in mysql. No kind of operation is to be performed on this array. The query will be simple given a image name return the whole feature vector. so is there any way in which the array can be stored (something like a magic container which encapsulates the array and then put it on the table and on retrieval it retrieves the magic container and pops out the array)

I want to do this in python. If possible support with a short code snippit of how to put the data in the mysql database.

score 8 · Accepted Answer · answered Jun 08 '15 at 15:51

8

You could use ndarray.dumps() to pickle it to a string then write it to a BLOB field? Recover it using numpy.loads()

answered Jun 08 '15 at 15:51

paddyg

2,153
20
24

2

`ndarray.dumbs()` returns the `ndarray` serialized as bytes. So, how can you format it into an SQL query? – Dimitris Poulopoulos Dec 13 '18 at 08:42

score 3 · Answer 2 · answered Jul 09 '16 at 08:01

3

You can also serialize it using numpy.getbuffer and numpy.frombuffer to make it independent of pickle, like

bf = np.getbuffer(np.ones((2,3), dtype=np.float32))
# store and read ...
np.frombuffer(bf, dtype=np.float32).reshape(2,3)

only you have to record the shape of the array as well if necessary.

answered Jul 09 '16 at 08:01

dontloo

10,067
4
29
50

Something has [changed](https://stackoverflow.com/questions/21821045/numpy-getbuffer-causes-attributeerror-module-object-has-no-attribute-getbuff) you now need `bytes(memoryview(arr))` – cardamom Jun 16 '17 at 13:30

store numpy array in mysql

2 Answers2

Linked