Numpy array for SQL table

I am trying to store multiple pickled Numpy arrays in a SQL database. Numpy arrays represent 3D and shapes (Name (text), Data (floats), Date (int)


I am currently doing it like this (for an array arr

containing data and names

and dates

as references containing the actual names and dates that match arr


name_size, ~, date_size = arr.shape
for i in range(name_size):
    for j in range(date_size):
        insert_into_db(name[i], date[j], arr[i,:,j)


However, this is very slow. I'm wondering if there isn't a more efficient way just by looking at the object arr

as a whole.

For example, first paste the references to names

and dates

into the database, and then somehow just copy the values ​​in arr

right into all at once (where they are ordered and smoothed correctly with the reference to Name

and Date

that we just pasted.


source to share

1 answer

If your database cannot contain native numpy arrays, you can use the dumps

or methods tostring



collects data into an object bytes

in both Python 3.x and str

Python 2.x objects , which can then be stored in the database as a sequence of strings or raw bytes. The trick is that the pickle format can change between python or numpy versions, so another numpy or python version won't necessarily be able to read it (although the numpy developers try to keep the pickle reader as backward compatible as possible):

testarr = np.arange(20)
data = testarr.dumps()


Which gives you (in python 3.x, it's different from python 2.x):




works in a similar way to converting an array to a string format. The advantage is that it has to be the same for python and numpy versions, but has the disadvantage that it does not preserve dimensions, so you will need to preserve the dimensions (and whether there will be an array C

or Fortran

order) in the database in order to correctly reconstruct the array (if it's not always the same):

testarr = np.arange(20)
data = testarr.tostring()


What you give (it will be the same in Python 2.x and 3.x, except that in Python 3.x it will be a type bytes

, and in python 2.x it will be a str






All Articles