numpy arrays and pymssql

Question

I need to store numpy arrays in a varbinary(max) field of an MS SQL database. In MySQL it was a simple task, but MS SQL is dicier. In order to be able to store something, I needed to mess around with CONVERT like so:

db_cursor.execute("INSERT INTO MyTable (AVect, BVect, SomeId) VALUES (CONVERT(varbinary(max), %s), CONVERT(varbinary(max), %s), %s)", (bytes(a_vect), bytes(b_vect), 42))

but when I run

db_cursor.execute("SELECT AVect FROM MyTable WHERE SomeId=42")

the value of db_cursor.fetchone()['AVect'] is

b'[  0.00000000e+00   1.22070312e-04   2.44140625e-04 ...,   4.99755859e-01\n   4.99877930e-01   5.00000000e-01]'

I am assuming the problem is with how I use CONVERT but what should it be?

The value I expected was b'\xdbF\xbeT*>\xf8??\xb1M\xd7"\xa8\xc5\xbf\xcf\xfe\xcb3\x0c\x9f\xd4\xbf\x11\x990Nc\xe3\xfc\xbf!:e8X\xb2\xf0?\x15bYn\xfa\xf9\x06@\x00\xba\x04\xcb\xd2\xc6\xf0\xbf=\x03\xf9$\xa5\x93\xe1?\xed\xb6U\xf7\xae/\xf0\xbf\x14 6I\x9aP\xe2?' — sjp
– sjp, Commented Oct 20, 2014 at 13:15

sjp · Accepted Answer · 2014-10-22 12:20:24Z

1

a.tostring() instead of bytes(a) seems to do the trick here. perhaps there are situations where pickle is necessary though, as per sebix's answer.

answered Oct 22, 2014 at 12:20

sjp

3924 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sjp Over a year ago

Also, it was a python version issue, not really a MS vs MySQL issue as I thought. The original code works fine with Python 3, but for Python 2.7, I needed numpy.tostring.

sebix · Accepted Answer · 2015-10-27 20:32:22Z

1

That's expected behavior. Look at what bytes(array) looks like: (in Legacy Python 2)

>>> bytes(np.arange(4))
'[0 1 2 3]'
>>> type(bytes(np.arange(4)))
<type 'str'>

What you could use is pickle.dumps:

>>> import pickle
>>> a = pickle.dumps(np.arange(2))
>>> a
"cnumpy.core.multiarray\n_reconstruct\np0\n(cnumpy\nndarray\np1\n(I0\ntp2\nS'b'\np3\ntp4\nRp5\n(I1\n(I2\ntp6\ncnumpy\ndtype\np7\n(S'i8'\np8\nI0\nI1\ntp9\nRp10\n(I3\nS'<'\np11\nNNNI-1\nI-1\nI0\ntp12\nbI00\nS'\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x01\\x00\\x00\\x00\\x00\\x00\\x00\\x00'\np13\ntp14\nb."
>>> pickle.loads(a)
array([0, 1])

This preserves all information needed for constructing your array, including data type, field names and so on. Using methods resulting in strings or byte-string, don't preserve these!

edited Oct 27, 2015 at 20:32

answered Oct 20, 2014 at 18:42

sebix

3,2972 gold badges31 silver badges49 bronze badges

4 Comments

sjp Over a year ago

in my case, In [29]: bytes(np.arange(4)) Out[29]: 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' perhaps I could use pickle, but that hardly seems neccessary since the thing I'm trying to do works perfectly well in MySQL without it.

sjp Over a year ago

oh, and the type is builtins.bytes

sebix Over a year ago

I have no idea what you want to say. Doesn't my version work? The bytes representation you use is not easily parseable. The pickle-version preserves all needed information like datatype and so on, which is not possible with byte-strings!

sjp Over a year ago

Pickle might certainly work, but it doesn't seem necessary to involve it in this case.

Collectives™ on Stack Overflow

numpy arrays and pymssql

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related