4

I am trying to save a number of pickled Numpy arrays into an SQL database. The Numpy arrays are 3D and of the form (Name (text), Data (floats), Date (int).

I am currently doing it as following (for array arr containing the data and names and dates as references containing actual names and dates respective to 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 am wondering if there is not a far more efficient way by just considering the object arr as a whole.

For example insert the references in names and dates into the database first and then somehow just copy the values in arr straight in all at once (where they are ordered and flattened correctly in reference to the Name and Date values we have just inserted.

Improve this question
2
  • Why are you using SQL for pickling numpy arrays ? you can always use Pickle module for python to store almost all the Python objects in a .p file and access when required ? It would reduce a lot of overhead. Commented Apr 27, 2015 at 13:44
  • 1
    I'm not using SQL to pickle, I am creating a database of objects I had previously pickled for more efficient querying. The format of the pickled object was a numpy array but this will be irrelevant once the information has been inserted into the database. Commented Apr 27, 2015 at 16:07

2 Answers 2

Reset to default
2

If your database cannot hold native numpy arrays, you can use the dumps or tostring methods.

dumps pickles the data to a bytes object in Python 3.x and a str object in Python 2.x, which can then be stored in the database as a string or raw byte sequence. The catch is that the pickle format can change between python or numpy versions, so a different version of numpy or python won't necessarily be able to read it (although numpy developers try to keep the pickle reader as backwards-compatible as possible):

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

Which gives you (in python 3.x, it is different in python 2.x):

b'\x80\x02cnumpy.core.multiarray\n_reconstruct\nq\x00cnumpy\nndarray\nq\x01K\x00\x85q\x02c_codecs\nencode\nq\x03X\x01\x00\x00\x00bq\x04X\x06\x00\x00\x00latin1q\x05\x86q\x06Rq\x07\x87q\x08Rq\t(K\x01K\x14\x85q\ncnumpy\ndtype\nq\x0bX\x02\x00\x00\x00i8q\x0cK\x00K\x01\x87q\rRq\x0e(K\x03X\x01\x00\x00\x00<q\x0fNNNJ\xff\xff\xff\xffJ\xff\xff\xff\xffK\x00tq\x10b\x89h\x03X\xa0\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01\x00\x00\x00\x00\x00\x00\x00\x02\x00\x00\x00\x00\x00\x00\x00\x03\x00\x00\x00\x00\x00\x00\x00\x04\x00\x00\x00\x00\x00\x00\x00\x05\x00\x00\x00\x00\x00\x00\x00\x06\x00\x00\x00\x00\x00\x00\x00\x07\x00\x00\x00\x00\x00\x00\x00\x08\x00\x00\x00\x00\x00\x00\x00\t\x00\x00\x00\x00\x00\x00\x00\n\x00\x00\x00\x00\x00\x00\x00\x0b\x00\x00\x00\x00\x00\x00\x00\x0c\x00\x00\x00\x00\x00\x00\x00\r\x00\x00\x00\x00\x00\x00\x00\x0e\x00\x00\x00\x00\x00\x00\x00\x0f\x00\x00\x00\x00\x00\x00\x00\x10\x00\x00\x00\x00\x00\x00\x00\x11\x00\x00\x00\x00\x00\x00\x00\x12\x00\x00\x00\x00\x00\x00\x00\x13\x00\x00\x00\x00\x00\x00\x00q\x11h\x05\x86q\x12Rq\x13tq\x14b.'

tostring works similarly in that it converts the array to a string format. It has the advantage that it should be the same across python and numpy versions, but has the disadvantage that it doesn't store the dimensions, so you would need to keep the dimensions (and whether the array is C or Fortran order) in the database to properly reconstruct the array (unless it is always the same):

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

Which gives you (this will be the same in Python 2.x and 3.x, except that in Python 3.x it will be a bytes type and in python 2.x it will be a str type):

b'\x00\x00\x00\x00\x00\x00\x00\x00\x01\x00\x00\x00\x00\x00\x00\x00\x02\x00\x00\x00\x00\x00\x00\x00\x03\x00\x00\x00\x00\x00\x00\x00\x04\x00\x00\x00\x00\x00\x00\x00\x05\x00\x00\x00\x00\x00\x00\x00\x06\x00\x00\x00\x00\x00\x00\x00\x07\x00\x00\x00\x00\x00\x00\x00\x08\x00\x00\x00\x00\x00\x00\x00\t\x00\x00\x00\x00\x00\x00\x00\n\x00\x00\x00\x00\x00\x00\x00\x0b\x00\x00\x00\x00\x00\x00\x00\x0c\x00\x00\x00\x00\x00\x00\x00\r\x00\x00\x00\x00\x00\x00\x00\x0e\x00\x00\x00\x00\x00\x00\x00\x0f\x00\x00\x00\x00\x00\x00\x00\x10\x00\x00\x00\x00\x00\x00\x00\x11\x00\x00\x00\x00\x00\x00\x00\x12\x00\x00\x00\x00\x00\x00\x00\x13\x00\x00\x00\x00\x00\x00\x00'
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hi TheBlackCat, maybe my question wasn't entirely clear. I now no longer care about the fact my object was a numpy array, I just want to get the data in the numpy array into the SQL database. In effect my numpy array is currently acting as a very inefficient database that I have to keep loading into memory and querying.
0

My solution was to dump the array and convert it in hexadecimal:

array_5_str = array_5.dumps().hex() # to save it in the table

To convert it into a ndarray again:

array_5_from_str = pickle.loads(bytes.fromhex(array_5_str))

You can compare the two ndarrays with: comparison = array_5 == array_5_from_str equal_arrays = comparison.all() print(equal_arrays)

Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.