Search code examples
pythonarraysnumpypymssql

numpy arrays and pymssql

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?

Solution

  • 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.